gnuoytr_at_rcn.com (robert) wrote:
>"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
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
