RE: [Q] wield create view error, need help?

see answer below

> -----Original Message-----
> From: dist cash [mailto:mccdba_at_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.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
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).