Re: With statement alias problem/10g

Michael O'Shea wrote:
> All, some Friday afternoon frustration. I have created a script to
> demonstrate a problem I am currently having and have included it inline
> below. Despite the clear error message "ORA-00942: table or view does
> not exist ", I don't understand why it is occurring. Account.orgs is a
> table. I cannot find anything on metalink, or perhaps I'm not
> searching on the correct term. Full SQLPlus trace included below.
> Regards
> Mike
>
>
> TESSELLA Michael.OShea_at_tessella.com
> __/__/__/ Tessella Support Services plc
> __/__/__/ 3 Vineyard Chambers, ABINGDON, OX14 3PX, England
> __/__/__/ Tel: (44)(0)1235-555511 Fax: (44)(0)1235-553301
> www.tessella.com Registered in England No. 1466429
>
>
>
> SQL>
> SQL>
> SQL> CONN user1/user1
> Connected.
> SQL>
> SQL>
> SQL> CREATE OR REPLACE VIEW testView1 AS
> 2 WITH
> 3 testAlias AS
> 4 (
> 5 SELECT COUNT(*) X
> 6 FROM account.orgs
> 7 )
> 8 SELECT *
> 9 FROM testAlias;
>
> View created.
>
> SQL>
> SQL>
> SQL> CREATE OR REPLACE VIEW testView2 AS
> 2 WITH
> 3 testAlias AS
> 4 (
> 5 SELECT 1 X
> 6 FROM DUAL
> 7 )
> 8 SELECT *
> 9 FROM testAlias;
>
> View created.
>
> SQL>
> SQL>
> SQL> GRANT SELECT ON testView1 TO user2;
>
> Grant succeeded.
>
> SQL> GRANT SELECT ON testView2 TO user2;
>
> Grant succeeded.
>
> SQL>
> SQL>
> SQL>
> SQL>
> SQL>
> SQL>
> SQL>
> SQL> CONN user2/user2
> Connected.
> SQL>
> SQL> DESCRIBE user1.testView1
> Name Null? Type
> ----------------------------------------- --------
> ----------------------------
> X NUMBER
>
> SQL> DESCRIBE user1.testView2
> Name Null? Type
> ----------------------------------------- --------
> ----------------------------
> X NUMBER
>
> SQL>
> SQL> SELECT *
> 2 FROM user1.testView1;
> FROM user1.testView1
> *
> ERROR at line 2:
> ORA-00942: table or view does not exist
>
>
> SQL>
> SQL> SELECT *
> 2 FROM user1.testView2;
>
> X
>
> ----------
>
> 1
>
>
> SQL>
> SQL>
> SQL>
> SQL>
> SQL>
> SQL>
> SQL>
> SQL> CONN user1/user1
> Connected.
> SQL>
> SQL> CREATE OR REPLACE VIEW testView1 AS
> 2 SELECT COUNT(*) X
> 3 FROM account.orgs;
>
> View created.
>
> SQL>
> SQL> GRANT SELECT ON testView1 TO user2;
>
> Grant succeeded.
>
> SQL>
> SQL>
> SQL>
> SQL>
> SQL>
> SQL>
> SQL> CONN user2/user2
> Connected.
> SQL>
> SQL> DESCRIBE user1.testView1
> Name Null? Type
> ----------------------------------------- --------
> ----------------------------
> X NUMBER
>
> SQL>
> SQL> SELECT *
> 2 FROM user1.testView1;
>
> X
>
> ----------
>
> 5745
>
>
> SQL>
> SQL>
> SQL>
> SQL>
> SQL> SELECT *
> 2 FROM V$VERSION;
>
> BANNER
>
> ----------------------------------------------------------------
>
> Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 -64bi
>
> PL/SQL Release 10.1.0.3.0 - Production
>
> CORE 10.1.0.3.0 Production
>
> TNS for Solaris: Version 10.1.0.3.0 - Production
>
> NLSRTL Version 10.1.0.3.0 - Production
>
>
> SQL>
> SQL>
> SQL> spool off

There is no problem with statement aliasing in 10g, as you prove with your second view. The issue is one, not of object existence, but of object privilege. USER2 clearly has no privileges on Account.org, whereas USER1 does. As such the views are created by USER1 without error; when select is granted on the views to USER2 no similar grant was given to USER2 on Account.org. DUAL is open to public select access, hence your second view returns results as expected. USER2 can't 'see' the Account.org table, thus it doesn't exist in that context.

Grant select on account.org to USER2 and your error will vanish.

David Fitzjarrell Received on Fri Aug 12 2005 - 08:37:54 CDT