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

Question

I am using Linux, Oracle10g. I created one user called test. and provided to create a session and select any dictionary for the same user.

i also provided sysdba and sysoper roles to the same users.

Now I want to display all privileges and roles granted to the user. I found the following query, but it only shows session creation and dictionary privilege selection.

select privilege from dba_sys_privs where grantee='SAMPLE' order by 1; 

please help solve the problem.

thank

Answer 1

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

Check tables USER_SYS_PRIVS, USER_TAB_PRIVS, USER_ROLE_PRIVS with these selection statements

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

Answer 2

In addition to the VAV 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'; 

Answer 3

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

Starting with Oracle 11g.

Replace USER with your username

Roles provided:

 SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'USER'; 

Privileges granted directly to the user:

 SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE = 'USER'; 

Privileges granted to the user:

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

System privileges granted:

 SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = 'USER'; 

If you want to find the user you are currently connected to, you can replace the DBA in the USER table name and delete the WHERE clause.

Answer 4

The granted IF privileges are granted to the user through some roles, then under SQL you can use

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

Answer 5

Combining earlier sentences to define your personal permissions (e.g. USER permissions), use the following:

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

Answer 6

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

Answer 7

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

Answer 8

always makes SQL re-accessible: - :)

 -- =================================================== -- &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 ;