Re: Strictly Enforcing Query Plan Stability

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)

Its not the voices in my head that bother me... 
  its the voices in yours.