Path: newssvr20.news.prodigy.com!newsmst01.news.prodigy.com!prodigy.com!in.100proofnews.com!in.100proofnews.com!news.maxwell.syr.edu!sn-xit-03!sn-xit-06!sn-post-01!supernews.com!news.supernews.com!not-for-mail From: postmaster@cjc.org (Cheng-Jih Chen) Newsgroups: comp.databases.oracle.server Subject: Re: Grant permissions on views with WITH clauses Date: Tue, 09 Sep 2003 18:50:59 -0000 Organization: CJC Organization Message-ID: References: <3F5E0DD3.4F83D484@remove_spam.peasland.com> Sender: postmaster@cjc.org Reply-To: postmaster@cjc.org X-Newsreader: trn 4.0-test76 (Apr 2, 2001) Originator: cjc@cjc.org (Cheng-Jih Chen) X-Complaints-To: abuse@supernews.com Lines: 37 Xref: newssvr20.news.prodigy.com comp.databases.oracle.server:242715 In article <3F5E0DD3.4F83D484@remove_spam.peasland.com>, Brian Peasland wrote: >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. Thanks. I dug around Metalink a bit, and found the following bug: http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=BUG&p_id=3083673 Bug No. 3083673 Filed 05-AUG-2003 Updated 18-AUG-2003 Product Oracle Server - Enterprise Edition V7 Product Version 9.2.0.3.0 Platform Generic Platform Version WINDOWS 2000 Database Version 9.2.0.3.0 Affects Platforms Generic Priority Severe Loss of Service Status Code Bug (Response/Resolution) Base Bug N/A Fixed in Product Version No Data Problem statement: ORA-942 WHEN SELECTING FROM VIEW WITH SUBQUERY FACTORING Unfortunately, they don't have a workaround beyond doing GRANTs on the underlying tables in the WITH clause, which is something my client isn't going to do. The date on the bug is also too recent for Oracle to have put out a patch. Blah.