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

You can use [Data Dictionary Documentation](http://stackoverflow.com/documentation/oracle/7347/data-dictionary#t=201610091552406899855) — Slava Babin, Oct 09 '16 at 16:13

score 85 · Answer 1 · edited Jan 15 '14 at 21:42

85

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

edited Jan 15 '14 at 21:42

Shiva

20,575
14
82
112

answered Jan 15 '14 at 21:21

user2668478

912
6
4

8

+1 very complete. Put "user" instead of sample to get info on current logged user – daitangio Apr 07 '14 at 09:55
4

Note: if you're using `dba_XXX_privs`, the column name is `grantee` instead of `username`. – Mr. Llama Nov 01 '16 at 15:44

score 72 · Accepted Answer · edited Sep 18 '19 at 08:42

72

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;

edited Sep 18 '19 at 08:42

bugybunny

544
5
13

answered Feb 25 '13 at 12:01

VAV

1,756
1
16
26

39

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. – vapcguy May 05 '17 at 22:01
12

For the lazy ones out there: `SELECT * FROM USER_SYS_PRIVS;` `SELECT * FROM USER_TAB_PRIVS;` `SELECT * FROM USER_ROLE_PRIVS;` – Victor Romano Dec 03 '17 at 15:00
1

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" – Adrián Jaramillo Dec 08 '19 at 14:24

Mocking · Answer 3 · 2016-05-25T02:31:50.810

71

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.

edited May 25 '16 at 02:31

answered Apr 01 '16 at 02:52

Mocking

1,764
2
20
36

2

This is the best answer. Thanks. – Baodad Jul 05 '17 at 17:20
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'); – em_bo Nov 21 '18 at 19:29

score 16 · Answer 4 · answered Oct 28 '15 at 16:49

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

I think that this one is the best answer. It works really good for me. — Diego Romero, Jun 25 '19 at 10:42

score 10 · Answer 5 · answered Dec 16 '14 at 15:28

10

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

answered Dec 16 '14 at 15:28

upog

4,965
8
42
81

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. – nanosoft Mar 19 '16 at 06:45

score 3 · Answer 6 · edited Jul 24 '13 at 16:40

3

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

edited Jul 24 '13 at 16:40

bensiu

24,660
56
77
117

answered Jul 24 '13 at 16:20

user2615480

65
1

score 1 · Answer 7 · edited Jun 18 '14 at 16:04

1

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

edited Jun 18 '14 at 16:04

nbrooks

18,126
5
54
66

answered Jan 15 '14 at 09:16

shans

19
1

score 0 · Answer 8 · answered Nov 11 '15 at 23:23

0

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
;

answered Nov 11 '15 at 23:23

dave

296
1
12
26

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. – vapcguy May 05 '17 at 22:07

score 0 · Answer 9 · edited Oct 08 '20 at 15:39

0

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;

edited Oct 08 '20 at 15:39

aboger

2,214
6
33
47

answered Oct 08 '20 at 13:15

Pierre Hamon

1

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

9 Answers9

Linked

Related