Overview

This query retrieves the privileges granted to an account or ROLE in a hierarchical structure according to ROLE inheritance relationships and outputs them in DDL format so that each privilege can be recreated.

It can be used to check account privileges, migrate accounts, or create an account with the same privileges.

Method

Enter the account or ROLE for which you want to retrieve the privilege DDL in the name variable.

exec :name := 'TIBERO'

set pages 1000
set lines 200
col div for a20
col priv for a100
var name varchar(20)

exec :name := 'TIBERO'

with
user_name as (
  select :name as uname from dual
),
all_roles as (
  select distinct granted_role as role_name
    from dba_role_privs
   start with grantee = (select uname from user_name)
 connect by prior granted_role = grantee
),
obj_privs as (
  select grantee,
         decode(owner, grantor, NULL, '/*GRANTOR:"'|| grantor ||'"*/ ')||
         'GRANT '||privilege||' ON '||
         decode(type,'DIRECTORY','DIRECTORY ','"'||owner||'".')||'"'||table_name||'"'||
         ' TO "'||grantee||'"'||
         decode(grantable,'YES',' WITH GRANT OPTION;',';') as priv
    from dba_tab_privs
   where table_name not in (select object_name from dba_recyclebin)
),
priv_tree as (
        select 'ROLE' as div, null as parent, rp.granted_role as child,
               'GRANT "'||rp.granted_role||'" TO "'||rp.grantee||'"'||
               decode(rp.admin_option,'YES',' WITH ADMIN OPTION;',';') as priv
          from dba_role_privs rp
         where rp.grantee = (select uname from user_name)
        union all
        select ' ROLE', rp.grantee, rp.granted_role,
               'GRANT "'||rp.granted_role||'" TO "'||rp.grantee||'"'||
               decode(rp.admin_option,'YES',' WITH ADMIN OPTION;',';')
          from dba_role_privs rp
         where rp.grantee in (select role_name from all_roles)
        union all
        select ' SYS_PRIV', spv.grantee, null,
               'GRANT '||spv.privilege||' TO "'||spv.grantee||'"'||
               decode(spv.admin_option,'YES',' WITH ADMIN OPTION;',';')
          from dba_sys_privs spv
         where spv.grantee in (select role_name from all_roles)
        union all
        select ' OBJ_PRIV', opv.grantee, null, opv.priv
          from obj_privs opv
         where opv.grantee in (select role_name from all_roles)
)
select lpad(' ',2*(level-1))||div||'('||level||')' as div,
       lpad(' ',2*(level-1))||
       replace(priv, chr(10), chr(10)||lpad(' ',2*(level-1))) as priv
  from priv_tree
 start with parent is null
connect by prior child = parent
union all
select 'SYS_PRIV(1)' as div,
       'GRANT '||privilege||' TO "'||grantee||'"'||
       decode(admin_option,'YES',' WITH ADMIN OPTION;',';')
  from dba_sys_privs
 where grantee = (select uname from user_name)
union all
select 'OBJ_PRIV(1)' as div, priv
  from obj_privs
 where grantee = (select uname from user_name)
  union all
 select 'DEFAULT_ROLE(1)','ALTER USER "'||grantee||'" DEFAULT ROLE '||
       nvl(listagg(decode(default_role,'YES','"'||granted_role||'"'), ',')
             within group (order by granted_role), 'NONE')||';' as ddl_text
  from dba_role_privs
 where grantee = (select uname from user_name)
 group by grantee
having count(decode(default_role,'NO',1)) > 0
;   

Query Execution Results

DIV                  PRIV
-------------------- ----------------------------------------------------------------------------------------------------
ROLE(1)              GRANT "PLUSTRACE" TO "TIBERO";
   OBJ_PRIV(2)         GRANT SELECT ON "SYS"."V$SQL_PLAN_STATISTICS" TO "PLUSTRACE";
   OBJ_PRIV(2)         GRANT SELECT ON "SYS"."V$SQL_PLAN" TO "PLUSTRACE";
   OBJ_PRIV(2)         GRANT SELECT ON "SYS"."_VT_AUTOTRACESTAT" TO "PLUSTRACE";
