List system and object privilege recursively for User/Role/Public

 Privileges can be granted directly or inherited from granted roles and recursive granted roles, even from PUBLIC. How to list these object and system privilege for a user?

-- final privlege resursive query, privilege granted to PUBLIC applies to all
WITH grantees AS (
    SELECT column_value AS grantees 
    FROM TABLE(
        sys.odcivarchar2list(
            'HR'
            --,'PUBLIC' 
        )
    )
)
select privilege, listagg(roles,',')  within group (order by roles) role_list
from (
    -- list all system privilege granted directly to grantees
    select s.privilege, 'S' as priv_type, null as roles  from dba_sys_privs s join grantees g 
    on s.grantee=g.grantees
    union
    -- list all object privilege granted directly to grantees
    select t.privilege||' on '|| t.owner||'.'||t.table_name privilege, 'O' as priv_type, null as roles 
    from dba_tab_privs t join grantees g on t.grantee=g.grantees
    union
    -- list all sys privilege granted through roles to grantees
    select s.privilege, 'S' as priv_type, r.role from dba_sys_privs s join dba_roles r 
    on s.grantee=r.role
    join (select granted_role from dba_role_privs 
    connect by prior granted_role=grantee start with grantee in (select * from grantees)) rr
    on r.role=rr.granted_role
    union
    -- list all object privilege granted through roles to grantees
    select t.privilege||' on '|| t.owner||'.'||t.table_name privilege, 'O' as priv_type, r.role 
    from dba_tab_privs t join dba_roles r on t.grantee=r.role
    join (select granted_role from dba_role_privs 
    connect by prior granted_role=grantee start with grantee in (select * from grantees)) rr
    on r.role=rr.granted_role
)
group by priv_type, privilege
order by priv_type DESC, privilege;

Example 1: Use HR as example:

SQL> -- final privlege resursive query, privilege granted to PUBLIC applies to all
SQL> WITH grantees AS (
  2      SELECT column_value AS grantees
  3      FROM TABLE(
  4          sys.odcivarchar2list(
  5              'HR'
  6              --,'PUBLIC'
  7          )
  8      )
  9  )
 10  select privilege, listagg(roles,',')  within group (order by roles) role_list
 11  from (
 12      -- list all system privilege granted directly to grantees
 13      select s.privilege, 'S' as priv_type, null as roles  from dba_sys_privs s join grantees g
 14      on s.grantee=g.grantees
 15      union
 16      -- list all object privilege granted directly to grantees
 17      select t.privilege||' on '|| t.owner||'.'||t.table_name privilege, 'O' as priv_type, null as roles
 18      from dba_tab_privs t join grantees g on t.grantee=g.grantees
 19      union
 20      -- list all sys privilege granted through roles to grantees
 21      select s.privilege, 'S' as priv_type, r.role from dba_sys_privs s join dba_roles r
 22      on s.grantee=r.role
 23      join (select granted_role from dba_role_privs
 24      connect by prior granted_role=grantee start with grantee in (select * from grantees)) rr
 25      on r.role=rr.granted_role
 26      union
 27      -- list all object privilege granted through roles to grantees
 28      select t.privilege||' on '|| t.owner||'.'||t.table_name privilege, 'O' as priv_type, r.role
 29      from dba_tab_privs t join dba_roles r on t.grantee=r.role
 30      join (select granted_role from dba_role_privs
 31      connect by prior granted_role=grantee start with grantee in (select * from grantees)) rr
 32      on r.role=rr.granted_role
 33  )
 34  group by priv_type, privilege
 35  order by priv_type DESC, privilege;

                                   PRIVILEGE    ROLE_LIST
____________________________________________ ____________
ALTER SESSION
CREATE CLUSTER                               RESOURCE
CREATE DATABASE LINK
CREATE INDEXTYPE                             RESOURCE
CREATE OPERATOR                              RESOURCE
CREATE PROCEDURE                             RESOURCE
CREATE SEQUENCE                              RESOURCE
CREATE SESSION
CREATE SYNONYM
CREATE TABLE                                 RESOURCE
CREATE TRIGGER                               RESOURCE
CREATE TYPE                                  RESOURCE
CREATE VIEW
UNLIMITED TABLESPACE
EXECUTE on SYS.DBMS_STATS
EXECUTE on XDB.DBMS_SODA_ADMIN               SODA_APP
EXECUTE on XDB.DBMS_SODA_USER_ADMIN          SODA_APP
READ on XDB.JSON$USER_COLLECTION_METADATA    SODA_APP

