Re: Strictly Enforcing Query Plan Stability

But Greg points out that ad-hoc is particularly the thing he wants to stop - any ad-hoc query which therefore does not have a precomputed  plan should fail at parse time.

If this were my aim, I think I would go for a 3-tier architecture, using the 'trusted proxy user', so that end users never knew their own Oracle passwords, and certainly never learnt the password of the proxy (8.1 / OCI only feature though).

You could try creating a view on the outln$ table that forced a once-per-execute call to a function that failed if no rows were returned. Then the recursive SQL to parse the query would crash if no plan existed.

--

Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Mark D Powell wrote in message <8sevp8$bn9$1_at_nnrp1.deja.com>...


>The thread is on Greg's desire for the optimizer to reject all queries

>that do not have plan stability defined.

>

>I would like to point out that request is not reasonable or well

>considered because of something called adhoc!  If the end-user has

>access to end-user reporting tools there is no way to antiscipate all

>possible queries nor should you have to.  The CBO was invented to

>handle adhoc queries.  For production code you should lock the path in

>using hints or the new query plan statbility feature, but even here we

>have found the CBO is usually good enough and we only tune what we have

>to.

>

>

>In article <39E1A92F.1A5D_at_yahoo.com>,

>  connor_mcdonald_at_yahoo.com wrote:

>> Greg Stark wrote:

>> >

>> > I have a wishlist feature I would love Oracle to support. In fact

 I'm


>> > surprised Oracle doesn't support it already. Perhaps there's a

 trick to


>> > accomplish what I want and I just don't know it?

>> >

>> > What I would like is to be able to set privileges such that my

 application


>> > cannot run _any_ query that doesn't have an outline already stored

 for it.


>> > Essentially what I want is to be able to guarantee that no code

 could possibly


>> > go live without _every_ SQL query being analyzed and the plan

 approved by a


>> > DBA. Any unapproved query should immediately get an error, not be

 run with


>> > some ad hoc query plan that could very well bring the whole

 application down.


>> >

>> > It seems to me that anything less is simply inadequate for a

 production


>> > mission critical system. Given the types of applications that run

 on Oracle


>> > I'm surprised this isn't a fundamental feature of the system since

 day 1.


>> >

>> > --

>> > greg

>>

>> Easy enough to do...

>>

>> In its simplest form, users have 'create session' and a role which

 lets


>> them do whats needed in the applications.  The role is only enabled

 when


>> they launch the app (as protected by a role password).

>>

>> Then outside the app, the only thing that they can do is connect.  All

>> you need do then to ensure that nothing is 'grant select to public'

 and


>> you're protected..

>>

>> HTH

>> --

>> ===========================================

>> Connor McDonald

>> http://www.oracledba.co.uk

>> (faster/mirrored at http://www.oradba.freeserve.co.uk)

>>

>

>

>--

>Mark D. Powell  -- The only advice that counts is the advice that

> you follow so follow your own advice --

>

>

>Sent via Deja.com http://www.deja.com/

>Before you buy.