Re: tough choices

Serge Rielau apparently said,on my timestamp of 27/06/2004 12:55 AM:

> Oracle provides a set of procedures which are used to manage "policies"
> against specific tables (or table like objects)/columns. A policy is
> associated with a function.

Not quite. The user creates the procedure, not Oracle. And the policy itself. And associates one with the other. Which means for example from now on, when in that table you do: SELECT * FROM THAT_TABLE
WHERE TABLE_ID = 2178;
what really executes is:
SELECT * FROM THAT_TABLE
WHERE TABLE_ID = 2178
AND MY_FUNCTION(whatever) = TRUE;
and no one knows about it except the authorized user who created the policy and administrators. Want to change the policy? Good, change the function. Want to create a view on the table? Good, but the view will still have the policy's function added to any predicate for that table after view merge. Want to restrict the policy to SELECT? Nothing could be easier: just create a policy only for SELECT. And so on.

> The promised value add is:
> * in not needing to alter the view, or introduce views for encapsulation
> in the first place.

yes, that is one advantage. Although I prefer to create a view on the table THEN add the policy to the view. This means I can (to a degree) isolate alterations to definition from the policy.

> * The predicate may be injected or not - depending on whatever the
> policy maker pleases (such as time of day, mood of the boss)

Not quite. Bottom line is: the policy is always active, it's the function that controls partial or time access in your example. Note that the function is stored PL/SQL, as complex as you may want to make it. Of course there is a penalty to pay for added complexity.

> * There is some level of encapsulation (i.e. the policy package).

Very much so.

>
> The policy maker is responsible to keep up to date with schema changes
> (or the predicates may break) and to ensure proper indexing exists (or
> else the predicates may bring the query to a grinding halt).

Or they may stay just like they were. Or use new indexes. It's just a predicate, like any other on that table. It either evaluates to true or false, then gets added to all other predicates, whatever indexes they may use or not.

> Did I miss anything?

Not much. Hopefully it will become clear with the example above.

-- 
Cheers
Nuno Souto
wizofoz2k_at_yahoo.com.au.nospam