nullnull使用PL/SQL获取创建用户的语句

在写这篇文章之前&＃xff0c;xxx已经写过了几篇关于改nullnull主题的文章,想要了解的朋友可以去翻一下之前的文章

    Create procedure:

create or replace procedure get_case_sqls_for_ddls_ver1 ascursor get_username isselect username from dba_users;beginfor l_user in get_username loopDBMS_OUTPUT.PUT_LINE(&＃39;-----------------------&＃39;);DBMS_OUTPUT.PUT_LINE(&＃39;select (case&＃39;);DBMS_OUTPUT.PUT_LINE(&＃39; when ((select count(*)&＃39;);DBMS_OUTPUT.PUT_LINE(&＃39; from dba_users&＃39;);DBMS_OUTPUT.PUT_LINE(&＃39; where username &＃61; &＃39;&＃39;&＃39; ||l_user.username || &＃39;&＃39;&＃39;) > 0)&＃39;);DBMS_OUTPUT.PUT_LINE(&＃39; then dbms_metadata.get_ddl (&＃39;&＃39;USER&＃39;&＃39;, &＃39;&＃39;&＃39; ||l_user.username || &＃39;&＃39;&＃39;)&＃39;);DBMS_OUTPUT.PUT_LINE(&＃39; else to_clob (&＃39;&＃39; -- Note: User not found!&＃39;&＃39;)&＃39;);DBMS_OUTPUT.PUT_LINE(&＃39; end ) "--Extracted_DDL" from dual&＃39;);DBMS_OUTPUT.PUT_LINE(&＃39;UNION ALL&＃39;);DBMS_OUTPUT.PUT_LINE(&＃39;-----------------------&＃39;);DBMS_OUTPUT.PUT_LINE(&＃39;select (case&＃39;);DBMS_OUTPUT.PUT_LINE(&＃39; when ((select count(*)&＃39;);DBMS_OUTPUT.PUT_LINE(&＃39; from dba_ts_quotas&＃39;);DBMS_OUTPUT.PUT_LINE(&＃39; where username &＃61; &＃39;&＃39;&＃39; ||l_user.username || &＃39;&＃39;&＃39;) > 0)&＃39;);DBMS_OUTPUT.PUT_LINE(&＃39; then dbms_metadata.get_granted_ddl (&＃39;&＃39;TABLESPACE_QUOTA&＃39;&＃39;,
&＃39;&＃39;&＃39; || l_user.username || &＃39;&＃39;&＃39;)&＃39;);DBMS_OUTPUT.PUT_LINE(&＃39; else to_clob (&＃39;&＃39; -- Note: No TS Quotas found!&＃39;&＃39;)&＃39;);DBMS_OUTPUT.PUT_LINE(&＃39; end ) from dual&＃39;);DBMS_OUTPUT.PUT_LINE(&＃39;UNION ALL&＃39;);DBMS_OUTPUT.PUT_LINE(&＃39;-----------------------&＃39;);DBMS_OUTPUT.PUT_LINE(&＃39;select (case&＃39;);DBMS_OUTPUT.PUT_LINE(&＃39; when ((select count(*)&＃39;);DBMS_OUTPUT.PUT_LINE(&＃39; from dba_role_privs&＃39;);DBMS_OUTPUT.PUT_LINE(&＃39; where grantee &＃61; &＃39;&＃39;&＃39; ||l_user.username || &＃39;&＃39;&＃39;) > 0)&＃39;);DBMS_OUTPUT.PUT_LINE(&＃39; then dbms_metadata.get_granted_ddl (&＃39;&＃39;ROLE_GRANT&＃39;&＃39;,
&＃39;&＃39;&＃39; || l_user.username || &＃39;&＃39;&＃39;)&＃39;);DBMS_OUTPUT.PUT_LINE(&＃39; else to_clob (&＃39;&＃39; -- Note: No granted roles found!&＃39;&＃39;)&＃39;);DBMS_OUTPUT.PUT_LINE(&＃39; end ) from dual&＃39;);DBMS_OUTPUT.PUT_LINE(&＃39;UNION ALL&＃39;);DBMS_OUTPUT.PUT_LINE(&＃39;-----------------------&＃39;);DBMS_OUTPUT.PUT_LINE(&＃39;select (case&＃39;);DBMS_OUTPUT.PUT_LINE(&＃39; when ((select count(*)&＃39;);DBMS_OUTPUT.PUT_LINE(&＃39; from dba_sys_privs&＃39;);DBMS_OUTPUT.PUT_LINE(&＃39; where grantee &＃61; &＃39;&＃39;&＃39; ||l_user.username || &＃39;&＃39;&＃39;) > 0)&＃39;);DBMS_OUTPUT.PUT_LINE(&＃39; then dbms_metadata.get_granted_ddl (&＃39;&＃39;SYSTEM_GRANT&＃39;&＃39;,
&＃39;&＃39;&＃39; || l_user.username || &＃39;&＃39;&＃39;)&＃39;);DBMS_OUTPUT.PUT_LINE(&＃39; else to_clob (&＃39;&＃39; -- Note: No System Privileges found!&＃39;&＃39;)&＃39;);DBMS_OUTPUT.PUT_LINE(&＃39; end ) from dual&＃39;);DBMS_OUTPUT.PUT_LINE(&＃39;UNION ALL&＃39;);DBMS_OUTPUT.PUT_LINE(&＃39;-----------------------&＃39;);DBMS_OUTPUT.PUT_LINE(&＃39;select (case&＃39;);DBMS_OUTPUT.PUT_LINE(&＃39; when ((select count(*)&＃39;);DBMS_OUTPUT.PUT_LINE(&＃39; from dba_tab_privs&＃39;);DBMS_OUTPUT.PUT_LINE(&＃39; where grantee &＃61; &＃39;&＃39;&＃39; ||l_user.username || &＃39;&＃39;&＃39;) > 0)&＃39;);DBMS_OUTPUT.PUT_LINE(&＃39; then dbms_metadata.get_granted_ddl (&＃39;&＃39;OBJECT_GRANT&＃39;&＃39;,
&＃39;&＃39;&＃39; || l_user.username || &＃39;&＃39;&＃39;)&＃39;);DBMS_OUTPUT.PUT_LINE(&＃39; else to_clob (&＃39;&＃39; -- Note: No Object Privileges found!&＃39;&＃39;)&＃39;);DBMS_OUTPUT.PUT_LINE(&＃39; end ) from dual&＃39;);DBMS_OUTPUT.PUT_LINE(&＃39;/&＃39;);DBMS_OUTPUT.PUT_LINE(&＃39;----------------------------------------------------------------------&＃39;);end loop;
end;
/

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

