Thanks Karl ________________________________ From: Karl Arao <karlarao@xxxxxxxxx> To: Mark.Bobak@xxxxxxxxxxxx Cc: "smishra_97@xxxxxxxxx" <smishra_97@xxxxxxxxx>; "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx> 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@KNOX10g> COL privilege format a20 scott@KNOX10g> COL object format a20 scott@KNOX10g> COL grantee format a20 scott@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@xxxxxxxxxxxx> 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@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