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
如需转载,请标明出处和链接,谢谢!