Here’s a couple of queries that can be used to see privileges granted through roles.
System Privileges:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
with cte (x) as ( select 'JOHNSMITH' x from dual ) select distinct privilege from dba_sys_privs where grantee in (select granted_role from dba_role_privs start with grantee = (select x from cte) connect by grantee = prior granted_role --------- union all --------- select x from cte); |
Object Privileges:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
with cte (x) as ( select 'JOHNSMITH' x from dual ) select distinct privilege, owner, table_name from dba_tab_privs where grantee in (select granted_role from dba_role_privs start with grantee = (select x from cte) connect by grantee = prior granted_role --------- union all --------- select x from cte); |