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