Re: VPD and modifying FROM clause

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