Skip to Main Content

Breadcrumb

Connor and Chris will both be at AI World from October 12 to October 17 , the premier Oracle conference of 2025. If you're in Vegas, please come say Hi or pop into our sessions

Question and Answer

Tom Kyte

Thanks for the question, Saibabu.

Asked: June 29, 2001 - 1:03 pm UTC

Last updated: January 16, 2004 - 5:18 pm UTC

Version: 8.1.6,9.0.0

Viewed 1000+ times

You Asked

Hi Tom,I have few questions unanswered since long time.
Database version : 8.1.5,8.1.6 and 8.1.7

1) I have granted a role "DBA" to user "ADM".Then I can select count(*) from sys.obj$ and describe "SYS.OBJ$".But nowhere I can see that select on OBJ$ has been granted to adm user.I have tried around with user_tab_privs,user_tab_privs_recd,all_tab_privs_recd.This privilege has not been granted to select_catalog_role also.

2) It looks so funny to me that "V$SESSION" is a public synonym for "V_$SESSION",and "V_$SESSION" is a view defined on "V$SESSION".Same thing with "GV$SESSION" and "GV_$SESSION".Why is this cyclic behaviour being maintained.I know we can get full definition from v$fixed_view_definition.

Database version : 9.0.0

3) default settting of O7_DICTIONARY_ACCESSIBILITY=FALSE in 9i.Similar situation is maintained as in questions no. 1.Now I can select count(*) from sys.obj$.But desc "SYS.OBJ$" is not working.Please explain.

4) Instead of grating DBA role to adm user.I have granted roles select_catalog_role,delete_catalog_role,execute_catalog_role,exp_full_database,imp_full_database to adm user with admin option.Now adm user cannot able to select from "SYS.OBJ$".How to explain this behaviour when Iam grating the same roles DBA having.

Please take your own time to explain these issues.Your help is very much appreciated.Have a nice weekend.

Thanks
Sai.

and Tom said...

1) the DBA role has the very powerful "SELECT ANY TABLE" priv. ADM can select from ANY table in the database -- there is no direct grant on OBJ$ to ADM.

2) The v$ tables are magic -- they are in memory tables, they are special. It is just the way they are.

3) in 9i they changed how this works. There is a new privelege

SELECT ANY DICTIONARY Query any data dictionary object in the SYS schema.

This privilege lets you selectively override the default FALSE setting of the O7_DICTIONARY_ACCESSIBILITY initialization parameter. Note: This privilege must be granted individually. It is not included in GRANT ALL PRIVILEGES, nor can it be granted through a role.

And DBA has this priv. It would appear to be a bug that DESC cannot describe it.


It would also appear to be a bug that the Oracle9i reference still refers to the ROLES, and not this new privelege.

4) See #3, the roles don't do it anymore.

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Excellent feedback,ofcourse you skipped internals of v$ views.

Saibabu Devabhaktuni, June 29, 2001 - 3:45 pm UTC

Thank you so much.To me "SELECT ANY DICTIONARY" is working through roles in 9i.I have grated it through roles and users can actuabble able to use it.

Privileg Grant Through Role

Abhi, April 12, 2003 - 10:53 am UTC

Hello Sir .
Sir we can grant privlege (object/system) to a user through
role or Directly .. what is difference in these two ways.
and whats the Reason behind reason this.
when we should grant privilege directly or when Throught Role. I think Jobs only can C directly Grnted Privs.
Is this Restriction is applicable for Both (System/Object) privileges.


Tom Kyte
April 13, 2003 - 8:08 pm UTC

see
</code> http://asktom.oracle.com/Misc/RolesAndProcedures.html <code>

for the difference.

roles are for end users.

direct grants are generally needed for developers.

"SELECT ANY DICTIONARY" is working through roles in 9i

Raj, January 15, 2004 - 6:20 pm UTC

"SELECT ANY DICTIONARY" not working throught role for me.

Oracle version 9iR2.

I tried after connect as system
1. grant select any dictionary to role1 ;
2. grant role1 to user1 ;
3. after connecting from sqlplus as user1 tried
4. select * from v$session gives
ora-00942 table or view does not exists.

But if I grant
1. grant select any dictionary to user1 ;
2. after connecting from sqlplus as user1 tried
3. select * from v$session (WORKS)

So it looks like as Tom said this needs to be granted individually to user and not through role.


Tom Kyte
January 16, 2004 - 1:09 am UTC 

are you sure -- works for me

ops$tkyte@ORA9IR2> grant select any dictionary to role1;
 
Grant succeeded.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> drop user a cascade;
 
User dropped.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create user a identified by a;
 
User created.
 
ops$tkyte@ORA9IR2> grant create session to a;
 
Grant succeeded.
 
ops$tkyte@ORA9IR2> grant role1 to a;
 
Grant succeeded.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> @connect a/a
ops$tkyte@ORA9IR2> set termout off
a@ORA9IR2> REM GET afiedt.buf NOLIST
a@ORA9IR2> set termout on
a@ORA9IR2> select count(*) from v$session;
 
  COUNT(*)
----------
         8

Sorry It Works

Raj, January 16, 2004 - 5:18 pm UTC

User I created yesterday to test this still does not work but after your reply. I created three new users and all of them are working.

Thanks for the prompt reply. Right now I am working of one of the assignment where I have to use this and I would have taken different path if I wouldn't have got your answer.



more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.