Re: Grant permissions on views with WITH clauses

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_at_remove_spam.peasland.com>,
> Brian Peasland <dba_at_remove_spam.peasland.com> 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_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"