分类目录归档：数据库

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

---------------------------------------------
-- ###########################################
---------------------------------------------
create or replace procedure GET_CREATE_USER_DDL
 as
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 ((select count(*)'); 
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" from dual'); 
DBMS_OUTPUT.PUT_LINE('UNION ALL');

DBMS_OUTPUT.PUT_LINE('-----------------------');
DBMS_OUTPUT.PUT_LINE('select (case'); 
DBMS_OUTPUT.PUT_LINE('        when ((select count(*)'); 
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 )  from dual'); 
DBMS_OUTPUT.PUT_LINE('UNION ALL');

DBMS_OUTPUT.PUT_LINE('-----------------------');
DBMS_OUTPUT.PUT_LINE('select (case'); 
DBMS_OUTPUT.PUT_LINE('        when ((select count(*)'); 
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 ((select count(*)'); 
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 Privileges 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 ((select count(*)'); 
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 Privileges found!'')'); 
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 SQLs for 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 the master-list of all the grants in the database:

---------------------------------------------
-- ###########################################
---------------------------------------------
spool /tmp/GET_CREATE_USER_DDL.sql
conn / as sysdba
set long 1000000000
set pages 50000
exec DBMS_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即可

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
/