0
user_create_ddl
emcs
50次下载
305次浏览
2019-07-24
4.5

脚本内容

--获取创建用户脚本及权限
set line 199  
set long 100000 
set pages 1000 
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR', true);
SELECT (
 CASE
   WHEN ((SELECT COUNT(*) FROM dba_users WHERE username = '&&Username') > 0)
   THEN dbms_metadata.get_ddl ('USER', '&&Username')
   ELSE to_clob (' -- Note: User not found!')
 END ) extracted_ddl
FROM dual
UNION ALL
SELECT (
 CASE
   WHEN ((SELECT COUNT(*) FROM dba_ts_quotas WHERE username = '&&Username') > 0)
   THEN dbms_metadata.get_granted_ddl( 'TABLESPACE_QUOTA', '&&Username')
   ELSE to_clob (' -- Note: No TS Quotas found!')
 END )
FROM dual
UNION ALL
SELECT (
 CASE
   WHEN ((SELECT COUNT(*) FROM dba_role_privs WHERE grantee = '&&Username') > 0)
   THEN dbms_metadata.get_granted_ddl ('ROLE_GRANT', '&&Username')
   ELSE to_clob (' -- Note: No granted Roles found!')
 END )
FROM dual
UNION ALL
SELECT (
 CASE
   WHEN ((SELECT COUNT(*) FROM dba_sys_privs WHERE grantee = '&&Username') > 0)
   THEN dbms_metadata.get_granted_ddl ('SYSTEM_GRANT', '&&Username')
   ELSE to_clob (' -- Note: No System Privileges found!')
 END )
FROM dual
UNION ALL
SELECT (
 CASE
   WHEN ((SELECT COUNT(*) FROM dba_tab_privs WHERE grantee = '&&Username') > 0)
   THEN dbms_metadata.get_granted_ddl ('OBJECT_GRANT', '&&Username')
   ELSE to_clob (' -- Note: No Object Privileges found!')
 END )
FROM dual

评论

热门资源
热门脚本
近期活动
全部
暂无活动,敬请期待...
相关课程
全部