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?
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;