有些时候想直接查看某个用户下对应的权限信息。自己每次从数据字典中查找有些太麻烦了。如果涉及的对象类型多一些，很容易遗漏。

一种方式就是通过exp直接导出对象的信息来，可以直接解析dump内容来得到object的一些信息，也可以直接访问数据字典表来得到。 以下是在Metalink中提供的脚本，我在原本的脚本基础上稍微改动了一下。 不过可以看到这个脚本还是有一些的缺点，首先会创建一个临时的表。把各种过滤信息都放入临时的表中，然后继续筛查，而且对于表中的有些对象类型（比如回收站中的对象）也罗列了出来，这个不是大家期望看到的。其它的部分功能都很全面。 sqlplus -s < SET ECHO off REM ------------------------------------------------------------------------ REM REQUIREMENTS: REM    DBA privs REM ------------------------------------------------------------------------ REM AUTHOR:  REM    Anonymous      REM    Copyright 1995, Oracle Corporation      REM ------------------------------------------------------------------------ REM PURPOSE: REM    Running this script will in turn create a script of  REM    all the object grants to users and roles.  This created  REM    script is called tfscsopv.lst. REM REM    Since a DBA cannot grant objects other than his own,  REM    this script will contain various connect clauses before  REM    each set of grant statements.  You must add the passwords  REM    for each user before executing the script.  Object grants  REM    are very dependant on the user who issues the grant,  REM    therefore, it is important that the correct user issue the  REM    grant. REM REM    In addition, DO NOT change the order of the grant statement. REM    They are spooled in sequence order, so that dependant grants REM    are executed in the correct order.  For example, lets say  REM    that Scott grants Jack select on emp with grant option, and  REM    in turn Jack grants select on Scott.emp to Steve.  It is  REM    essential that Scott's grant be issued before Jack's.    REM    Otherwise, Jack's grant will fail. REM REM    NOTE:  This script DOES NOT include grants made by 'SYS'. REM ------------------------------------------------------------------------ REM DISCLAIMER: REM    This script is provided for educational purposes only. It is NOT  REM    supported by Oracle World Wide Technical Support. REM    The script has been tested and appears to work as intended. REM    You should always run new scripts on a test instance initially. REM ------------------------------------------------------------------------ REM Main text of script follows:     set verify off set feedback off set termout off set pagesize 500 set heading off set recsep off set linesize 200  set termout on select 'Creating object grant script by user...' from dual; set termout off   create table g_temp (seq NUMBER, grantor_owner varchar2(20),                     text VARCHAR2(800));   DECLARE    cursor grant_cursor is      SELECT ur$.name, uo$.name, o$.name, ue$.name,               m$.name, t$.sequence#,                decode(NVL(t$.option$,0), 1, ' WITH GRANT OPTION;',';')      FROM sys.objauth$ t$, sys.obj$ o$, sys.user$ ur$,             sys.table_privilege_map m$, sys.user$ ue$, sys.user$ uo$        WHERE o$.obj# = t$.obj# AND t$.privilege# = m$.privilege AND              t$.col# IS NULL AND t$.grantor# = ur$.user# AND              t$.grantee# = ue$.user# and               o$.owner#=uo$.user# and             --  o$.name=upper('$2') and              ur$.name=upper('$1') and              t$.grantor# != 0        order by sequence#;    lv_grantor    sys.user$.name%TYPE;    lv_owner      sys.user$.name%TYPE;    lv_table_name sys.obj$.name%TYPE;    lv_grantee    sys.user$.name%TYPE;    lv_privilege  sys.table_privilege_map.name%TYPE;    lv_sequence   sys.objauth$.sequence#%TYPE;    lv_option     VARCHAR2(30);    lv_string     VARCHAR2(800);    lv_first      BOOLEAN;      procedure write_out(p_seq INTEGER, p_owner VARCHAR2, p_string VARCHAR2) is    begin       insert into g_temp (seq, grantor_owner,text)  values (lv_sequence, lv_grantor, lv_string);     end;   BEGIN   OPEN grant_cursor;     LOOP       FETCH grant_cursor INTO lv_grantor,lv_owner,lv_table_name,lv_grantee,          lv_privilege,lv_sequence,lv_option;       EXIT WHEN grant_cursor%NOTFOUND;       lv_string := 'GRANT ' || lv_privilege || ' ON ' || lv_owner ||                    '.' ||                    lv_table_name || ' TO ' || lv_grantee ||                    lv_option;       write_out(lv_sequence, lv_grantor,lv_string);     END LOOP;   CLOSE grant_cursor; END; /   spool gen_sqls/$1_roles.sql break on guser skip 1 col text format a60 word_wrap col text format a100   select    text from     g_temp order by seq, grantor_owner /   spool off   drop table g_temp; EOF exit脚本的运行效果如下，输入schema名称即可。

[ora11g@rac1 dbm_lite]$ ksh genroles.sh n1

Creating object grant script by user...

GRANT READ ON SYS.EXPDP_LOCATION TO PRDCONN;

GRANT WRITE ON SYS.EXPDP_LOCATION TO PRDCONN; GRANT READ ON SYS.EXT_DATAPUMP TO MIG; GRANT WRITE ON SYS.EXT_DATAPUMP TO MIG; GRANT SELECT ON N1.BIG_INSERT TO APP_CONN WITH GRANT OPTION; GRANT SELECT ON N1.TT TO APP_CONN WITH GRANT OPTION; GRANT SELECT ON N1.T TO APP_CONN WITH GRANT OPTION;GRANT SELECT ON N1.BIN$/KBps0AbJ07gRQAAAAAAAQ==$0 TO APP_CONN WITH GRANT OPTION;

稍后会在这个基础的版本做一个大改造。让脚本的功能更加灵活和全面。                                             

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23718752/viewspace-1259191/，如需转载，请注明出处，否则将追究法律责任。