?
Solved

No of roles and privs assigned to each role

Posted on 2011-09-20
3
Medium Priority
?
379 Views
Last Modified: 2012-05-12
Hi,
I need a query to find a no of roles in the database and all privs assigned to each role.
Please help me.
0
Comment
Question by:nirvairghuman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 252 total points
ID: 36567619
select count(*) from dba_roles;  -- count of all roles


select r.role,count(p.privilege) from dba_roles r, dba_sys_privs p    -- count of privileges for each role
where r.role = p.grantee
group by r.role
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 248 total points
ID: 36567626
I might have missed a view or two but here are the core ones:

select role from dba_roles;

select role, granted_role from role_role_privs;
select role, privilege from role_sys_privs;
select role, table_name, column_name, privilege from role_tab_privs;
0
 

Author Comment

by:nirvairghuman
ID: 36568313
Thanks a lot Guys,
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question