Oracle PL/SQL Tutorial/System Tables Data Dictionary/dba role privs

Direct Role Grants to Users

SQL>
SQL> SELECT b.granted_role ||
  2         DECODE(admin_option, "YES",
  3         " (With Admin Option)",
  4         NULL) what_granted, a.username
  5  FROM   sys.dba_users a, sys.dba_role_privs b
  6  WHERE  a.username = b.grantee
  7  and    rownum < 50
  8  ORDER BY 1;
WHAT_GRANTED
--------------------------------------------------
USERNAME
------------------------------
AQ_ADMINISTRATOR_ROLE (With Admin Option)
SYSTEM
AQ_ADMINISTRATOR_ROLE (With Admin Option)
SYS
AQ_USER_ROLE (With Admin Option)
SYS

WHAT_GRANTED
--------------------------------------------------
USERNAME
------------------------------
AUTHENTICATEDUSER (With Admin Option)
SYS
CONNECT
MDSYS
CONNECT
PLSQL

WHAT_GRANTED
--------------------------------------------------
USERNAME
------------------------------
CONNECT
HR
CONNECT
FLOWS_FILES
CONNECT (With Admin Option)
SYS

WHAT_GRANTED
--------------------------------------------------
USERNAME
------------------------------
CONNECT (With Admin Option)
FLOWS_020100
CTXAPP
PLSQL
CTXAPP
XDB

WHAT_GRANTED
--------------------------------------------------
USERNAME
------------------------------
CTXAPP (With Admin Option)
CTXSYS
CTXAPP (With Admin Option)
SYS
DBA
sqle

WHAT_GRANTED
--------------------------------------------------
USERNAME
------------------------------
DBA (With Admin Option)
FLOWS_020100
DBA (With Admin Option)
SYS
DBA (With Admin Option)
SYSTEM

WHAT_GRANTED
--------------------------------------------------
USERNAME
------------------------------
DELETE_CATALOG_ROLE (With Admin Option)
SYS
EXECUTE_CATALOG_ROLE (With Admin Option)
SYS
EXP_FULL_DATABASE (With Admin Option)
SYS

WHAT_GRANTED
--------------------------------------------------
USERNAME
------------------------------
GATHER_SYSTEM_STATISTICS (With Admin Option)
SYS
HS_ADMIN_ROLE (With Admin Option)
SYS
IMP_FULL_DATABASE (With Admin Option)
SYS

WHAT_GRANTED
--------------------------------------------------
USERNAME
------------------------------
LOGSTDBY_ADMINISTRATOR (With Admin Option)
SYS
OEM_ADVISOR (With Admin Option)
SYS
OEM_MONITOR
DBSNMP

WHAT_GRANTED
--------------------------------------------------
USERNAME
------------------------------
OEM_MONITOR (With Admin Option)
SYS
PLUSTRACE (With Admin Option)
SYS
RECOVERY_CATALOG_OWNER (With Admin Option)
SYS

WHAT_GRANTED
--------------------------------------------------
USERNAME
------------------------------
RESOURCE
HR
RESOURCE
FLOWS_FILES
RESOURCE
PLSQL

WHAT_GRANTED
--------------------------------------------------
USERNAME
------------------------------
RESOURCE
CTXSYS
RESOURCE
XDB
RESOURCE
MDSYS

WHAT_GRANTED
--------------------------------------------------
USERNAME
------------------------------
RESOURCE
OUTLN
RESOURCE
TSMSYS
RESOURCE (With Admin Option)
FLOWS_020100

WHAT_GRANTED
--------------------------------------------------
USERNAME
------------------------------
RESOURCE (With Admin Option)
SYS
SCHEDULER_ADMIN (With Admin Option)
SYS
SELECT_CATALOG_ROLE
FLOWS_020100

WHAT_GRANTED
--------------------------------------------------
USERNAME
------------------------------
SELECT_CATALOG_ROLE (With Admin Option)
SYS
XDBADMIN (With Admin Option)
SYS
XDBWEBSERVICES (With Admin Option)
SYS

45 rows selected.
SQL>
SQL>

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>