Difference between query of privileges in tables

Question

Reading this answer I found a query to find the privileges of a table:

SELECT * FROM ALL_TAB_PRIVS WHERE TABLE_NAME = 'MY_TABLE'

But a friend give me to me this query:

select a.USERNAME,a.GRANTED_ROLE,b.ROLE,b.owner,b.TABLE_NAME,b.privilege
from user_role_privs a,role_tab_privs b
where b.ROLE=a.GRANTED_ROLE and b.TABLE_NAME = 'MY_TABLE';

There is some substantial difference between each query?

Answer 1

They are quite different, yes.

In Oracle, privileges on a table can be granted either directly to a user (in which case they would appear in ALL_TAB_PRIVS) or privileges can be granted to a role (visible in ROLE_TAB_PRIVS) and that role can be granted to a user (visible in USER_ROLE_PRIVS). The first query will show you the users that have direct grants on a table. The second query will show you the users that have been granted a role that has been granted access to the table (note that in both cases you really ought to specify an OWNER in addition to the table name). Neither will show you information about grants that have been made through multiple nested levels of roles (i.e. User A has been granted Role 1, Role 1 has been granted Role 2, Role 2 has been granted access to a table). Grants made via roles can also get a bit tricky because there are default and non-default roles and password protected roles and roles can be enabled and disabled in a session.

In general, I'd suggest taking a look at the scripts available on Pete Finnigan's site if you want to have something that covers all the possible cases. In this case, you probably want to use his who_can_access script to determine what users can access a particular table.