Re: Grant permissions on views with WITH clauses

I've never used this WITH clause, but the ORA-942 may not be from the fact that USER2 cannot see the view. In fact, you've stated that the view appears in ALL_VIEWS, so that leads me to believe that USER2 can see this view. The ORA-942 error is because USER2 does not have the select privs required on the underlying tables in the WITH clause. I had to grant SELECT on FOO1 and FOO2 directly to USER2 before I could get USER2 to access the view. This should probably be classified as a bug so contact Oracle Support.

HTH,
Brian

Cheng-Jih Chen wrote:
>
> This is Oracle 9.2.0.3 on Solaris 2.8. I'm having problems
> with grant permissions on views that contain the WITH clause
> to factor out subqueries (for more info on the WITH clause:
> http://four.pairlist.net/pipermail/oracle-article/2003/000002.html).
>
> Basically, the GRANT SELECT succeeds, but the grantee can't SELECT on
> the view. A similar view without the WITH clause works fine, as expected.
>
> Here's a simple case. I have two users, user1 and user2, and I do the following:
>
> CONNECT user1/user1
>
> CREATE TABLE foo1 (col1 VARCHAR(10), col2 VARCHAR(10));
>
> CREATE TABLE foo2 (col1 VARCHAR(10), col2 VARCHAR(10));
>
> CREATE TABLE foo3 (col1 VARCHAR(10), col2 VARCHAR(10));
>
> INSERT INTO foo1 VALUES ('0', ' ');
> INSERT INTO foo1 VALUES ('1', 'a');
> INSERT INTO foo1 VALUES ('2', 'b');
> INSERT INTO foo1 VALUES ('3', 'c');
> INSERT INTO foo1 VALUES ('4', 'd');
> INSERT INTO foo1 VALUES ('5', 'e');
> INSERT INTO foo1 VALUES ('6', 'f');
> INSERT INTO foo1 VALUES ('7', 'g');
> INSERT INTO foo1 VALUES ('8', 'h');
> INSERT INTO foo1 VALUES ('9', 'i');
>
> INSERT INTO foo2 VALUES ('7', ' ');
> INSERT INTO foo2 VALUES ('8', 'a');
> INSERT INTO foo2 VALUES ('9', 'b');
> INSERT INTO foo2 VALUES ('a', 'c');
> INSERT INTO foo2 VALUES ('b', 'd');
> INSERT INTO foo2 VALUES ('c', 'e');
> INSERT INTO foo2 VALUES ('d', 'f');
> INSERT INTO foo2 VALUES ('e', 'g');
> INSERT INTO foo2 VALUES ('f', 'h');
> INSERT INTO foo2 VALUES ('g', 'i');
>
> INSERT INTO foo3 VALUES ('0', ' ');
> INSERT INTO foo3 VALUES ('1', 'a');
> INSERT INTO foo3 VALUES ('2', 'b');
> INSERT INTO foo3 VALUES ('3', 'c');
> INSERT INTO foo3 VALUES ('4', 'd');
> INSERT INTO foo3 VALUES ('5', 'e');
> INSERT INTO foo3 VALUES ('6', 'f');
> INSERT INTO foo3 VALUES ('7', 'g');
> INSERT INTO foo3 VALUES ('8', 'h');
> INSERT INTO foo3 VALUES ('9', 'i');
>
> CREATE VIEW bar1 AS
> WITH two_foo AS
> (SELECT a.col1, b.col2
> FROM foo1 a, foo2 b
> WHERE a.col1 = b.col1)
> SELECT a.col1, a.col2
> FROM foo3 a, two_foo b
> WHERE a.col1 = b.col1;
>
> CREATE VIEW bar2 AS
> SELECT a.col1, a.col2
> FROM foo3 a, (SELECT a.col1, b.col2 FROM foo1 a, foo2 b WHERE a.col1 = b.col1) b
> WHERE a.col1 = b.col1;
>
> GRANT SELECT ON bar1 TO user2;
> GRANT SELECT ON bar2 TO user2;
>
> CONNECT user2/user2
>
> SELECT * FROM user1.bar1; -- fails with ORA-00942: table or view does not exist
> SELECT * FROM user1.bar2; -- succeeds as expected
>
> Note that both bar1 and bar2 are visible in user2's ALL_VIEWS view.
>
> Can anyone help? Thanks.

-- 
===================================================================

Brian Peasland
dba_at_remove_spam.peasland.com

Remove the "remove_spam." from the email address to email me.


"I can give it to you cheap, quick, and good. Now pick two out of
 the three"