0 votes
1 view
in SQL by (16.2k points)

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?

1 Answer

0 votes
by (34.9k points)

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;

...