Re: With statement alias problem/10g

From: Michael O'Shea <michael.oshea_at_tessella.com>
Date: 12 Aug 2005 08:59:37 -0700
Message-ID: <1123862377.084301.69030@g43g2000cwa.googlegroups.com>

Thanks for your comments again David. There are no ROLEs involved.

The script below creates two users, and queries a table "test" in user1. One query works, the other does not. The only difference is the alias syntax. This is not a priv or role issue.

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> CONN SYSTEM/SYSTEM
Connected.
SQL>
SQL> CREATE USER USER1 IDENTIFIED BY USER1 DEFAULT TABLESPACE DRIAD QUOTA UNLIMITED ON DRIAD; User created.

SQL> GRANT CREATE TABLE TO USER1; Grant succeeded.

SQL> GRANT CREATE VIEW TO USER1; Grant succeeded.

SQL> GRANT CREATE SESSION TO USER1; Grant succeeded.

SQL> SQL> CREATE USER USER2 IDENTIFIED BY USER2 DEFAULT TABLESPACE DRIAD QUOTA UNLIMITED ON DRIAD; User created.

SQL> GRANT CREATE SESSION TO USER2; Grant succeeded.

SQL>
SQL>
SQL> CONN USER1/USER1

Connected.
SQL>
SQL> CREATE TABLE test(a NUMBER);

Table created.

SQL>
SQL> CREATE OR REPLACE VIEW testView3 AS   2 WITH
  3 testAlias AS

  4     (
  5        SELECT COUNT(*) X
  6          FROM test
  7      )
  8      SELECT testAlias.X
  9       FROM testAlias;

View created.

SQL>
SQL> CREATE OR REPLACE VIEW testView4 AS

  2      SELECT testAlias.X
  3       FROM (
  4             SELECT COUNT(*) X
  5               FROM test
  6            ) testAlias;

View created.

SQL>
SQL>
SQL> GRANT SELECT ON testView3 TO USER2;

Grant succeeded.

SQL> GRANT SELECT ON testView4 TO USER2;

Grant succeeded.

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

---

         0

SQL>
SQL>
SQL> SELECT *

  2 FROM V$VERSION; BANNER

---

Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Bit 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> spool off

fitzjarrell_at_cox.net wrote:
> 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:59:37 CDT