Re: tough choices

"Howard J. Rogers" <hjr_at_dizwell.com> wrote in message news:<40dc009f$0$18196$afc38c87_at_news.optusnet.com.au>...
> "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.
>
> > > * 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.
>
> Regards
> HJR
i'm in the process of implementing this in a piece of software which is truly weird, but uses oracle (and db2, but thankfully not at this customer). since i despise both the semantics and syntax of BottleFutz, i dreaded having to find another job. to my rescue came FGAC. it does work as advertised. i don't need to write BottleFutz code. the security functionality can be sold off to other BottleFutz customers who are on oracle (with just a few edits) and never touch that code.

the only glitch that's come up (today, of course) is that dropping a package doesn't drop the policy functions attached to it (no cascade delete). so when i changed the policy name from Test to Real_Name, ran my drops with the Real_Name, and got 28110 error. turns out, there was a row left in SYS.DBA_POLICIES with the package name and the old policy name. ran the drop policy script.

it really is pretty slick. and has the advantage of being able to say to clients: "security can't be subverted by crystal reports".

robert Received on Fri Jun 25 2004 - 11:48:20 CDT