RE: User List for Certain Privs
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxx>
- To: "smishra_97@xxxxxxxxx" <smishra_97@xxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 18 May 2009 14:41:08 -0400
Sanjay,
I just saw a similar discussion on MetaLink Forum (Community, whatever) just
now.
Someone posted this that you may find useful:
undef username
col "User/Role" for a40
col "Priv/Role" for a30
select lpad(' ',level*2,' ')||c "User/Role" ,p "Priv/Role",a "With Admin" from (
select granted_role p, grantee c, admin_option a from dba_role_privs
union
select 'PUBLIC' p, upper('&&username') c, 'NO' a from dual
union
select privilege p, grantee c, admin_option a from dba_sys_privs
order by c
) x
connect by c = prior p
start with c = upper('&&username');
Hope that helps,
-Mark
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Sanjay Mishra
Sent: Monday, May 18, 2009 12:40 PM
To: smishra_97@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
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
Other related posts:
