Re: User List for Certain Privs

From: Karl Arao <karlarao_at_gmail.com>
Date: Tue, 19 May 2009 10:09:31 +0800
Message-ID: <12ee65600905181909v74f2bf39o3f8dde978d745425_at_mail.gmail.com>

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

Received on Mon May 18 2009 - 21:09:31 CDT