oracle_脚本获取创建用户语句

最新推荐文章于 2024-04-09 10:44:08 发布

转载最新推荐文章于 2024-04-09 10:44:08 发布 · 1.8k 阅读

Oracle安装、设定、升级、迁移专栏收录该内容

24 篇文章

订阅专栏

本文介绍了一种批量获取Oracle数据库中用户及其相关权限、配额等DDL脚本的方法。通过提供的PL/SQL过程，可以自动生成创建用户的DDL脚本，包括系统权限、对象权限及配额设置等内容。

在某些情况下,我们需要获得数据库用户的创建脚本(包含系统权限,对象权限,配额等相关语句),这些东西如果人工去做绝对是体力活,在asktom网站上看到相关脚本,做了测试和验证,确实很好

-----------------------------------------

-- ###########################################

---------------------------------------------

create or replace procedure GET_CREATE_USER_DDL

cursor get_username is

select username

from dba_users

--where username IN('CHF','XIFENFEI')

;

begin

for l_user in get_username loop

DBMS_OUTPUT.PUT_LINE('-----------------------');

DBMS_OUTPUT.PUT_LINE('select (case');

DBMS_OUTPUT.PUT_LINE(' when ((selectcount(*)');

DBMS_OUTPUT.PUT_LINE(' from dba_users');

DBMS_OUTPUT.PUT_LINE(' where username = '''||l_user.username||''') >0)');

DBMS_OUTPUT.PUT_LINE(' then dbms_metadata.get_ddl (''USER'','''||l_user.username||''')');

DBMS_OUTPUT.PUT_LINE(' else to_clob('' -- Note: User not found!'')');

DBMS_OUTPUT.PUT_LINE(' end ) "--Extracted_DDL" fromdual');

DBMS_OUTPUT.PUT_LINE('UNION ALL');

DBMS_OUTPUT.PUT_LINE('-----------------------');

DBMS_OUTPUT.PUT_LINE('select (case');

DBMS_OUTPUT.PUT_LINE(' when ((selectcount(*)');

DBMS_OUTPUT.PUT_LINE(' from dba_ts_quotas');

DBMS_OUTPUT.PUT_LINE(' where username = '''||l_user.username||''') >0)');

DBMS_OUTPUT.PUT_LINE(' then dbms_metadata.get_granted_ddl(''TABLESPACE_QUOTA'',

'''||l_user.username||''')');

DBMS_OUTPUT.PUT_LINE(' else to_clob('' -- Note: No TS Quotas found!'')');

DBMS_OUTPUT.PUT_LINE(' end ) fromdual');

DBMS_OUTPUT.PUT_LINE('UNION ALL');

DBMS_OUTPUT.PUT_LINE('-----------------------');

DBMS_OUTPUT.PUT_LINE('select (case');

DBMS_OUTPUT.PUT_LINE(' when ((selectcount(*)');

DBMS_OUTPUT.PUT_LINE(' from dba_role_privs');

DBMS_OUTPUT.PUT_LINE(' where grantee = '''||l_user.username||''') >0)');

DBMS_OUTPUT.PUT_LINE(' then dbms_metadata.get_granted_ddl(''ROLE_GRANT'',

'''||l_user.username||''')');

DBMS_OUTPUT.PUT_LINE(' else to_clob('' -- Note: No granted roles found!'')');

DBMS_OUTPUT.PUT_LINE(' end ) from dual');

DBMS_OUTPUT.PUT_LINE('UNION ALL');

DBMS_OUTPUT.PUT_LINE('-----------------------');

DBMS_OUTPUT.PUT_LINE('select (case');

DBMS_OUTPUT.PUT_LINE(' when ((selectcount(*)');

DBMS_OUTPUT.PUT_LINE(' from dba_sys_privs');

DBMS_OUTPUT.PUT_LINE(' where grantee = '''||l_user.username||''') >0)');

DBMS_OUTPUT.PUT_LINE(' then dbms_metadata.get_granted_ddl(''SYSTEM_GRANT'',

'''||l_user.username||''')');

DBMS_OUTPUT.PUT_LINE(' else to_clob('' -- Note: No System Privilegesfound!'')');

DBMS_OUTPUT.PUT_LINE(' end ) from dual');

DBMS_OUTPUT.PUT_LINE('UNION ALL');

DBMS_OUTPUT.PUT_LINE('-----------------------');

DBMS_OUTPUT.PUT_LINE('select (case');

DBMS_OUTPUT.PUT_LINE(' when ((selectcount(*)');

DBMS_OUTPUT.PUT_LINE(' from dba_tab_privs');

DBMS_OUTPUT.PUT_LINE(' where grantee = '''||l_user.username||''') >0)');

DBMS_OUTPUT.PUT_LINE(' then dbms_metadata.get_granted_ddl(''OBJECT_GRANT'',

'''||l_user.username||''')');

DBMS_OUTPUT.PUT_LINE(' else to_clob('' -- Note: No Object Privilegesfound!'')');

DBMS_OUTPUT.PUT_LINE(' end ) from dual');

DBMS_OUTPUT.PUT_LINE('/');

DBMS_OUTPUT.PUT_LINE('----------------------------------------------------------------------');

end loop;

end;

---------------------------------------------

--###########################################

---------------------------------------------

The above proc when called with the foll. will give the SQLsfor all users:

---------------------------------------------

-- ###########################################

---------------------------------------------

set pages 50000

set serveroutput on size unlimited

spool /tmp/exec_GET_CREATE_USER_DDL.sql

exec GET_CREATE_USER_DDL

spool off

---------------------------------------------

-- ###########################################

---------------------------------------------

These SQLs generated can in turn be run as follows to get themaster-list of all the grants in the database:

---------------------------------------------

-- ###########################################

---------------------------------------------

spool /tmp/GET_CREATE_USER_DDL.sql

conn / as sysdba

set long 1000000000

set pages 50000

execDBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE);

@/tmp/exec_GET_CREATE_USER_DDL.sql

spool off

---------------------------------------------

-- ###########################################

---------------------------------------------

通过这个脚本,我们可以在游标地方限制我们需要获得脚本的用户,而最终得到的/tmp/GET_CREATE_USER_DDL.sql就是我们需要的创建用户的对应脚本.

如果只需要获得一个用户创建相关脚本,只需要执行如下sql即可

execDBMS_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