set long 10000000
set longchunksize 1000000
set pages 0 head off feed off echo off
set lines 500
col lines for a500 word_wrapped
exec dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE);
select '-- SYSTEM GRANTS' from dual;
select dbms_metadata.get_granted_ddl('SYSTEM_GRANT',du.username) as lines from dba_users du where du.username in (${preserve_grant_quoted}) and exists (select grantee from dba_sys_privs where grantee=du.username);
select '-- ROLE GRANTS' from dual;
select dbms_metadata.get_granted_ddl('ROLE_GRANT',du.username) as lines from dba_users du where du.username in (${preserve_grant_quoted}) and exists (select grantee from dba_role_privs where grantee=du.username);
select '-- DEFAULT ROLES' from dual;
select dbms_metadata.get_granted_ddl('DEFAULT_ROLE',du.username) as lines from dba_users du where du.username in (${preserve_grant_quoted}) ;
select '-- PROXY GRANTS' from dual;
select dbms_metadata.get_granted_ddl('PROXY', pu.client) as lines from proxy_users pu where pu.client in (${preserve_grant_quoted}) ;
Wednesday, July 20, 2016
dumping system, role and proxy grants for users
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment