1. 查看指定用戶擁有的所有權限(系統權限、對象權限、角色),分別是通過dba_sys_privs,dba_tab_privs,dba_role_privs三個視圖來查看。
腳本:query_user_privs.sql
2. 獲得指定用戶所賦權限的DDL語句,有兩種方式:
1).利用1中query_user_privs.sql腳本,在select 後加入grant to等關鍵字,使得查詢輸出的結果為完整的DDL語句。
腳本:get_ddl_privs_dic.sql
2)使用oracle提供的包:
dbms_metadata.get_ddl('USER','&&uname') 獲取創建user的DDL語句
dbms_metadata.get_granted_ddl('SYSTEM_GRANT','&&uname') 獲取指定用戶賦予系統權限的DDL語句
dbms_metadata.get_granted_ddl('ROLE_GRANT','&&uname‘) 獲取指定用戶賦予角色的DDL語句
dbms_metadata.get_granted_ddl('OBJECT_GRANT,'&&uname') 獲取指定用戶賦予對象權限的DDL語句
腳本:get_ddl_privs_pac.sql
3. 腳本
3.1 query_user_privs.sql
set echo off set verify off set pagesize 999 set linesize 200 col type format a20 SELECT * FROM (SELECT a.username, 'ROLE' AS TYPE, b.granted_role || DECODE (admin_option, 'YES', ' (With Admin Option)', NULL) what_granted FROM sys.dba_users a, sys.dba_role_privs b WHERE a.username = b.grantee UNION SELECT a.username, 'SysPrivs' AS TYPE, b.privilege || DECODE (admin_option, 'YES', ' (With Admin Option)', NULL) what_granted FROM sys.dba_users a, sys.dba_sys_privs b WHERE a.username = b.grantee UNION SELECT a.username, 'ObjPrivs' AS TYPE, b.owner || '.' || b.table_name || ' - ' || b.privilege || DECODE (grantable, 'YES', ' (With Grant Option)', NULL) what_granted FROM sys.dba_users a, sys.dba_tab_privs b WHERE a.username = b.grantee ORDER BY 1) WHERE username = upper('&input_username');
3.2 get_ddl_privs_dic.sql
clear screen accept uname prompt 'Enter User Name : ' accept outfile prompt ' Output filename : ' col username noprint col lne newline set heading off pagesize 0 verify off feedback off linesize 180 spool &&outfile..gen prompt SELECT username, 'CREATE USER '||username||' '|| DECODE(password, 'EXTERNAL', 'IDENTIFIED EXTERNALLY', 'IDENTIFIED BY VALUES '''||password||''' ') lne, 'DEFAULT TABLESPACE '||default_tablespace lne, 'TEMPORARY TABLESPACE '||temporary_tablespace||';' lne FROM DBA_USERS WHERE USERNAME LIKE UPPER('%&&uname%') OR UPPER('&&uname') IS NULL ORDER BY USERNAME; SELECT username, 'ALTER USER '||username||' QUOTA '|| DECODE(MAX_BYTES, -1, 'UNLIMITED', TO_CHAR(ROUND(MAX_BYTES/1024))||'K') ||' ON '||tablespace_name||';' lne FROM DBA_TS_QUOTAS WHERE USERNAME LIKE UPPER('%&&uname%') OR UPPER('&&uname') IS NULL ORDER BY USERNAME; col grantee noprint select grantee, granted_role granted_priv, 'GRANT '||granted_role||' to '||grantee|| DECODE(ADMIN_OPTION, 'Y', ' WITH ADMIN OPTION;', ';') from dba_role_privs where grantee like upper('%&&uname%') UNION select grantee, privilege granted_priv, 'GRANT '||privilege||' to '||grantee|| DECODE(ADMIN_OPTION, 'Y', ' WITH ADMIN OPTION;', ';') from dba_sys_privs where grantee like upper('%&&uname%') order by 1, 2; spool off
3.3 get_ddl_privs_pac.sql
clear screen accept uname prompt 'Enter User Name : ' accept outfile prompt ' Output filename : ' spool &&outfile..gen SET LONG 2000000 PAGESIZE 0 head off verify off feedback off linesize 180 SELECT dbms_metadata.get_ddl('USER','&&uname') FROM dual; SELECT dbms_metadata.get_granted_ddl('SYSTEM_GRANT','&&uname') from dual; SELECT dbms_metadata.get_granted_ddl('ROLE_GRANT','&&uname') from dual; SELECT dbms_metadata.get_granted_ddl('OBJECT_GRANT','&&uname') from dual; spool off