What privileges does a role grant?

Question

I have an Oracle database with some user defined roles. I would like to create a new role with very similar privileges (it would be the same role for a different schema) , but I can't figure out what privileges does this role grant.

Is there a query which I can use to figure that out?

(I tried

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

from an answer for another question, but I got no rows selected.)

Are you using the literal string `'USER'` or are you substituting an actual user name value? (If you're looking for your own user, you can use the unquoted `USER` function; but putting quotes around it makes it a literal value). The question you linked to said to "Replace USER with the desired username" but i'm not sure you have done that... also remember that the case of the user name has to match what's in the `dba_users` view, and is usually uppercase. — Alex Poole, Sep 26 '16 at 09:21
I did replace the 'USER' part with both an upper case and lower case version — ytg, Sep 26 '16 at 09:59
OK, this is awkward... I accidentally ran the same query again and now I have 83 rows selected. I must have mistyped the username for the first time. — ytg, Sep 26 '16 at 10:10
Now the only thing to figure out is how to get the info I need from the mess that sql plus produces — ytg, Sep 26 '16 at 10:15

Slava Babin · Answer 1 · 2016-09-27T09:10:03.103

1

If you got no rows selected, then maybe you do not have any granted roles. Maybe you have privileges granted directly to user:

 SELECT * FROM DBA_TAB_PRIVS 
 WHERE GRANTEE = 'USER';

Make sure that you have access to this view.

UPD Alex Poole

You can have the select catalog role.

edited Sep 27 '16 at 09:10

answered Sep 26 '16 at 08:45

Slava Babin

708
2
12
30

`no rows selected` – ytg Sep 26 '16 at 08:48
But `SELECT * FROM USER_ROLE_PRIVS;` with the user seems to give me some roles... – ytg Sep 26 '16 at 08:53
1

Try this - SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE IN (SELECT granted_role FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'USER'); – Slava Babin Sep 26 '16 at 09:02
`no rows selected` – ytg Sep 26 '16 at 09:14
1

You don't have to be connected as sysdba to see the DBA data dictionary views. You can have [the `select catalog` role](https://docs.oracle.com/cd/E11882_01/network.112/e36292/authorization.htm#DBSEG99873), for instance, as an otherwise non-DBA user.. – Alex Poole Sep 26 '16 at 09:30
Yes, you are right, more correctly will be "make sure that you have access to this views" – Slava Babin Sep 26 '16 at 09:34

score 1 · Answer 2 · edited Sep 15 '17 at 13:06

1

If you want to crate new user with the same privileges you can just grant him same roles, system and object privileges:

SELECT granted_role
FROM DBA_ROLE_PRIVS
WHERE GRANTEE = 'USER'

and then grant the roles to new_user

GRANT ROLE_NAME TO NEW_USER

To select privileges granted directly to user:

SELECT * FROM DBA_SYS_PRIVS 
WHERE GRANTEE='USER'

and

SELECT * FROM DBA_TAB_PRIVS 
WHERE GRANTEE = 'USER';

If you want to find out what privileges particular role gives you can execute

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

and

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

Take a look at Privilege information in data Dictionary

edited Sep 15 '17 at 13:06

MT0

143,790
11
59
117

answered Sep 26 '16 at 09:24

Slava Babin

708
2
12
30

Do not create a new answer, edit your existing answer – Thomas G Sep 26 '16 at 09:36
Most of this is already in the answer that was linked to in the question, and doesn't explain why the OP got no data back. – Alex Poole Sep 26 '16 at 09:38
1

OP said that he tried one query. And that SELECT * FROM USER_ROLE_PRIVS; gives some roles. So my answer is just to grant this roles if OP needs same privileges or to use other query. – Slava Babin Sep 26 '16 at 09:48

Alex Poole · Accepted Answer · 2016-09-26T10:47:28.810

The value you substitute for 'USER' has to be a valid user name, in uppercase (usually - unless the user was created with a quoted identifier) to match the user's entry in DBA_USERS.

If you're trying to duplicate the role you might find it easier to extract the DDL and dependent DDL using the DBMS_MATADATA package, something like:

set long 32767

exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'SQLTERMINATOR', true);

select dbms_metadata.get_ddl('ROLE', granted_role)
from dba_role_privs where grantee = 'USER';

select dbms_metadata.get_granted_ddl('SYSTEM_GRANT', granted_role)
from dba_role_privs drp
where grantee = 'USER'
and exists (
  select null from dba_sys_privs dsp where dsp.grantee = drp.granted_role
);

select dbms_metadata.get_granted_ddl('OBJECT_GRANT', granted_role)
from dba_role_privs drp
where grantee = 'USER'
and exists (
  select null from dba_tab_privs dtp where dtp.grantee = drp.granted_role
);
and then edit the output to change names and schema etc. as required.

With multiple roles the output won't be in quite the order you might like, but you could potentially use a cursor and write to a file if that's an issue. And if you have nested roles you'll need to identify those and then get their dependent DDL as well.

I have a feeling that this is what I'm looking for, but I get `ORA-31608: specified object of type OBJECT_GRANT not found` and `ORA-31608: specified object of type SYSTEM_GRANT not found` errors. (Which is a bit funny, because I get actual results with OBJECT_GRANT before the error comes. — ytg, Sep 26 '16 at 10:38
@yfg - I think that means you have at least one role with only system grants, and one role with only object grants? Although neither query should produce any output in that case... unless you're doing this in a PL/SQL loop maybe. I've added an `exists` check that will hopefully avoid that being a problem. — Alex Poole, Sep 26 '16 at 10:44

What privileges does a role grant?

3 Answers3