RE: granting SELECT privilege on SYS.X$ TABLES

Someone must have created sys.x_$ views on some of the sys.x$ tables. Installing statspack does that for example for X$KCBFWAIT, X$KSPPSV, X$KSPPI, and X$KSQST. Do you have quest? I believe it does it for some of the x$ tables as well. I routinely do it for all x$ tables in my test databases and grant select to the select_catalog_role. Then I can access the x$tables without having to log on as sys.

In my test databases I always
At 07:14 PM 11/14/2003, you wrote:
>P.S. I forgot to mention that in all the databases (including the 8.1.7
>databases) in which I tried this, init parameter
>O7_DICTIONARY_ACCESSIBILITY was set to FALSE.
>
>I always thought that one could not grant SELECT privilege on the SYS.X$
>tables, and to make them accessible to another user one would have to
>create a view on the table (as mentioned on Steve Adams' ixora website:
>http://www.ixora.com.au/scripts/prereq.htm
>create_xviews.sql)
>
>However someone told me recently that you could grant SELECT on sys.X_$...
>
>When I tried this, I saw results that confused me.
>In Database A, Oracle 8.1.7.4.1, Windows 2000 server, I was able to
>1- grant select on SYS.X_$KTFBFE to another_user ;
>2- grant select on SYS.X_$KTFBHC to another_user ;
>3- grant select on SYS.X_$KTFBUE to another_user ;
>
>BUT
>
>4- grant select on SYS.X_$KDXST to another_user ;
>returns ORA-00942 table or view does not exist.
>
>In database B, using the same ORACLE_HOME as database A (i.e. identical
>Oracle version and OS)
>even the first three grant statements returned ORA-00942
>
>When I tried it on more recent Oracle databases on Windows / SunOS
>servers, it worked intermittently:
>Oracle 9.0 (SunOS): all GRANTS failed
>Oracle 9.2 (SunOS): GRANTS 1-3 were successful, GRANT 4 failed
>Oracle 10.1 beta (Windows 2000): all GRANTS failed
>
>Does anyone know the reason for this strange behaviour?
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>--
>Author: Jacques Kilchoer
> INET: Jacques.Kilchoer_at_quest.com
>
>Fat City Network Services -- 858-538-5051 http://www.fatcity.com
>San Diego, California -- Mailing list and web hosting services
>---------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).

Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Wolfgang Breitling
  INET: breitliw_at_centrexcc.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).