Oracle FAQ Your Portal to the Oracle Knowledge Grid

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

Re: tough choices

From: Turkbear <>
Date: Fri, 25 Jun 2004 12:23:15 -0500
Message-ID: <> (robert) wrote:

>"Howard J. Rogers" <> wrote in message news:<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.
>> 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".

Not Cross-posted.

I use Business Objects' Business Views to enforce row and column-level security based on Crystal Enterprise's authentication of users..( all our enterprise reporting is done through CE ) - Create Business views and you can then specify who has access to what rows and/or columns. My Report designers ( using Crystal Reports) only have access to those views not to the database table/views directly.

Not a plug, just a comment since robert mentioned crystal. Received on Fri Jun 25 2004 - 12:23:15 CDT

Original text of this message