RE: Select won't work for view create

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Thu, 05 Apr 2001 10:26:40 -0700
Message-ID: <F001.002E2F0E.20010405103057@fatcity.com>

>-----Original Message-----
>From: Dasko, Dan [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 ...
>
>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_at_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 Received on Thu Apr 05 2001 - 12:26:40 CDT