Create procedure:
create or replace procedure get_case_sqls_for_ddls_ver1 as cursor get_username is select username from dba_users; 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 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,'SQLTERMINATOR',TRUE); @/tmp/sqls_gathered_frm_trial_run_1.sql spool off