Grant permissions on views with WITH clauses

From: Cheng-Jih Chen <postmaster_at_cjc.org>
Date: Tue, 09 Sep 2003 15:55:12 -0000
Message-ID: <vlrtv03uvscla3@news.supernews.com>

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. Received on Tue Sep 09 2003 - 10:55:12 CDT