Re: select_catalog_role granted to newly created user named 'T' with create session privilege does not select data from dba_users
Date: Fri, 4 Dec 2009 19:16:31 -0500
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:
>> I'm on 184.108.40.206 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.