Query for All tables and privileges under a specific role in ORACLE?

Question

What is the query to get all objects with privileges under a specific role.

ROLE_NAME  TABLE SELECT INSERT DELETE UPDATE EXECUTE

possible duplicate of [How to show all privileges from a user in oracle](http://stackoverflow.com/questions/9811670/how-to-show-all-privileges-from-a-user-in-oracle) — Preet Sangha, Apr 14 '14 at 05:51

score 6 · Answer 1 · answered Apr 14 '14 at 06:42

6

SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE='ROLE_NAME';

DBA_TAB_PRIVS describes all object grants in the database.

Or to get the grants for all the roles, you can use below query.

SELECT grantee role_name,table_name,privilege FROM dba_tab_privs dtp,dba_roles dr WHERE dtp.grantee=dr.role ORDER BY role_name,table_name,privilege;

answered Apr 14 '14 at 06:42

Krishna

Note, this does not cover System Privileges like `SELECT ANY TABLE`. Also you can grant a ROLE to another ROLE, this is not covered, either. – Wernfried Domscheit Apr 14 '14 at 07:29

1 Answers1