Re: select_catalog_role granted to newly created user named 'T' with create session privilege does not select data from dba_users

From: sundar mahadevan <sundarmahadevan82_at_gmail.com>
Date: Fri, 4 Dec 2009 19:16:31 -0500
Message-ID: <7d81ec570912041616g55e2b0b0i2ad5cb72eea4e9f_at_mail.gmail.com>

Many Thanks Kurt. The Issue is now resolved. It seems that I will have to logoff and log back in for select_catalog_role to work. As far as my understanding goes, with select_catalog_role granted, the user will be able to view all the sys schema views (for example dba_users,v$session). On the other hand, with select any dictionary, the user will be able to select data from sys's tables (for example sys.icol$, sys.obj$). Am I correct in understanding this? My next question: Let us consider, I have A,B and C to be 3 roles and D as a system privilge. I grant privilege 'D' to role C and then role C to B and then B to A. This has always been my assumption that Roles finally contain system privileges. In this case HS_ADMIN_SELECT_ROLE is granted to SELECT_CATALOG_ROLE and there are no system privileges under HS_ADMIN_SELECT_ROLE. So what does Oracle do(check) in order to allow access to sys.obj$ for a user 'T' who has SELECT_CATALOG_ROLE granted. Is a namesake Role suffice without any system privileges for Oracle to give access for user 'T' to access sys.obj$? Thanks in advance.

SQL> SELECT * FROM role_role_privs WHERE role='SELECT_CATALOG_ROLE';

ROLE                           GRANTED_ROLE                   ADM
------------------------------ ------------------------------ ---
SELECT_CATALOG_ROLE            HS_ADMIN_SELECT_ROLE           NO

SQL> SELECT * FROM role_role_privs WHERE role='HS_ADMIN_SELECT_ROLE';

no rows selected

SQL> SELECT * FROM role_sys_privs WHERE role='HS_ADMIN_SELECT_ROLE';

On Fri, Dec 4, 2009 at 4:24 PM, Kurt Franke <Kurt-Franke_at_web.de> wrote:
>
> Hi,
>
>> I'm on 11.2.0.1 on OEL5.3
>>
>> O7_DICTIONARY_ACCESSIBILITY is set to FALSE. I created a new user
>> named 'T'  and granted 'create session' privileges to 'T'. I also have
>> another user named 'SCOTT' and a table named 'EMP' under this schema.
>> With 'select any table' privilege granted to 'T' as user 'system', I
>> am able to select data from scott.emp as user 'T' but I am unable to
>> view DBA_USERS (which is obvious since O7_DICTIONARY_ACCESSIBILITY is
>> set to FALSE). Then I revoke 'select any table' from 'T' and grant
>> 'select_catalog_role' as user 'system' to 'T'. I expect to view data
>> from dba_users but this does not happen. I get table or view does not
>> exist. What am I doing wrong here? Thanks in advance.
>
> the system privilege SELECT ANY DICTIONARY is your friend.
>
> of course you may create a special role holding it if it is not ok to grant it directly
> to a user.
>
>
> regards
>
> kf
>
>
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Dec 04 2009 - 18:16:31 CST