Re: v6 to v7 data dict view question

In article <3a8urv$6ep_at_News1.mcs.com>, dmausner_at_brauntech.com (Dave Mausner) writes:
> In article <3a8cqd$d95_at_info-server.bbn.com>, jkelly_at_bbn.com (Jim Kelly) says:

>>In the v6 version I could do "select username, connect_priv from dba_users"
>>to get a list of what users currently have connection privilege.  In v7
>>connect_priv, resource_priv, and dba_priv are no longer part of the
>>dba_users view.  Anyone know where I can get at this info now?

Assuming you're the DBA wanting to find all the users that have CREATE SESSION privilege, USER_ROLE_PRIVS won't work, because that shows only your own privileges.

Because of the recursive nature of roles, it is a pretty complicated process to identify all of the users who have a privilege.

The way that I understand it, to identify all of the people that have CREATE SESSION privilege, you would have to:

1.Query DBA_SYS_PRIVS to identify the users and roles that have CREATE SESSION.
2. Find all the roles that contain the roles in step 1.
3. Find all the roles that contain the roles in step 2.
4. etc.. until you have no more roles
5. Find all the users that have any of the roles from the above steps as one

   of their default roles
6. UNION the users in 1 and 5.
7. Get rid of duplicates

Am I understanding this right? Anyone know how to do this easily? For

-- 
Bob Swisshelm                | swisshelm_at_Lilly.com     | 317 276 5472
Eli Lilly and Company        | Lilly Corporate Center  | Indianapolis, IN 46285