Re: Grant permissions on views with WITH clauses

Just as a little refinement on the problem.

If you grant select on the underlying tables, then queries against the view __MAY__ fail 'randomly' with error 32036 when executed by the grantee.

The problem may not occur with all variations of using subquery factoring and I haven't tried identifying exact boundaries of the problem, but the point at which the issues appears is when the optimiser decides to INLINE the factored subquery rather than generating a temporary table for it.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html

____Finland__September 22nd - 24th
____Norway___September 25th - 26th
____UK_______December (UKOUG conference)

Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____USA__October
____UK___November


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


"Cheng-Jih Chen" <postmaster_at_cjc.org> wrote in message
news:vls88j37op835f_at_news.supernews.com...


> 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.

>