set head off
set pages 0
set serveroutput on size unlimited
spool /tmp/sqls_gathered_frm_trial_run_1.sql
exec get_case_sqls_for_ddls_ver1
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/grants_by_running_trial3_ver0.0.sql
conn / as sysdba
set head off
set long 1000000000
set pages 0
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,&＃39;SQLTERMINATOR&＃39;,TRUE);
&＃64;/tmp/sqls_gathered_frm_trial_run_1.sql
spool off

文章结束给大家分享下程序员的一些笑话语录&＃xff1a; 一条狗在街上闲逛&＃xff0c;看见橱窗里一张告示&＃xff1a;「招聘程序员。会编程&＃xff0c;有团队精神&＃xff0c;至少精通两种语言。均等机会。」
　　那条狗就进去申请&＃xff0c;但是被拒绝了。
　　「我不能雇一条狗在公司里做事。」经理说。
　　狗不服气&＃xff0c;指着告示上「均等机会」几字抗议。
　　经理没法&＃xff0c;叹了口气&＃xff0c;不屑地问道&＃xff1a;「你会编程吗&＃xff1f;」
　　那条狗默默地走到电脑前&＃xff0c;编了个程序&＃xff0c;运作准确。
　　「你有团队精神吗&＃xff1f;」经理问。
　　那条狗掉头看了看门外&＃xff0c;一大群野狗在外面虎视耽耽。
　　「我真的不能雇狗做这份工作。」经理气急败坏地说。
　　「就算会编程、有团队精神&＃xff0c;但是我需要的雇员至少要能精通两种语言。」
　　那条狗抬头看着经理说&＃xff1a;「喵&＃xff0d;噢。」