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

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

9 Answers

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

Look at

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; 

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.

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

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

SELECT * FROM DBA_ROLE_PRIVS WHERE UPPER(GRANTEE) LIKE '%XYZ%'; 

select * from ROLE_TAB_PRIVS where role in ( select granted_role from dba_role_privs where granted_role in ('ROLE1','ROLE2') ) 

always make SQL re-usuable: -:)

-- =================================================== -- &role_name will be "enter value for 'role_name'". -- Date: 2015 NOV 11. -- sample code: define role_name=&role_name -- sample code: where role like '%&&role_name%' -- =================================================== define role_name=&role_name select * from ROLE_ROLE_PRIVS where ROLE = '&&role_name'; select * from ROLE_SYS_PRIVS where ROLE = '&&role_name'; select role, privilege,count(*) from ROLE_TAB_PRIVS where ROLE = '&&role_name' group by role, privilege order by role, privilege asc ; 

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;