通過shell得到資料庫中許可權的指令碼

有些時候想直接檢視某個使用者下對應的許可權資訊。自己每次從資料字典中查詢有些太麻煩了。如果涉及的物件型別多一些，很容易遺漏。
一種方式就是通過exp直接匯出物件的資訊來，可以直接解析dump內容來得到object的一些資訊，也可以直接訪問資料字典表來得到。
以下是在Metalink中提供的指令碼，我在原本的指令碼基礎上稍微改動了一下。
不過可以看到這個指令碼還是有一些的缺點，首先會建立一個臨時的表。把各種過濾資訊都放入臨時的表中，然後繼續篩查，而且對於表中的有些物件型別（比如回收站中的物件）也羅列了出來，這個不是大家期望看到的。其它的部分功能都很全面。

sqlplus -s $DB_CONN_STR@$SH_DB_SID < 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/，如需轉載，請註明出處，否則將追究法律責任。