How To Check Object Privileges Granted to a Role or User in Oracle

dbtut October 17, 2019 ORACLE

To perform various operations on database objects, system and object privileges must be assigned to users.

Each system privilege allows a user to perform certain database operations.

Object privileges allow a user to execute specific operations on a specific object.

The following queries can be used to determine which system or object privileges are granted to which role or users:

Check System Privileges Granted to a Role or User

SQL> select * from ;

1	SQL> select * from ;

Check Object Privileges Granted to a Role or User

SQL> /* Formatted on 10/12/2018 21:27:57 (QP5 v5.326) */
SELECT ue.name                                             grantee,
       u.name                                              owner,
       o.name                                              object_name,
       ur.name                                             grantor,
       tpm.name                                            privilege,
       DECODE (MOD (oa.option$, 2), 1, 'YES', 'NO')        grantable,
       DECODE (BITAND (oa.option$, 2), 2, 'YES', 'NO')     heirarchy,
       OBJECT_TYPE
  FROM sys.objauth$         oa,
       sys.obj$             o,
       sys.user$            u,
       sys.user$            ur,
       sys.user$            ue,
       table_privilege_map  tpm,
       DBA_OBJECTS
 WHERE     oa.obj# = o.obj#
       AND oa.grantor# = ur.user#
       AND oa.grantee# = ue.user#
       AND oa.col# IS NULL
       AND oa.privilege# = tpm.privilege
       AND u.user# = o.owner#

SQL> /* Formatted on 10/12/2018 21:27:57 (QP5 v5.326) */

SELECT ue.name grantee,

u.name owner,

o.name object_name,

ur.name grantor,

tpm.name privilege,

DECODE (MOD (oa.option$, 2), 1, 'YES', 'NO') grantable,

DECODE (BITAND (oa.option$, 2), 2, 'YES', 'NO') heirarchy,

OBJECT_TYPE

FROM sys.objauth$ oa,

sys.obj$ o,

sys.user$ u,

sys.user$ ur,

sys.user$ ue,

table_privilege_map tpm,

DBA_OBJECTS

WHERE oa.obj# = o.obj#

AND oa.grantor# = ur.user#

AND oa.grantee# = ue.user#

AND oa.col# IS NULL

AND oa.privilege# = tpm.privilege

AND u.user# = o.owner#

Using this query, a view similar to the DBA_SYS_PRIVS view can be generated as follows.

/* Formatted on 10/12/2018 21:39:34 (QP5 v5.326) */
CREATE OR REPLACE VIEW DBA_OBJ_PRIVS
AS
    SELECT ue.name                                             grantee,
           u.name                                              owner,
           o.name                                              object_name,
           ur.name                                             grantor,
           tpm.name                                            privilege,
           DECODE (MOD (oa.option$, 2), 1, 'YES', 'NO')        grantable,
           DECODE (BITAND (oa.option$, 2), 2, 'YES', 'NO')     heirarchy,
           OBJECT_TYPE
      FROM sys.objauth$         oa,
           sys.obj$             o,
           sys.user$            u,
           sys.user$            ur,
           sys.user$            ue,
           table_privilege_map  tpm,
           DBA_OBJECTS
     WHERE     oa.obj# = o.obj#
           AND oa.grantor# = ur.user#
           AND oa.grantee# = ue.user#
           AND oa.col# IS NULL
           AND oa.privilege# = tpm.privilege
           AND u.user# = o.owner#
           AND DBA_OBJECTS.OBJECT_ID = oa.obj#;

/* Formatted on 10/12/2018 21:39:34 (QP5 v5.326) */

CREATE OR REPLACE VIEW DBA_OBJ_PRIVS