使用PL/SQL获取创建用户的语句(二)
er.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
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