Showing posts with label grants. Show all posts
Showing posts with label grants. Show all posts

Wednesday, July 20, 2016

dumping system, role and proxy grants for users

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}) ;