Newsgroups: comp.databases.oracle.server
Path: newssvr20.news.prodigy.com!newsmst01.news.prodigy.com!prodigy.com!prodigy.com!in.100proofnews.com!in.100proofnews.com!news.maxwell.syr.edu!newspeer.radix.net!news.er.usgs.gov!news
From: Brian Peasland <dba@remove_spam.peasland.com>
Subject: Re: Grant permissions on views with WITH clauses
X-Nntp-Posting-Host: edcxpw014.cr.usgs.gov
Content-Type: text/plain; charset=us-ascii
Message-ID: <3F5E0DD3.4F83D484@remove_spam.peasland.com>
Sender: news@igsrsparc2.er.usgs.gov (Janet Walz (GD) x6739)
Content-Transfer-Encoding: 7bit
Organization: U.S. Geological Survey, Reston VA
X-Accept-Language: en
References: <vlrtv03uvscla3@news.supernews.com>
Mime-Version: 1.0
Date: Tue, 9 Sep 2003 17:28:51 GMT
X-Mailer: Mozilla 4.78 [en] (Windows NT 5.0; U)
Lines: 102
Xref: newssvr20.news.prodigy.com comp.databases.oracle.server:242707

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@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"