RE: Select won't work for view create

[Dasko, Dan]

Title: RE: Select won't work for view create

That was the problem.  Thanks.

 

Dan

-----Original Message-----
From: Jacques Kilchoer [mailto:[EMAIL PROTECTED]]
Sent: Thursday, April 05, 2001 2:31 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Select won't work for view create

>-----Original Message-----
>From: Dasko, Dan [mailto:[EMAIL PROTECTED]]
>
>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 ...
>
>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.
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.
SQL> create table t2 (n number, v varchar2 (30)) ;
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@jrktest
Connected.
SQL> create view v1 as select n, d from x.t1 ;
View created.
SQL> create view v2 as
  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
               *
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
Irvine, California 92618
U.S.A.
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.