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
|
1 |
SQL> select * from ; |
Check Object Privileges Granted to a Role or User
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
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# |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
/* 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#; |
