Object Grants to Roles : dba_tab_privs « System Tables Data Dictionary « Oracle PL/SQL Tutorial






SQL>
SQL>
SQL> SELECT owner || '.' || table_name obj,
  2         privilege what_granted, grantable, grantee
  3  FROM   sys.dba_tab_privs
  4  WHERE  NOT EXISTS
  5  (SELECT  'x'
  6  FROM     sys.dba_users
  7  WHERE    username = grantee)
  8  and    rownum < 50
  9  ORDER BY 1,2,3;

OBJ
-------------------------------------------------------------
WHAT_GRANTED                             GRA GRANTEE
---------------------------------------- --- ------------------------------
OUTLN.OL$
SELECT                                   NO  SELECT_CATALOG_ROLE

OUTLN.OL$HINTS
SELECT                                   NO  SELECT_CATALOG_ROLE

OUTLN.OL$NODES
SELECT                                   NO  SELECT_CATALOG_ROLE


OBJ
-------------------------------------------------------------
WHAT_GRANTED                             GRA GRANTEE
---------------------------------------- --- ------------------------------
SYS.AUD$
DELETE                                   NO  DELETE_CATALOG_ROLE

SYS.AUX_STATS$
DELETE                                   NO  GATHER_SYSTEM_STATISTICS

SYS.AUX_STATS$
INSERT                                   NO  GATHER_SYSTEM_STATISTICS


OBJ
-------------------------------------------------------------
WHAT_GRANTED                             GRA GRANTEE
---------------------------------------- --- ------------------------------
SYS.AUX_STATS$
SELECT                                   NO  GATHER_SYSTEM_STATISTICS

SYS.AUX_STATS$
UPDATE                                   NO  GATHER_SYSTEM_STATISTICS

SYS.AW$
DEBUG                                    NO  DBA


OBJ
-------------------------------------------------------------
WHAT_GRANTED                             GRA GRANTEE
---------------------------------------- --- ------------------------------
SYS.AW$
SELECT                                   NO  DBA

SYS.AWSEQ$
ALTER                                    NO  DBA

SYS.AWSEQ$
SELECT                                   NO  DBA


OBJ
-------------------------------------------------------------
WHAT_GRANTED                             GRA GRANTEE
---------------------------------------- --- ------------------------------
SYS.AW_OBJ$
DEBUG                                    NO  DBA

SYS.AW_OBJ$
SELECT                                   NO  DBA

SYS.AW_PROP$
DEBUG                                    NO  DBA


OBJ
-------------------------------------------------------------
WHAT_GRANTED                             GRA GRANTEE
---------------------------------------- --- ------------------------------
SYS.AW_PROP$
SELECT                                   NO  DBA

SYS.DBMS_STANDARD
EXECUTE                                  NO  PUBLIC

SYS.DUAL
SELECT                                   YES PUBLIC


OBJ
-------------------------------------------------------------
WHAT_GRANTED                             GRA GRANTEE
---------------------------------------- --- ------------------------------
SYS.EXPIMP_TTS_CT$
DELETE                                   NO  IMP_FULL_DATABASE

SYS.EXPIMP_TTS_CT$
INSERT                                   NO  IMP_FULL_DATABASE

SYS.EXPIMP_TTS_CT$
SELECT                                   NO  IMP_FULL_DATABASE


OBJ
-------------------------------------------------------------
WHAT_GRANTED                             GRA GRANTEE
---------------------------------------- --- ------------------------------
SYS.EXPIMP_TTS_CT$
UPDATE                                   NO  IMP_FULL_DATABASE

SYS.FGA_LOG$
DELETE                                   NO  DELETE_CATALOG_ROLE

SYS.INCEXP
DELETE                                   NO  EXP_FULL_DATABASE


OBJ
-------------------------------------------------------------
WHAT_GRANTED                             GRA GRANTEE
---------------------------------------- --- ------------------------------
SYS.INCEXP
INSERT                                   NO  EXP_FULL_DATABASE

SYS.INCEXP
UPDATE                                   NO  EXP_FULL_DATABASE

SYS.INCFIL
DELETE                                   NO  EXP_FULL_DATABASE


OBJ
-------------------------------------------------------------
WHAT_GRANTED                             GRA GRANTEE
---------------------------------------- --- ------------------------------
SYS.INCFIL
INSERT                                   NO  EXP_FULL_DATABASE

SYS.INCFIL
UPDATE                                   NO  EXP_FULL_DATABASE

SYS.INCVID
DELETE                                   NO  EXP_FULL_DATABASE


OBJ
-------------------------------------------------------------
WHAT_GRANTED                             GRA GRANTEE
---------------------------------------- --- ------------------------------
SYS.INCVID
INSERT                                   NO  EXP_FULL_DATABASE

SYS.INCVID
UPDATE                                   NO  EXP_FULL_DATABASE

SYS.MAP_OBJECT
ALTER                                    NO  DBA


OBJ
-------------------------------------------------------------
WHAT_GRANTED                             GRA GRANTEE
---------------------------------------- --- ------------------------------
SYS.MAP_OBJECT
DEBUG                                    NO  DBA

SYS.MAP_OBJECT
DELETE                                   NO  DBA

SYS.MAP_OBJECT
FLASHBACK                                NO  DBA


OBJ
-------------------------------------------------------------
WHAT_GRANTED                             GRA GRANTEE
---------------------------------------- --- ------------------------------
SYS.MAP_OBJECT
INSERT                                   NO  DBA

SYS.MAP_OBJECT
ON COMMIT REFRESH                        NO  DBA

SYS.MAP_OBJECT
QUERY REWRITE                            NO  DBA


OBJ
-------------------------------------------------------------
WHAT_GRANTED                             GRA GRANTEE
---------------------------------------- --- ------------------------------
SYS.MAP_OBJECT
SELECT                                   NO  DBA

SYS.MAP_OBJECT
SELECT                                   NO  SELECT_CATALOG_ROLE

SYS.MAP_OBJECT
UPDATE                                   NO  DBA


OBJ
-------------------------------------------------------------
WHAT_GRANTED                             GRA GRANTEE
---------------------------------------- --- ------------------------------
SYS.PS$
DEBUG                                    NO  DBA

SYS.PS$
SELECT                                   NO  DBA

SYS.RE$NV_LIST
EXECUTE                                  NO  PUBLIC


OBJ
-------------------------------------------------------------
WHAT_GRANTED                             GRA GRANTEE
---------------------------------------- --- ------------------------------
SYS.STANDARD
EXECUTE                                  NO  PUBLIC

SYS.STMT_AUDIT_OPTION_MAP
SELECT                                   NO  PUBLIC

SYS.SYSTEM_PRIVILEGE_MAP
SELECT                                   YES PUBLIC


OBJ
-------------------------------------------------------------
WHAT_GRANTED                             GRA GRANTEE
---------------------------------------- --- ------------------------------
SYS.TABLE_PRIVILEGE_MAP
SELECT                                   YES PUBLIC


49 rows selected.

SQL>








30.42.dba_tab_privs
30.42.1.Object Grants to Users
30.42.2.Object Grants to Roles
30.42.3.Find out all dba table privileges
30.42.4.User Table Privileges report