RE: Select won't work for view create

That
was the problem.  Thanks.
<SPAN
class=247394417-05042001> 
<SPAN
class=247394417-05042001>Dan

  <FONT face=Tahoma
  size=2>-----Original Message-----From: Jacques Kilchoer   [mailto:Jacques.Kilchoer_at_quest.com]Sent: Thursday, April 05, 2001   2:31 PMTo: Multiple recipients of list ORACLE-LSubject:   RE: Select won't work for view create
  >-----Original Message----- <FONT
  size=2>>From: Dasko, Dan [<A
  href="mailto:Dan.Dasko_at_cdicorp.com">mailto:Dan.Dasko_at_cdicorp.com]
> >I have a select that works

  fine.  However, it doesn't > work fine when I   try to create a view of the result set. > There is   nothing particularly fancy about the query, just >a   plethora of outer joins from the main table to the secondary tables.
> I get an error back about one of the tables not
  existing, but like I >said, it works as a select,   just not as a create view as ... > <FONT   size=2>>FWIW Oracle 8.0.6 on DG-UX
  To create a view, access to the table has to be granted   directly, not via a role. Have you checked for that?   If you have access via a role, you would be able to   select, but not create view. See example below.   

  SQL> -- CREATING USER X SQL>
  create user x identified by x default tablespace users temporary   tablespace temp ; User
  created. SQL> grant create session, create table to   x ; Grant succeeded. SQL>
  alter user x quota unlimited on users ; User   altered.
  SQL> -- CREATING USER Y SQL>
  create user y identified by y default tablespace users temporary   tablespace temp ; User
  created. SQL> grant create session, create view to   y ; Grant succeeded. SQL>
  create role fory ; Role created. <FONT   size=2>SQL> grant fory to y; Grant
  succeeded.
  SQL> -- X CREATES TABLES SQL> --
  Y WILL HAVE ACCESS TO T1 DIRECTLY SQL> -- Y WILL   HAVE ACCESS TO T2 VIA ROLE SQL> connect x/x@   Connected. SQL> create table t1 (n
  number, d date) ; Table created. <FONT   size=2>SQL> create table t2 (n number, v varchar2 (30)) ; <FONT   size=2>Table created. SQL> grant select on t1 to y   ; Grant succeeded. SQL>
  grant select on t2 to fory ; Grant succeeded.   

  SQL> -- Y CREATES VIEWS SQL> --
  Y IS ABLE TO CREATE A VIEW ON T1 BUT NOT ON T2 SQL>

• Y CAN SELECT ON T2 SQL> connect y/y_at_jrktest Connected. SQL> create view v1 as select n, d from x.t1 ; View created. SQL> create view v2 as <FONT size=2>  2  select a.n, a.d, b.v   3  from x.t1 a, x.t2 b   4  where a.n = b.n ; from x.t1 a, x.t2 b <FONT size=2>              
  • ERROR at line 3: ORA-00942: table or view does not exist SQL> select * from x.t2 ; no rows selected
    • Jacques R. Kilchoer (949) 754-8816 Quest Software, Inc. 8001 Irvine Center Drive <FONT size=2>Irvine, California 92618 U.S.A. <A href="http://www.quest.com" target=_blank>http://www.quest.com _____________________________________________________________________This e-mail message has been scanned for the presence of all known computer viruses by the MessageLabs Virus Control Center. However, it is still recommended that you use local virus scanning software to monitor for the presence of viruses.