Re: User List for Certain Privs

The issue with my query is that it will work if I had CREATE SESSION direct or 
CREATE SESSION is given to ROLE 1 and then Role 1 is assigned to User

But if ROLE 1 is granted to another ROLE2 and then ROLE2 is assigned to user, then it will not show up here. So looking some CONNECT by Clause kind of query 

Thanks
Sanjay



________________________________
From: Sanjay Mishra <smishra_97_at_yahoo.com>
To: smishra_97_at_yahoo.com; oracle-l_at_freelists.org
Sent: Monday, May 18, 2009 12:40:07 PM
Subject: Re: User List for Certain Privs


I am trying the following and if somebody can suggest more effeciently

  1  select grantee, 'DIRECT' from dba_sys_privs where grantee not in (select role from role_sys_privs where privilege='CREATE SESSION')
  2  union
  3* select grantee ,granted_role from dba_role_privs where granted_role in (select role from role_sys_privs where privilege='CREATE SESSION')




________________________________
From: Sanjay Mishra <smishra_97_at_yahoo.com>
To: oracle-l_at_freelists.org
Sent: Monday, May 18, 2009 12:31:02 PM
Subject: User List for Certain Privs


Hi

Can somebody shared the SQL who can give the list of user based on PRIVILEGE passed to the script. e. I want to create a list of user who has CREATE SESSION privlege. It should only show me the username or if possible to include Direct/Role 

I need to create a report that can list the output as 
Username        Direct/Role
sanjay                Direct
santosh              Role

Thanks
Sanjay


      

--
http://www.freelists.org/webpage/oracle-l