在某些情况下,我们需要获得数据库用户的创建脚本(包含系统权限,对象权限,配额等相关语句),这些东西如果人工去做绝对是体力活,在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
UNION ALL
select (case
UNION ALL
select (case
UNION ALL
select (case
UNION ALL
select (case
/