How to find out if select grant is obtained directly or through a role
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 th开发者_JS百科rough 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?
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.
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.
精彩评论