Oracle收集使用者的許可權

【使用情境】系統上線前準備工作。一般遷移資料的時候經常使用匯入dump檔案的方法。由於是一個一個使用者匯入進去的，所以在匯入處理程序中會報許可權問題的錯誤（可以忽略），還必須在生產庫授權系統才能正常工作。使用下面的指令碼可以得到授權語句。

在UAT環境執行select語句，在生產環境執行grant語句,根據實際使用者做相應的修改。

注意：查詢的使用者需要有查看dba_開頭資料字典的許可權，否則報出表或者視圖不存在。

SELECT 'GRANT ' || T1.GRANTED_ROLE || ' TO ' || T1.GRANTEE || ';' AS TEXT

FROM DBA_ROLE_PRIVS T1

WHERE T1.GRANTEE IN ('ABOQ',

'AMLM',

'BOP',

'CREDIT',

'DATACORE',

'MDR2',

'METABASE',

'METABASE_CREDIT',

'REPORT',

'UPRR',

'WORK')

UNION ALL

SELECT 'GRANT ' || T2.PRIVILEGE || ' TO ' || T2.GRANTEE || ';' AS TEXT

FROM DBA_SYS_PRIVS T2

WHERE T2.GRANTEE IN ('ABOQ',

'AMLM',

'BOP',

'CREDIT',

'DATACORE',

'MDR2',

'METABASE',

'METABASE_CREDIT',

'REPORT',

'UPRR',

'WORK')

UNION ALL

SELECT 'GRANT ' || T3.PRIVILEGE || ' ON ' || T3.OWNER || '.' ||

T3.TABLE_NAME || ' TO ' || T3.GRANTEE || ';' AS TEXT

FROM DBA_TAB_PRIVS T3

WHERE T3.GRANTEE IN ('ABOQ',

'AMLM',

'BOP',

'CREDIT',

'DATACORE',

'MDR2',

'METABASE',

'METABASE_CREDIT',

'REPORT',

'UPRR',

'WORK');

這樣寫比較死板，使用‘&’替換變數也很不好用，可以建立一個預存程序。

create or replace procedure user_priv(username in varchar2) as
  cursor v_cur  is
    SELECT 'GRANT ' || T1.GRANTED_ROLE || ' TO ' || T1.GRANTEE || ';' AS TEXT
      FROM DBA_ROLE_PRIVS T1
     where T1.grantee = upper(username)
    union all
    SELECT 'GRANT ' || T2.PRIVILEGE || ' TO ' || T2.GRANTEE || ';' AS TEXT
      FROM DBA_SYS_PRIVS T2
     WHERE T2.GRANTEE = upper(username)
    union all
    SELECT 'GRANT ' || T3.PRIVILEGE || ' ON ' || T3.OWNER || '.' ||
           T3.TABLE_NAME || ' TO ' || T3.GRANTEE || ';' AS TEXT
      FROM DBA_TAB_PRIVS T3
     where T3.grantee = upper(username);
/*select privilege from dba_sys_privs where grantee in (select granted_role from dba_role_privs where grantee=upper(username) );如果有必要可-以收集角色中的許可權*/

begin
  for v_t in v_cur loop
    begin
      dbms_output.put_line(v_t.TEXT);
    end;
  end loop;
end;

最後直接運行procedure即可  execute user_priv (username => 'hr');

如果遇到輸出文本問題請參考：http://blog.csdn.net/u010033674/article/details/8744629

http://www.cnblogs.com/AlbertCQY/archive/2013/03/31/2992497.html