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@xxxxxxxxx> To: smishra_97@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx 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@xxxxxxxxx> To: oracle-l@xxxxxxxxxxxxx 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