OraFAQ Forum: Security » To Find A List Of Roles and Privileges In Database

Home » RDBMS Server » Security » To Find A List Of Roles and Privileges In Database (Windows XP, Oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

To Find A List Of Roles and Privileges In Database [message #496118]

Thu, 24 February 2011 11:02

oraQ
Messages: 57
Registered: January 2011

Member

How can I list all the roles and privileges of roles in Oracle? Can I get the details of the activities they perform? Please guide.
Thanks.

[Updated on: Thu, 24 February 2011 11:03]

Report message to a moderator

Re: To Find A List Of Roles and Privileges In Database [message #496119 is a reply to message #496118]

Thu, 24 February 2011 11:14

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

You can use the below views to find role related informations,

DBA_ROLES - Roles in DB
DBA_ROLE_PRIVS - Roles granted to users and roles
ROLE_ROLE_PRIVS - Roles which are granted to roles
ROLE_SYS_PRIVS - System privileges granted to roles
ROLE_TAB_PRIVS - Table privileges granted to roles

Report message to a moderator

Re: To Find A List Of Roles and Privileges In Database [message #496120 is a reply to message #496118]

Thu, 24 February 2011 11:21

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

Can I get the details of the activities they perform?

A role does not perform any activity.

select table_name, comments from  dictionary where lower(comments) like '%role%';

Regards
Michel

Report message to a moderator

Re: To Find A List Of Roles and Privileges In Database [message #496219 is a reply to message #496120]

Fri, 25 February 2011 05:32

oraQ
Messages: 57
Registered: January 2011

Member

Helpful tips you shared both. One more thing, why there is error when dba_roles is used in prod whereas the same view can be used in dev? Thanks again for many useful tips.

Report message to a moderator

Re: To Find A List Of Roles and Privileges In Database [message #496220 is a reply to message #496219]

Fri, 25 February 2011 05:34

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

It depends on the error.

Regards
Michel

Report message to a moderator

Re: To Find A List Of Roles and Privileges In Database [message #496222 is a reply to message #496220]

Fri, 25 February 2011 05:39

oraQ
Messages: 57
Registered: January 2011

Member

Here is how I get the error:
ORA-00942: table or view does not exist
What does this mean w.r.t prod?

Report message to a moderator

Re: To Find A List Of Roles and Privileges In Database [message #496223 is a reply to message #496222]

Fri, 25 February 2011 05:47

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

You have not the privilege to query this view (or less likely the catalog is not installed).

Regards
Michel

[Updated on: Sat, 26 February 2011 10:12]

Report message to a moderator

Re: To Find A List Of Roles and Privileges In Database [message #496326 is a reply to message #496223]

Sat, 26 February 2011 09:47

oraQ
Messages: 57
Registered: January 2011

Member

Thanks for the inputs. But can you guide me how to get the list of roles and privileges of each role in the database?

Report message to a moderator

Re: To Find A List Of Roles and Privileges In Database [message #496327 is a reply to message #496326]

Sat, 26 February 2011 09:49

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Thanks for the inputs. But can you guide me how to get the list of roles and privileges of each role in the database?
query VIEWs posted in this thread.

Do you know how to write SELECT statement?

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Re: To Find A List Of Roles and Privileges In Database [message #496329 is a reply to message #496327]

Sat, 26 February 2011 10:09

oraQ
Messages: 57
Registered: January 2011

Member

Quote:

Do you know how to write SELECT statement?

What does this mean? I wanted to know the granted roles and privileges of each role in one query. Thanks to everyone.

[Updated on: Sat, 26 February 2011 10:11]

Report message to a moderator

Re: To Find A List Of Roles and Privileges In Database [message #496330 is a reply to message #496329]

Sat, 26 February 2011 10:13

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

how will you, me or anyone know when correct answer has been posted?

>I wanted to know the granted roles and privileges of each role in one query.
granted to who?

Report message to a moderator

Re: To Find A List Of Roles and Privileges In Database [message #496331 is a reply to message #496329]

Sat, 26 February 2011 10:14

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

What does this mean?

Just what it is written.
Do you know how to write a SELECT statement?
You have the views to query, just SELECT on them. What is your problem in this task?

Regards
Michel

Report message to a moderator

Re: To Find A List Of Roles and Privileges In Database [message #514469 is a reply to message #496331]

Tue, 05 July 2011 00:49

x-oracle
Messages: 380
Registered: April 2011
Location: gujarat

Senior Member

select privilege from dba_sys_privs where grantee='YOUR USER' order by 1;
select granted_role, default_role from dba_role_privs where grantee='YOUR USER' order by 1;

this query also helpful to you to show particular role assing to particular user

Report message to a moderator

Previous Topic:	SQLs to monitor user access
Next Topic:	Create user with non expiring password

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jan 04 21:40:56 CST 2025