Home » RDBMS Server » Security » To Find A List Of Roles and Privileges In Database (Windows XP, Oracle 11g)
To Find A List Of Roles and Privileges In Database [message #496118] Thu, 24 February 2011 11:02 Go to next message
oraQ
Messages: 51
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 Go to previous messageGo to next message
BlackSwan
Messages: 21941
Registered: January 2009
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
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 Go to previous messageGo to next message
Michel Cadot
Messages: 57608
Registered: March 2007
Location: Nanterre, France, http://...
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
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 Go to previous messageGo to next message
oraQ
Messages: 51
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.
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 Go to previous messageGo to next message
Michel Cadot
Messages: 57608
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It depends on the error.

Regards
Michel
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 Go to previous messageGo to next message
oraQ
Messages: 51
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?
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 Go to previous messageGo to next message
Michel Cadot
Messages: 57608
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
oraQ
Messages: 51
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?
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 Go to previous messageGo to next message
BlackSwan
Messages: 21941
Registered: January 2009
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/
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 Go to previous messageGo to next message
oraQ
Messages: 51
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 Go to previous messageGo to next message
BlackSwan
Messages: 21941
Registered: January 2009
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?
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 Go to previous messageGo to next message
Michel Cadot
Messages: 57608
Registered: March 2007
Location: Nanterre, France, http://...
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
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 Go to previous message
x-oracle
Messages: 318
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
Previous Topic: SQLs to monitor user access
Next Topic: Create user with non expiring password
Goto Forum:
  


Current Time: Fri Apr 18 01:48:59 CDT 2014

Total time taken to generate the page: 0.10563 seconds