ROLE(1)              GRANT "EXP_FULL_DATABASE" TO "TIBERO";
   SYS_PRIV(2)         GRANT EXEMPT REDACTION POLICY TO "EXP_FULL_DATABASE";
   SYS_PRIV(2)         GRANT FLASHBACK ANY TABLE TO "EXP_FULL_DATABASE";
   SYS_PRIV(2)         GRANT ANALYZE ANY TO "EXP_FULL_DATABASE";
   SYS_PRIV(2)         GRANT EXECUTE ANY PROCEDURE TO "EXP_FULL_DATABASE";
   SYS_PRIV(2)         GRANT SELECT ANY SEQUENCE TO "EXP_FULL_DATABASE";
   SYS_PRIV(2)         GRANT SELECT ANY TABLE TO "EXP_FULL_DATABASE";
   SYS_PRIV(2)         GRANT CREATE TABLE TO "EXP_FULL_DATABASE";
   SYS_PRIV(2)         GRANT CREATE SESSION TO "EXP_FULL_DATABASE";
   ROLE(2)             GRANT "SELECT_CATALOG_ROLE" TO "EXP_FULL_DATABASE";
     OBJ_PRIV(3)         GRANT SELECT ON "LBACSYS"."DBA_TLS_STATUS" TO "SELECT_CATALOG_ROLE";
     OBJ_PRIV(3)         GRANT SELECT ON "LBACSYS"."DBA_SA_AUDIT_OPTIONS" TO "SELECT_CATALOG_ROLE";
     OBJ_PRIV(3)         GRANT SELECT ON "LBACSYS"."DBA_SA_PROG_PRIVS" TO "SELECT_CATALOG_ROLE";
     OBJ_PRIV(3)         GRANT SELECT ON "LBACSYS"."DBA_SA_PROGRAMS" TO "SELECT_CATALOG_ROLE";
     OBJ_PRIV(3)         GRANT SELECT ON "LBACSYS"."DBA_SA_USER_PRIVS" TO "SELECT_CATALOG_ROLE";
     OBJ_PRIV(3)         GRANT SELECT ON "LBACSYS"."DBA_SA_USER_LABELS" TO "SELECT_CATALOG_ROLE";
     OBJ_PRIV(3)         GRANT SELECT ON "LBACSYS"."DBA_SA_USER_GROUPS" TO "SELECT_CATALOG_ROLE";
     OBJ_PRIV(3)         GRANT SELECT ON "LBACSYS"."DBA_SA_USER_COMPARTMENTS" TO "SELECT_CATALOG_ROLE";
     OBJ_PRIV(3)         GRANT SELECT ON "LBACSYS"."DBA_SA_USER_LEVELS" TO "SELECT_CATALOG_ROLE";
     OBJ_PRIV(3)         GRANT SELECT ON "LBACSYS"."DBA_SA_USERS" TO "SELECT_CATALOG_ROLE";
     OBJ_PRIV(3)         GRANT SELECT ON "LBACSYS"."DBA_SA_GROUP_HIERARCHY" TO "SELECT_CATALOG_ROLE";
     OBJ_PRIV(3)         GRANT SELECT ON "LBACSYS"."DBA_SA_GROUPS" TO "SELECT_CATALOG_ROLE";
     OBJ_PRIV(3)         GRANT SELECT ON "LBACSYS"."DBA_SA_COMPARTMENTS" TO "SELECT_CATALOG_ROLE";
     OBJ_PRIV(3)         GRANT SELECT ON "LBACSYS"."DBA_SA_LEVELS" TO "SELECT_CATALOG_ROLE";
     OBJ_PRIV(3)         GRANT SELECT ON "LBACSYS"."DBA_SA_DATA_LABELS" TO "SELECT_CATALOG_ROLE";
     OBJ_PRIV(3)         GRANT SELECT ON "LBACSYS"."DBA_SA_LABELS" TO "SELECT_CATALOG_ROLE";
     OBJ_PRIV(3)         GRANT SELECT ON "LBACSYS"."DBA_SA_SCHEMA_POLICIES" TO "SELECT_CATALOG_ROLE";
     OBJ_PRIV(3)         GRANT SELECT ON "LBACSYS"."DBA_SA_TABLE_POLICIES" TO "SELECT_CATALOG_ROLE";
     OBJ_PRIV(3)         GRANT SELECT ON "LBACSYS"."DBA_SA_POLICIES" TO "SELECT_CATALOG_ROLE";
     SYS_PRIV(3)         GRANT SELECT ANY DICTIONARY TO "SELECT_CATALOG_ROLE";
ROLE(1)              GRANT "RESOURCE" TO "TIBERO";
   SYS_PRIV(2)         GRANT CREATE TRIGGER TO "RESOURCE";
   SYS_PRIV(2)         GRANT CREATE PROCEDURE TO "RESOURCE";
   SYS_PRIV(2)         GRANT CREATE SEQUENCE TO "RESOURCE";
   SYS_PRIV(2)         GRANT CREATE TABLE TO "RESOURCE";
ROLE(1)              GRANT "CONNECT" TO "TIBERO" WITH ADMIN OPTION;
   SYS_PRIV(2)         GRANT CREATE SESSION TO "CONNECT";
SYS_PRIV(1)          GRANT SELECT ANY TABLE TO "TIBERO" WITH ADMIN OPTION;
OBJ_PRIV(1)          /*GRANTOR:"SYSCAT"*/ GRANT SELECT ON "SYS"."V$SESSION" TO "TIBERO" WITH GRANT OPTION;
DEFAULT_ROLE(1)      ALTER USER "TIBERO" DEFAULT ROLE "CONNECT","RESOURCE";

44 rows selected.