Use PL/SQL to obtain the statement for creating a user

Use PL/SQL to obtain the statement Create procedure: create or replace procedure created as cursor get_username is select username from dba_users; begin for l_user in get_username loop DBMS_OUTPUT.PUT_LINE ('authorization ('-----------------------'); DBMS_OUTPUT.PUT_LINE ('select (case '); Explain ('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 ('hangzhou ('-----------------------'); 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_metada Ta. 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'); values ('hour'); 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'); values ('hour'); 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'); values ('hour'); 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 ('unknown'); 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/batch 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/container conn/as sysdba set head off set long 1000000000 set pages 0 exec DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM, 'sqlterminator', TRUE); @/tmp/container spool off