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

Question

I am using Linux  Oracle10g  I have created one user called test  and granted create session and select any dictionary permission to the same user   i also granted sysdba and sysoper roles to the same users   Now i want to display all the privileges and roles granted to the user   I found following query but it shows only create session and select dictionary privileges   select privilege  from dba sys privs  where grantee  SAMPLE   order by 1    please help to resolve the issue   Thanks

User · Answer

None of the other answers worked for me so I wrote my own solution:

As of Oracle 11g.

Replace USER with the desired username

Granted Roles:

SELECT * 
  FROM DBA_ROLE_PRIVS 
 WHERE GRANTEE = 'USER';

Privileges Granted Directly To User:

SELECT * 
  FROM DBA_TAB_PRIVS 
 WHERE GRANTEE = 'USER';

Privileges Granted to Role Granted to User:

SELECT * 
  FROM DBA_TAB_PRIVS  
 WHERE GRANTEE IN (SELECT granted_role 
                     FROM DBA_ROLE_PRIVS 
                    WHERE GRANTEE = 'USER');

Granted System Privileges:

SELECT * 
  FROM DBA_SYS_PRIVS 
 WHERE GRANTEE = 'USER';

If you want to lookup for the user you are currently connected as, you can replace DBA in the table name with USER and remove the WHERE clause.

User · Answer

SELECT    FROM DBA ROLE PRIVS  WHERE UPPER GRANTEE  LIKE   XYZ

User · Answer

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

User · Answer

always make SQL re-usuable  -    --                                                     --  amp role name will be  enter value for  role name    -- Date   2015 NOV 11   -- sample code    define role name  amp role name -- sample code    where role like    amp  amp role name   --                                                       define role name  amp role name  select   from ROLE ROLE PRIVS where ROLE     amp  amp role name   select   from ROLE SYS PRIVS  where ROLE     amp  amp role name     select role  privilege count     from ROLE TAB PRIVS where ROLE     amp  amp role name  group by role  privilege order by role  privilege asc

User · Answer

IF privileges are given to a user through some roles  then below SQL can be used  select   from ROLE ROLE PRIVS where ROLE    ROLE NAME   select   from ROLE TAB PRIVS  where ROLE    ROLE NAME   select   from ROLE SYS PRIVS  where ROLE    ROLE NAME

User · Answer

Combining the earlier suggestions to determine your personal permissions  ie  USER  permissions   then use this   -- your permissions select   from USER ROLE PRIVS where USERNAME  USER  select   from USER TAB PRIVS where Grantee   USER  select   from USER SYS PRIVS where USERNAME   USER   -- granted role permissions select   from ROLE ROLE PRIVS where ROLE IN  select granted role from USER ROLE PRIVS where USERNAME  USER   select   from ROLE TAB PRIVS  where ROLE IN  select granted role from USER ROLE PRIVS where USERNAME  USER   select   from ROLE SYS PRIVS  where ROLE IN  select granted role from USER ROLE PRIVS where USERNAME  USER

User · Answer

select    from ROLE TAB PRIVS  where role in       select granted role     from dba role privs      where granted role in   ROLE1   ROLE2

User · Answer

In addition to VAV s answer  The first one was most useful in my environment  select   from USER ROLE PRIVS where USERNAME  SAMPLE   select   from USER TAB PRIVS where Grantee    SAMPLE   select   from USER SYS PRIVS where USERNAME    SAMPLE

User · Answer

The only visible result I was able to understand was first to connect with the user I wanted to get the rights  then with the following query  SELECT GRANTEE  PRIVILEGE  TABLE NAME FROM USER TAB PRIVS

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

Examples related to oracle

Examples related to security

Examples related to oracle10g

Examples related to user-accounts