DB version :11.2
I couldn't find a DBA_ view which would list all the privileges granted to a role. Finally I had to grant the role to a user and then connect as that granted user and then query ROLE_TAB_PRIVS view. As a DBA , I can't login into business schemas to check this.
The scenario
==============
SCOTT schema has two tables : HRTB_EMP_MASTER and HELLOWORLD
I want to grant SELECT privileges on these two tables to another user called TESTUSER but not directly ; through roles
SQL> conn / as sysdba
Connected.
SQL> grant create role to testuser;
Grant succeeded.
SQL> conn testuser/test123
Connected.
SQL>
SQL> create role testuser_ro;
Role created.
SQL> conn / as sysdba
Connected.
SQL> grant select on scott.hrtb_emp_master to testuser_ro; --- > Granting the SELECT priv to the role first
Grant succeeded.
SQL> grant select on scott.helloworld to testuser_ro;
Grant succeeded.
SQL> SELECT ROLE, OWNER, TABLE_NAME, PRIVILEGE FROM ROLE_TAB_PRIVS where owner = 'SCOTT'; ----> This won't work because I am connected as SYS
----> ROLE_TAB_PRIVS is user specific view
no rows selected
Since I couldn't find a DBA view which will the privileges granted to a role , I granted the role to the user I had to login to the user (against our security policy) and query
ROLE_TAB_PRIVS.
SQL> grant testuser_ro to testuser;
Grant succeeded.
SQL> SELECT ROLE, OWNER, TABLE_NAME, PRIVILEGE FROM ROLE_TAB_PRIVS where owner = 'SCOTT';
no rows selected
SQL> conn testuser/test123
Connected.
SQL> SELECT ROLE, OWNER, TABLE_NAME, PRIVILEGE FROM ROLE_TAB_PRIVS where owner = 'SCOTT';
ROLE OWNER TABLE_NAME PRIVILEGE
--------------- --------------- -------------------- ----------
TESTUSER_RO SCOTT HELLOWORLD SELECT
TESTUSER_RO SCOTT HRTB_EMP_MASTER SELECT