How to List All Grants a User Received

How can I list ALL grants a user received?

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;

How to show all privileges from a user in oracle?

You can try these below views.

SELECT * FROM USER_SYS_PRIVS; 
SELECT * FROM USER_TAB_PRIVS;
SELECT * FROM USER_ROLE_PRIVS;

DBAs and other power users can find the privileges granted to other users with the DBA_ versions of these same views. They are covered in the documentation .

Those views only show the privileges granted directly to the user. Finding all the privileges, including those granted indirectly through roles, requires more complicated recursive SQL statements:

select * from dba_role_privs connect by prior granted_role = grantee start with grantee = '&USER' order by 1,2,3;
select * from dba_sys_privs  where grantee = '&USER' or grantee in (select granted_role from dba_role_privs connect by prior granted_role = grantee start with grantee = '&USER') order by 1,2,3;
select * from dba_tab_privs  where grantee = '&USER' or grantee in (select granted_role from dba_role_privs connect by prior granted_role = grantee start with grantee = '&USER') order by 1,2,3,4;

How to find the privileges and roles granted to a user in Oracle?

Look at http://docs.oracle.com/cd/B10501_01/server.920/a96521/privs.htm#15665

Check USER_SYS_PRIVS, USER_TAB_PRIVS, USER_ROLE_PRIVS tables with these select statements

SELECT * FROM USER_SYS_PRIVS; 
SELECT * FROM USER_TAB_PRIVS; 
SELECT * FROM USER_ROLE_PRIVS;

How to list all grants of select, insert, delete or update for a user

Pete Finnegan, Oracle security expert extrordinaire, has several different tools available that will help you answer these types of questions.

See:
http://www.petefinnigan.com/tools.htm

In particular, for the question above, see find_all_privs.sql

Hope that helps.

How to list all users with SELECT ANY TABLE permission in Oracle?

SELECT ANY TABLE is a system privilege. So to answer your question you need to query the static data dictionary view DBA_SYS_PRIVS.
You'll need to be a DBA or power user to query this view.

select grantee
from dba_sys_privs
where privilege = 'SELECT ANY TABLE';

How to find missing grant on all tables for one role

SELECT table_name
  FROM dba_tables
 WHERE owner = 'STUDENT'
 AND table_name NOT IN
     (SELECT table_name
        FROM dba_tab_privs
       WHERE owner = 'STUDENT'
         AND privilege = 'SELECT'
         AND grantee = 'STUDENT_DBA');

This will return all tables in the STUDENT schema that do not have select permissions directly granted to the STUDENT_DBA role.

---

Related Topics