Newsgroups: comp.databases.oracle.server Path: newssvr20.news.prodigy.com!newsmst01.news.prodigy.com!prodigy.com!logbridge.uoregon.edu!news.maxwell.syr.edu!newspeer.radix.net!news.er.usgs.gov!news From: Brian Peasland 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: <3F5E34A1.F8C24BF7@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: <3F5E0DD3.4F83D484@remove_spam.peasland.com> Mime-Version: 1.0 Date: Tue, 9 Sep 2003 20:14:25 GMT X-Mailer: Mozilla 4.78 [en] (Windows NT 5.0; U) Lines: 55 Xref: newssvr20.news.prodigy.com comp.databases.oracle.server:242725 Darn. I was hoping you'd be able to file a bug report with Oracle Corp. I guess someone beat you to the punch. Cheers, Brian Cheng-Jih Chen wrote: > > 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. -- =================================================================== 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"