Oracle FAQ Your Portal to the Oracle Knowledge Grid

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

Re: tough choices

From: Howard J. Rogers <>
Date: Fri, 25 Jun 2004 20:38:53 +1000
Message-ID: <40dc009f$0$18196$>

"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
> > 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.

> > * Secure Application Contexts (the afore mentioned user information,
> > which cannot be spoofed)
> > * Global Application Contexts (same again, this time shared across
> > multiple connections)
> Can't comment, don't know.
> > * Relevant Column Enforcement (applies security policy only when query
> > accesses named columns)
> I get less rows if I refer to a protected column? Never heard of that
> requirement before...

Neither have I actually! Fine-grained auditing certainly creates an audit record if you view particular column data, but it doesn't actually stop you seeing it.

> > * Relevant Column Masking (all rows are returned, but relevant columns
> > are masked (hidden) according to security policy)
> Views, relying on join-elimination, unused column drop.
> > * Partitioned Fine Grained Access Control (allows multiple security
> > policies to be applied to the same table, information about the user
> > determines which policy is applied)
> Like security clearances? Views again...

Again, no. Views would be too static, too complex, too many of them, not flexible enough.

> > * Proxy Authentication - allows a user identity on a client to be
> > securely proxied through a middle tier, without the need for the middle
> > tier to know the users security credentials (password etc).
> > * Audit Policies (similar to security polcies, in that they are specific
> > to what the user trys to access, and that they fire an audit event)
> Can't comment, don't know
> > * Audit trails that include what data the user saw at the time they
> > performed the operation (uses Flashback if the row has since been
> > changed, deleted, etc)
> Uhm... but flashback is only good for a short time, right? There is no
> guarantee the flashback is still available (?)

UNDO_RETENTION. It's as good as a guarantee, provided only you aren't stingy with your undo tablespace.

> > * Administrator Audit Trails - an audit trail of what the DBA did that
> > the DBA etc cannot see.
> > * Proxied User Audit Trails - an audit trail that shows what a client
> > did via a middle tier proxy.
> >
> > Last but not least, 10 independent security certifications (over
> > multiple releases).
> I'm not going to get into that one....
> >
> > Note that this is just the base EE product - the Advanced Security
> > Option, and Label Security, extends this list of features (and
> > certifications) even further.
> IMHO a lot of the finegrained access control part tastes like syntacic
> sugar.
> Complexity gets shifted from a view definition to the table DDL (it's
> got to be modeled somewhere).

No, the complexity is contained within the security policy definitions. The table DDL itself is unchanged.

It's really rather neat, actually.

> How does Oracle enforce?

On RLS, by re-writing the query at parse time. By appending a where clause that the application itself didn't issue.

HJR Received on Fri Jun 25 2004 - 05:38:53 CDT

Original text of this message