Solved

List All Active Users and Associated Roles In Oracle 10gR2

Posted on 2011-03-14
2
666 Views
Last Modified: 2012-08-13
I need to create a query that returns all active Oracle users (Unlocked) and their associated roles.
0
Comment
Question by:AUSA_IT5
2 Comments
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 35129080
Users and status can be found in dba_users.

Assigned roles in dba_role_privs.

You also realize there can be individual privs assigned?  Not just roles.
0
 
LVL 4

Expert Comment

by:MarioAlcaide
ID: 35129243
SELECT DBA_ROLE_PRIVS.GRANTEE,DBA_ROLE_PRIVS.GRANTED_ROLE
FROM DBA_ROLE_PRIVS,DBA_USERS
WHERE DBA_ROLE_PRIVS.GRANTEE = DBA_USERS.USERNAME
AND DBA_USERS.ACCOUNT_STATUS = 'OPEN'
ORDER BY GRANTEE;
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

856 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