Re: tough choices

"Serge Rielau" <srielau_at_ca.eye-be-em.com> wrote in message news:cbh13o$e84$1_at_hanover.torolab.ibm.com...
> Howard J. Rogers wrote:
>
> > "Serge Rielau" <srielau_at_ca.eye-be-em.com> wrote in message
> > news:cbgou8$dmp$1_at_hanover.torolab.ibm.com...
> >>>* Security Policies (policies attached to tables and views that
> >>>determine what rows can be accessed based on information known about
the
> >>>user)
> >>
> >>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.

Maybe. I don't know enough about DB2 views to comment one way or another. All I would say is that the RLS policy is extremely flexible, because you code it whatever way you want. If you want it to test for username coming from a specific IP address on alternate Thursdays after 5.00pm, you can do so. Where it starts getting very clever indeed is where I can write multiple policies for the one table that can be ANDed or ORed as you choose. And you can have a default policy to apply as well or instead of. That is, I think, what Mark meant by 'partitioned fine grained access control'. I can have one table, call it SALES. I have customers who I want to be able to select particular rows based on their customer code. I also have salesmen, who I want to be able to select particular rows based on sales region. Two completely different sorts of where predicates to apply; two completely different types of session level credentials to be checked; one table, two bits of quick PL/SQL, effectively, and it's done. Now imagine a table with half a dozen different possible audiences, and hence half a dozen different possible policies to apply depending on which way the wind was blowing at the time... that is not something that views (Oracle views, at least) would make very easy to do. If they could do it at all, there would be a mammoth effort required at the point of view definition to juggle all the possible variables and permutations. That would be difficult code to maintain. It's inflexible, and it wouldn't scale.

> 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'm no developer. Maybe your powers of SQL writing are better than mine (it wouldn't surprise me!). But what RLS can do, no embedding of global variables in Oracle views can match.

> 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?)

I doubt it, but I've never done it to check. I smell the breakable parse lock in the neighbourhood!!

Cheers,
HJR Received on Fri Jun 25 2004 - 06:51:51 CDT