>-----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
