How to find out if select grant is obtained directly or through a role

Question

One of the pitfalls in Oracle is the fact that sometimes you can select from a table if you run a query in SQLplus but that you can't when running the query from a stored procedure. In order to run a query from a stored procedure you need a direct grant for the object and not a grant obtained through a role.

If I see a table in the all_tables view, how can I know if I can see this table because of a direct grant or because of a role grant?

Answer 1

Look at ALL_TAB_PRIVS:

select grantee from all_tab_privs 
where table_schema = 'SCOTT' and table_name='EMP'
and privilege = 'SELECT';

This shows all grantees, whether roles or users.

Answer 2

One method to see exactly what a procedure would see is to issue the command:

SET ROLE none

It disables all roles for your current session.