Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: tough choices

Re: tough choices

From: Serge Rielau <>
Date: Fri, 25 Jun 2004 07:08:17 -0400
Message-ID: <cbh13o$e84$>

Howard J. Rogers wrote:

> "Serge Rielau" <> wrote in message
> news:cbgou8$dmp$

  >>>* Security Policies (policies attached to tables and views that
>>>determine what rows can be accessed based on information known about the
>>Can be handled with views. Let the DBMS do what the DBMS does best.

> Mark can answer for himself, but no this sort of thing can't reasonably be
> handled with views. I have a sales table. I want customers to access it...
> but they must only see their own rows. If all I've got are views, I've got
> to create a different view for each user. And change my application so it
> references the right view at the right time as new views are added because
> new customers are acquired. Views just won't cut it.
> RLS (VPD) ((FGAC)) (((!!!!))) means the app can issue one SQL statement, and
> the optimiser will re-write it, depending on who you are, where you're
> querying from, any other attributes I care to capture about you as you log
> on. And because the optimiser is re-writing the query, I don't have to
> modify my application code. And yes, you're right: the DBMS does this, and
> does it best... if I need to change the way it all works, I can change a
> policy on the back-end, not modify my code in the application.
I don't buy the part about the view having to change. The view is "parameterized" with session level credential. In DB2 this could, for example be the USER register as used in my example. With table based row level access control the DBMS also must look up these credentials.
The difference is to bury them or to expose them. I recall a debate with Daniel (or Nuno?) about when to use "global varibales" in views and when not. Thsi a case where I believe it is proper. In a way that makes views even MORE flexible because all the power of SQL is available to enforce the semantics I want. I agree that changing the semantics is more cumbersome in the DB2 case at present due to the pain of view-evolution. In Oracle the pain is not so prevalent because of automatic revalidation. Which makes the difference whether a change in policy on the table feature causes a loss of the cached plans or not. (hmmm. can I change the policy in the midst of someones transaction?)


PS: I shall read up on O10g language support for this to better understand where you're coming from.

Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Received on Fri Jun 25 2004 - 06:08:17 CDT

Original text of this message