通過shell得到資料庫中權限的腳本

有些時候想直接檢視某個使用者下對應的權限資訊。自己每次從資料字典中查找有些太麻煩了。如果涉及的對象類型多一些，很容易遺漏。

一種方式就是通過exp直接導出對象的資訊來，可以直接解析dump内容來得到object的一些資訊，也可以直接通路資料字典表來得到。

以下是在Metalink中提供的腳本，我在原本的腳本基礎上稍微改動了一下。

不過可以看到這個腳本還是有一些的缺點，首先會建立一個臨時的表。把各種過濾資訊都放入臨時的表中，然後繼續篩查，而且對于表中的有些對象類型（比如資源回收筒中的對象）也羅列了出來，這個不是大家期望看到的。其它的部分功能都很全面。

SET ECHO off

REM ------------------------------------------------------------------------

REM REQUIREMENTS:

REM    DBA privs

REM AUTHOR: 

REM    Anonymous     

REM    Copyright 1995, Oracle Corporation     

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    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    NOTE:  This script DOES NOT include grants made by 'SYS'.

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 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;

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;

稍後會在這個基礎的版本做一個大改造。讓腳本的功能更加靈活和全面。