18 rows selected.

Example 2: Use DONGHUA (DBA Account) as example:

SQL> -- final privlege resursive query, privilege granted to PUBLIC applies to all
SQL> WITH grantees AS (
  2      SELECT column_value AS grantees
  3      FROM TABLE(
  4          sys.odcivarchar2list(
  5              'DONGHUA'
  6              --,'PUBLIC'
  7          )
  8      )
  9  )
 10  select privilege, listagg(roles,',')  within group (order by roles) role_list
 11  from (
 12      -- list all system privilege granted directly to grantees
 13      select s.privilege, 'S' as priv_type, null as roles  from dba_sys_privs s join grantees g
 14      on s.grantee=g.grantees
 15      union
 16      -- list all object privilege granted directly to grantees
 17      select t.privilege||' on '|| t.owner||'.'||t.table_name privilege, 'O' as priv_type, null as roles
 18      from dba_tab_privs t join grantees g on t.grantee=g.grantees
 19      union
 20      -- list all sys privilege granted through roles to grantees
 21      select s.privilege, 'S' as priv_type, r.role from dba_sys_privs s join dba_roles r
 22      on s.grantee=r.role
 23      join (select granted_role from dba_role_privs
 24      connect by prior granted_role=grantee start with grantee in (select * from grantees)) rr
 25      on r.role=rr.granted_role
 26      union
 27      -- list all object privilege granted through roles to grantees
 28      select t.privilege||' on '|| t.owner||'.'||t.table_name privilege, 'O' as priv_type, r.role
 29      from dba_tab_privs t join dba_roles r on t.grantee=r.role
 30      join (select granted_role from dba_role_privs
 31      connect by prior granted_role=grantee start with grantee in (select * from grantees)) rr
 32      on r.role=rr.granted_role
 33  )
 34  group by priv_type, privilege
 35  order by priv_type DESC, privilege;

                           PRIVILEGE                                                 ROLE_LIST
____________________________________ _________________________________________________________
ADMINISTER ANY SQL TUNING SET        DBA,EM_EXPRESS_ALL
ADMINISTER DATABASE TRIGGER          DBA,IMP_FULL_DATABASE
ADMINISTER RESOURCE MANAGER          DBA,EM_EXPRESS_ALL,EXP_FULL_DATABASE,IMP_FULL_DATABASE
ADMINISTER SQL MANAGEMENT OBJECT     DBA,EM_EXPRESS_ALL,EXP_FULL_DATABASE,IMP_FULL_DATABASE
ADMINISTER SQL TUNING SET            DBA,EM_EXPRESS_ALL
ADVISOR                              DBA,EM_EXPRESS_ALL
ALTER ANY ANALYTIC VIEW              DBA
ALTER ANY ASSEMBLY                   DBA
ALTER ANY ATTRIBUTE DIMENSION        DBA
ALTER ANY CLUSTER                    DBA
ALTER ANY CUBE                       DBA
ALTER ANY CUBE BUILD PROCESS         DBA
ALTER ANY CUBE DIMENSION             DBA
ALTER ANY DIMENSION                  DBA
ALTER ANY EDITION                    DBA
ALTER ANY EVALUATION CONTEXT         DBA
ALTER ANY HIERARCHY                  DBA
ALTER ANY INDEX                      DBA
ALTER ANY INDEXTYPE                  DBA
ALTER ANY LIBRARY                    DBA
ALTER ANY MATERIALIZED VIEW          DBA
ALTER ANY MEASURE FOLDER             DBA
ALTER ANY MINING MODEL               DBA
ALTER ANY OPERATOR                   DBA
ALTER ANY OUTLINE                    DBA
ALTER ANY PROCEDURE                  DBA,IMP_FULL_DATABASE
ALTER ANY ROLE                       DBA,EM_EXPRESS_ALL
.... (omitted)
UPDATE on XDB.XDB$CHECKOUTS                     DBA
UPDATE on XDB.XDB$CONFIG                        DBA,XDBADMIN
UPDATE on XDB.XDB$D_LINK                        DBA
UPDATE on XDB.XDB$H_INDEX                       DBA
UPDATE on XDB.XDB$H_LINK                        DBA
UPDATE on XDB.XDB$NLOCKS                        DBA
UPDATE on XDB.XDB$RESCONFIG                     DBA
UPDATE on XDB.XDB$RESOURCE                      DBA
WRITE on SYS.DATA_PUMP_DIR                      EXP_FULL_DATABASE,IMP_FULL_DATABASE

5,323 rows selected.