Oracle PL/SQL/System Tables Views/dba role privs

Query DBA_ROLE_PRIVS, ROLE_TAB_PRIVS for Name of the object, Privilege granted,Was admin option granted

 

SQL> select
  4        ROLE_TAB_PRIVS.Table_Name,     /*Name of the object*/
  5        ROLE_TAB_PRIVS.Privilege,      /*Privilege granted*/
  6        ROLE_TAB_PRIVS.Grantable       /*Was admin option granted?*/
  7   from DBA_ROLE_PRIVS, ROLE_TAB_PRIVS
  8  where DBA_ROLE_PRIVS.Granted_Role = ROLE_TAB_PRIVS.Role
  9    and DBA_ROLE_PRIVS.Grantee = "some username";
SQL>
SQL>
SQL>

Query DBA_ROLE_PRIVS, ROLE_TAB_PRIVS for Recipient of the grant,Owner of the object

 
SQL> select
  2        DBA_ROLE_PRIVS.Grantee,        /*Recipient of the grant*/
  3        ROLE_TAB_PRIVS.Owner          /*Owner of the object*/
  7   from DBA_ROLE_PRIVS, ROLE_TAB_PRIVS
  8  where DBA_ROLE_PRIVS.Granted_Role = ROLE_TAB_PRIVS.Role
  9    and DBA_ROLE_PRIVS.Grantee = "some username";
SQL>
SQL>
SQL>

Query dba_role_privs table

  
SQL>
SQL> set verify off
SQL>
SQL> column grole format a20 heading "Role Granted"
SQL> column wadmin format a6 heading "Admin?"
SQL>
SQL> select granted_role grole, initcap(admin_option) wadmin
  2    from dba_role_privs
  3    where rownum < 50
  4  /
Role Granted         Admin?
-------------------- ------
CONNECT              No
RESOURCE             No
XDBADMIN             No
PLUSTRACE            Yes
XDBWEBSERVICES       No
SCHEDULER_ADMIN      Yes
EXP_FULL_DATABASE    No
IMP_FULL_DATABASE    No
DELETE_CATALOG_ROLE  Yes
SELECT_CATALOG_ROLE  Yes
EXECUTE_CATALOG_ROLE Yes
GATHER_SYSTEM_STATIS No
TICS
DBA                  Yes
CTXAPP               Yes
CONNECT              Yes
RESOURCE             Yes
XDBADMIN             Yes
PLUSTRACE            Yes
OEM_ADVISOR          Yes
OEM_MONITOR          Yes
AQ_USER_ROLE         Yes
HS_ADMIN_ROLE        Yes
XDBWEBSERVICES       Yes
SCHEDULER_ADMIN      Yes
AUTHENTICATEDUSER    Yes
EXP_FULL_DATABASE    Yes
IMP_FULL_DATABASE    Yes
DELETE_CATALOG_ROLE  Yes
SELECT_CATALOG_ROLE  Yes
EXECUTE_CATALOG_ROLE Yes
AQ_ADMINISTRATOR_ROL Yes
E
LOGSTDBY_ADMINISTRAT Yes
OR
RECOVERY_CATALOG_OWN Yes
ER
GATHER_SYSTEM_STATIS Yes
TICS
CTXAPP               No
RESOURCE             No
CONNECT              No
RESOURCE             No
RESOURCE             No
CTXAPP               Yes
RESOURCE             No
OEM_MONITOR          No
DBA                  No
DBA                  Yes
AQ_ADMINISTRATOR_ROL Yes
E
RESOURCE             No
XDBWEBSERVICES       No
CONNECT              No
RESOURCE             No
49 rows selected.
SQL>
SQL> --

Query user granted roles

 
SQL>
SQL> 
SQL> COL grantee          FORMAT A8
SQL> COL granted_role     FORMAT A30
SQL> COL grantor          FORMAT A8
SQL> COL privilege        FORMAT A12
SQL> COL owner            FORMAT A4
SQL> COL table_name       FORMAT A30
SQL>
SQL> 
SQL> SELECT   grantee
  2  ,        granted_role
  3  FROM     dba_role_privs
  4  WHERE    grantee = "PLSQL";
GRANTEE  GRANTED_ROLE
-------- ------------------------------
PLSQL    CTXAPP
PLSQL    CONNECT
PLSQL    RESOURCE
3 rows selected.
SQL>
SQL> -- Query resources.
SQL> SELECT   grantor
  2  ,        owner
  3  ,        table_name
  4  ,        grantee
  5  ,        privilege
  6  FROM     dba_tab_privs
  7  WHERE    grantee = "PLSQL";
no rows selected