查看oracle用户所有权限，并获取授权的DDL

rrt44

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   

ballontt
2014/02/27
---The End---
微博：weibo.com/ballontt
如需转载，请标明出处和链接，谢谢！