Re: VPD and modifying FROM clause

Well, using EXISTS in the predicate function eliminated the need for a subselect.

However, an odd thing I noticed is that the predicate function appears to be called twice for every SQL statement using a table with a policy set. I created a logging table to prove this, and added an INSERT to the predicate function to see what was going on.

Why do predicate functions get called twice for each SQL statement?

HansF wrote:
> On Mon, 16 May 2005 06:45:33 -0700, cochrane68 interested us by writing:
>
> > We're using an Oracle 9i VPD to control security. The VPD allows
> > modification of the WHERE clause, but is there any way to modify the
> > FROM clause also?
> >
> > If we are only able to modify the WHERE clause, we will be forced to
> > use a subselect which over time will perform poorly due to data
> > proliferation. A modification to the FROM clause would allow us to use
> > a JOIN condition, which should perform much better.
>
> VPD policy is attached to an object. Specifically one of the objects in
> the FROM clause. Accessing the object is what triggers the VPD policy. So
> VPD can not change the FROM list.
>
> If you keep your stats current, you may not have quite the issue with
> subselects as you believe. (You ARE using CBO, aren't you????) However,
> you may also not be restricted to subselect, as a number of subselect
> patterns can be replaced by other patterns.
>
> If you move to 10g, you will also have access to the rewrite API. That
> allows you to accomplish your objective directly - I believe there are
> examples at PSOUG's site in Morgan's library.
>
> --
> Hans Forbrich
> Canada-wide Oracle training and consulting
> mailto: Fuzzy.GreyBeard_at_gmail.com
> *** I no longer assist with top-posted newsgroup queries ***
Received on Thu Jun 02 2005 - 11:16:49 CDT