Re: Grant permissions on views with WITH clauses

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. Received on Tue Sep 09 2003 - 13:50:59 CDT