Python241820 Python241820 - 12 days ago 6
SQL Question

Show objects and privileges affected by role [ORACLE]

How to display objects and privileges that are affected by a specific role?

I have managed to display that information for the tables with this query:

SELECT PRIVILEGE, TABLE_NAME from ROLE_TAB_PRIVS WHERE ROLE='MYROLE';


My question is how to perform this query with all objects in the database.

Answer

Object privileges granted by role:

SELECT * FROM dba_tab_privs WHERE grantee = 'MYROLE';

System privileges granted by role

SELECT * FROM dba_sys_privs WHERE grantee = 'MYROLE';

other roles granted by role:

SELECT * FROM dba_role_privs WHERE grantee = 'MYROLE';