Re: User List for Certain Privs
Date: Wed, 20 May 2009 11:38:47 +0800
Message-ID: <12ee65600905192038s3de3a503j33825e6aee3cf24d_at_mail.gmail.com>
Anytime Sanjay. If you want more tools, Pete Finnigan has a lot on his website http://www.petefinnigan.com/tools.htm
On Wed, May 20, 2009 at 2:37 AM, Sanjay Mishra <smishra_97_at_yahoo.com> wrote:
> 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_at_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-lReceived on Tue May 19 2009 - 22:38:47 CDT