SQL> CREATE OR REPLACE VIEW WWV_DBA_SGA ( COMPONENT_GROUP,
2 MEMORY_SIZE_IN_BYTES ) AS select name component_group, bytes
memory_size_in_bytes from v$sgastat
3 .
SQL> /
MEMORY_SIZE_IN_BYTES ) AS select name component_group, bytes
memory_size_in_bytes from v$sgastat
*
ERROR at line 2:
ORA-00942: table or view does not exist
I tried to grant privilege and got error:
SQL> grant select on sys.v$sgastat to portal30;
grant select on sys.v$sgastat to portal30
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views
Do you have ideal how to fix this problem?
Thanks.
From: Jacques Kilchoer <[EMAIL PROTECTED]>
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Subject: RE: [Q] wield create view error, need help?
Date: Wed, 23 Oct 2002 17:08:28 -0800
see answer below
> -----Original Message-----
> From: dist cash [mailto:mccdba@;hotmail.com]
>
> I have ORACLE 8.1.7 on SUn Server. I have wield problem on
> create view. If
> I only use "select ..", I don't have error. But if I use
> "createor replace
> view ..select ...", I got error and I have DBA right. The
> statement are:
>
>
> SQL> CREATE OR REPLACE VIEW DBE_SYNONYMS ( OWNER,
> 2 SYNONYM_NAME, TABLE_OWNER, TABLE_NAME, DB_LINK,
> 3 CREATED, STATUS ) AS select u.name, o.name, s.owner,
> s.name, s.node,
> o.ctime,
> 4 from sys.user$ u, sys.syn$ s, sys.obj$ o
> 5 where o.obj# = s.obj#
> 6 and o.type#=5
> 7 and o.owner#=u.user#
> 8 .
> SQL> /
> from sys.user$ u, sys.syn$ s, sys.obj$ o
> *
> ERROR at line 4:
> ORA-00942: table or view does not exist
>
> ....
To create the view, the owner of the view will need SELECT access granted
DIRECTLY, not via a role. If the owner of the view wants to grant SELECT on
the view to other users, then the owner of the view will need SELECT ...
with grant option
i.e.
grant select on sys.obj$ to x ;
connect x/password
create view my_obj as select * from sys.obj$ ; <--- OK
grant select on my_obj to y ; <--- will fail: x does not have right access
grant select on sys.obj$ to x with grant option
connect x/password
create view my_obj as select * from sys.obj$ ; <--- OK
grant select on my_obj to y ; <--- OK
If instance parameter O7_DICTIONARY_ACCESSIBILITY = FALSE then only a SYSDBA
user will be able to grant the privileges to user X.
Finally, if you were using Oracle 9.x, you could grant user X "select any
dictionary" system privilege (directly, not via a role) instead of granting
select access on each SYS table you want to use.
_________________________________________________________________
Surf the Web without missing calls!�Get MSN Broadband. http://resourcecenter.msn.com/access/plans/freeactivation.asp
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: dist cash
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
