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

Question

One of the pitfalls in Oracle is the fact that sometimes you can choose from a table if you run a query in SQLplus, but you cannot execute a query from a stored procedure. To execute a request from a stored procedure, you need a direct grant for the object, not a grant received through the role.

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

Answer 1

Take a look at ALL_TAB_PRIVS:

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

All recipients, whether roles or users, are displayed here.

Answer 2

One way to see exactly what the procedure sees is to issue a command:

 SET ROLE none 

Disables all roles for your current session.