How can I list ALL grants a user received?

Question

I need to see all grants on an Oracle DB.

I used the TOAD feature to compare schemas but it does not shows temptable grants etc. so there's my question:

How can I list all grants on a Oracle DB?

Answer 1

Assuming you want to list grants on all objects a particular user has received:

select * from all_tab_privs_recd where grantee = 'your user'

This will not return objects owned by the user. If you need those, use all_tab_privs view instead.

Answer 2

If you want more than just direct table grants (e.g., grants via roles, system privileges such as select any table, etc.), here are some additional queries:

System privileges for a user:

SELECT PRIVILEGE
  FROM sys.dba_sys_privs
 WHERE grantee = <theUser>
UNION
SELECT PRIVILEGE 
  FROM dba_role_privs rp JOIN role_sys_privs rsp ON (rp.granted_role = rsp.role)
 WHERE rp.grantee = <theUser>
 ORDER BY 1;

Direct grants to tables/views:

SELECT owner, table_name, select_priv, insert_priv, delete_priv, update_priv, references_priv, alter_priv, index_priv 
  FROM table_privileges
 WHERE grantee = <theUser>
 ORDER BY owner, table_name;

Indirect grants to tables/views:

SELECT DISTINCT owner, table_name, PRIVILEGE 
  FROM dba_role_privs rp JOIN role_tab_privs rtp ON (rp.granted_role = rtp.role)
 WHERE rp.grantee = <theUser>
 ORDER BY owner, table_name;