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

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

Stirps

You can use Data Dictionary Documentation – Savarin

Answer 1

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;

Boyle

In other words, do SELECT * FROM those tables. Let's not get lazy... I get upset when I don't see code. Not everyone can read your mind for exact syntax, and documentation links can get moved. – Discontinue

For the lazy ones out there: SELECT * FROM USER_SYS_PRIVS; SELECT * FROM USER_TAB_PRIVS; SELECT * FROM USER_ROLE_PRIVS; – Abib

if you are a person looking for roles granted to "whatever in the database" like me, USER_ROLE_PRIVS won't work for you because: "USER_ROLE_PRIVS describes the roles granted to the current user", like the Oracle documentation says. Instead, you should be looking for information in the DBA_ROLE_PRIVS view. ¿Why? Because: "DBA_ROLE_PRIVS describes the roles granted to all users and roles in the database" – Malm

Answer 2

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

Allanallana

+1 very complete. Put "user" instead of sample to get info on current logged user – Jankey

Note: if you're using dba_XXX_privs, the column name is grantee instead of username. – Ibbetson

Answer 3

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.

Operator

This is the best answer. Thanks. – Johathan

Here's one more to round it out - System Privileges Granted to Role Granted to User: SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE IN (SELECT granted_role FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'DWMGR'); – Late

This is the actual answer, USER_ROLE_PRIVS etc is for that logged in user as stated above. – Coach

Answer 4

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

Soche

Excellent! This ought to be the real answer! – Discontinue

I think that this one is the best answer. It works really good for me. – Cointreau

Answer 5

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

Compose

very helpful to dig deeper when u have rights provided via roles.. Second query helped me alot to check if one our table access right is provided to me. – Hotfoot

Answer 6

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

Gossoon

Answer 7

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

Bierce

Answer 8

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
;

Couturier

Only checks the ROLES group for a given role. If we're talking reusability, it should 1) loop through the USER group and spit out * from USER_ROLE_PRIVS, USER_TAB_PRIVS, and USER_SYS_PRIVS , for a given user, then 2) give the roles for the given user that is input (like in ShamrockCS' answer). I think given the OP's question, it's where we want to know the properties of a given user, not the users with a given role. – Discontinue

Answer 9

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;

Tablecloth