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 show temptable grants etc. so there's my question:

How can I list all grants on an Oracle DB?

Answer 1

Following are some additional queries, if you want more than just direct table grants such as grants via roles, system privileges like select any table and so on :

Use this for 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;

Try the code given below, for 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;

For indirect grants to tables/views follow this:

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;