Re: User List for Certain Privs

Thanks Karl




________________________________
From: Karl Arao <karlarao_at_gmail.com>
To: Mark.Bobak_at_proquest.com
Cc: "smishra_97_at_yahoo.com" <smishra_97_at_yahoo.com>; "oracle-l_at_freelists.org" <oracle-l@freelists.org>
Sent: Monday, May 18, 2009 10:09:31 PM
Subject: Re: User List for Certain Privs

Hi Sanjay, 

You could also take a look at Effective Oracle Database 10g Security by Design, very nice security book!

http://www.mhprofessional.com/product.php?cat=7&isbn=0072231300

scripts here http://www.mhprofessional.com/downloads/products/0072231300/0072231300_code_old.zip




Below will show you direct/indirect roles granted to a user:


CREATE ROLE a; 
CREATE ROLE b; 
CREATE ROLE c; 
CREATE ROLE d; 
CREATE ROLE e; 
GRANT a TO b; 
GRANT b TO c; 
GRANT c TO d; 
GRANT e TO c; 
GRANT c TO d; 
GRANT e TO c; 
-- Granting D to SCOTT will give SCOTT all roles 
GRANT d TO scott; 

-- Create a table and grant privileges to role A 
CREATE TABLE obj_of_interest AS SELECT * FROM DUAL; 
GRANT SELECT ON obj_of_interest TO a; 

EXEC show_roles.display('scott') 
Direct Role:�� CONNECT�� 
Direct Role:�� D 
Indirect Role: ..C via D 
Indirect Role: ....B via C 
Indirect Role: ......A via B 
Indirect Role: ....E via C�� 
Direct Role:�� RESOURCE���� 
PL/SQL procedure successfully completed.

scott_at_KNOX10g> COL privilege format a20�� 
scott_at_KNOX10g> COL object format a20�� 
scott_at_KNOX10g> COL grantee format a20�� 
scott_at_KNOX10g> SELECT privilege, object, grantee�� 
� 2��� FROM user_object_privs���� 
� 3�� WHERE owner = 'SEC_MGR' AND OBJECT = 'OBJ_OF_INTEREST'; 
�
PRIVILEGE����������� OBJECT�������������� GRANTEE�� 
-------------------- -------------------- --------------------�� 
SELECT�������������� OBJ_OF_INTEREST����� A 





CREATE OR REPLACE PACKAGE show_roles
AS
� PROCEDURE display (p_username IN VARCHAR2);
END;
/

CREATE OR REPLACE PACKAGE BODY show_roles
AS
--------------------------------------------------------
� FUNCTION convert_level (p_level IN NUMBER)
��� RETURN VARCHAR2
� AS
��� l_str� VARCHAR2 (32767);
� BEGIN
��� FOR i IN 1 .. p_level
��� LOOP
����� l_str := l_str || '..';
��� END LOOP;

��� RETURN l_str;
� END;

--------------------------------------------------------
� PROCEDURE recursive_role_getter (
��� p_role�� IN� VARCHAR2,
��� p_level� IN� NUMBER)
� AS
� BEGIN
��� FOR irec IN (SELECT�� granted_role
�������������������� FROM dba_role_privs
������������������� WHERE grantee = UPPER (p_role)
���������������� ORDER BY 1)
��� LOOP
����� DBMS_OUTPUT.put_line (�� 'Indirect Role: '
��������������������������� || convert_level (p_level)
��������������������������� || irec.granted_role
��������������������������� || ' via '
��������������������������� || p_role);
����� recursive_role_getter (irec.granted_role,
���������������������������� p_level + 1);� -- recurse
��� END LOOP;
� EXCEPTION
��� WHEN OTHERS
��� THEN
����� NULL;
� END;

--------------------------------------------------------
� PROCEDURE display (p_username IN VARCHAR2)
� AS
� BEGIN
��� FOR rec IN (SELECT�� granted_role
������������������� FROM dba_role_privs
������������������ WHERE grantee = UPPER (p_username)
��������������� ORDER BY 1)
��� LOOP
����� DBMS_OUTPUT.put_line (�� 'Direct Role:�� '
��������������������������� || rec.granted_role);
����� recursive_role_getter (rec.granted_role, 1);
��� END LOOP;
� END;
--------------------------------------------------------
END;
/






- Karl Arao
http://karlarao.wordpress.com









On Tue, May 19, 2009 at 2:41 AM, Bobak, Mark <Mark.Bobak_at_proquest.com> wrote:

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Sanjay Mishra
Sent: Monday, May 18, 2009 12:40 PM  

To: smishra_97_at_yahoo.com; oracle-l_at_freelists.org
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