Re: With statement alias problem/10g

Michael O'Shea wrote:
> Further to previous postings, I have further confirmation that the
> issue initially documented is not priv related. The following query,
> performed two slightly different ways, cleanly shows one working
> whereas the other does not.
>
> 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>
> SQL> CONN USER1/USER1
> Connected.
> SQL>
> SQL>
> SQL> CREATE OR REPLACE VIEW testView3 AS
> 2 WITH
> 3 testAlias AS
> 4 (
> 5 SELECT COUNT(*) X
> 6 FROM account.orgs
> 7 )
> 8 SELECT testAlias.X
> 9 FROM testAlias;
>
> View created.
>
> SQL>
> SQL>
> SQL> CREATE OR REPLACE VIEW testView4 AS
> 2 SELECT testAlias.X
> 3 FROM (
> 4 SELECT COUNT(*) X
> 5 FROM account.orgs
> 6 ) testAlias;
>
> View created.
>
> SQL>
> SQL> GRANT SELECT ON testView3 TO USER2;
>
> Grant succeeded.
>
> SQL> GRANT SELECT ON testView4 TO USER2;
>
> Grant succeeded.
>
> SQL>
> SQL>
> SQL>
> SQL>
> SQL> CONN USER2/USER2
> Connected.
> SQL>
> SQL> SELECT *
> 2 FROM USER1.testView3;
> FROM USER1.testView3
> *
> ERROR at line 2:
> ORA-00942: table or view does not exist
>
>
> SQL>
> SQL> SELECT *
> 2 FROM USER1.testView4;
>
> X
> ----------
> 5745
>
> SQL> SELECT * 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

I shall state this again: the problem is PRIVILEGE related, as privileges granted to ROLES are NOT recognized through views, stored procedures,, functions, etc.

Grant select on ACcount.org DIRECTLY to USER2 and the problem goes away.

David Fitzjarrell Received on Fri Aug 12 2005 - 10:29:55 CDT