Re: With statement alias problem/10g

Michael O'Shea wrote:
> David, IANAL_VISTA thank you for your prompt comments on GRANTs. You
> are correct that USER2 does not have privs on Account.org and USER1
> does.
>
> I am not totally convinced on the priv argument however. The following
> are snippets quoted out of context from message 1 in this thread.
>
>
> When the following is executed as USER1
>
> CREATE OR REPLACE VIEW testView1 AS
> SELECT COUNT(*) X
> FROM account.orgs;
>
> GRANT SELECT ON testView1 TO user2;
>
>
> I "can" perform a SELECT statement on the view testView1 FROM USER2.
>
>
>
> When the following is executed as USER1
>
> CREATE OR REPLACE VIEW testView1 AS
> WITH
> testAlias AS
> (
> SELECT COUNT(*) X
> FROM account.orgs
> )
> SELECT *
> FROM testAlias;
>
> GRANT SELECT ON testView1 TO user2;
>
> I "cannot" perform a SELECT statement on the view testView1 from USER2
>
>
> If the argument was solely a priv argument (whether USER2 has
> privileges on Account.org), surely both SELECTs would either fail or
> succeed. This is not what is observed.
>
> Thanks again
> 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

Your last statement is not true, as USER2 MAY have grants via a ROLE but not directly. Role privileges do not cascade through to stored procedures, views, etc.; only direct grants to the user are reecognised.

The solution to the problem is still as I posted.

David Fitzjarrell Received on Fri Aug 12 2005 - 10:20:17 CDT