| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: SQL question avoiding 2 views and not in
I have also been on a project which used fine-grained access control. The
project was a reporting application for a large organisation and one of the
problems we found we dealing with the various levels of security. The
organisation has ~10,000 cost centres and some users could see only a very
small branch, whilst others could see the entire structure, whilst others
could see almost everything except for a few very high level branches.
There was also a different way of providing security (ie: not cost centre
related) and many users had a combination of both to be applied.
One of the biggest "gotchas" was performance tuning the application. Because of the complexity of our security implementation we initially fell in the trap of tuning the query with no security added (a couple of users had the clause "and 1 = 1"). Naturally when other users connected the explain plan changed and performance was pretty average. It took a while to index all tables in such a way that it worked well for every type of security clause. So, yes there can be a performance hit and that hit can vary dramatically depending on what type of clause you are adding.
Having said that, it did work and was transparent to the front end, allowing users to create their own queries / reports and still be bound by the security model.
Cheers,
Mark.
JApplewhite_at_austin.isd
.tenet.edu To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
Sent by: cc:
root_at_fatcity.com Subject: RE: SQL question avoiding 2 views and not in
14/12/2002 10:03
Please respond to
ORACLE-L
Lisa,
A couple of years ago, when I was a consultant, I implemented Application Context and Fine-Grained Access Control, AKA Row Level Security for a client.
Since it causes a predicate to be appended to the Where clause of every SQL statement issued against the tables having a Security Policy, I guess performance could be impacted if you didn't index the columns referenced by the appended predicates. We never noticed a bit of degradation in our testing, but we were careful about the indexing.
I left that project before it went into production, so I don't know the ultimate outcome. However, I'd sure use FGAC again, if the need arises, it works very well. Actually, I probably *will* use it on a couple of our 3rd Party apps here which don't enforce security to the degree that we require. I'll let y'all know how it performs.
Jack C. Applewhite
Database Administrator
Austin Independent School District
Austin, Texas
512.414.9715 (wk)
512.935.5929 (pager)
JApplewhite_at_austin.isd.tenet.edu
"Koivu, Lisa"
<Lisa.Koivu_at_efair To: Multiple
recipients of list ORACLE-L
field.com> <ORACLE-L_at_fatcity.com>
Sent by: cc:
root_at_fatcity.com Subject: RE: SQL question
avoiding 2 views and not in
12/13/2002 03:38
PM
Please respond to
ORACLE-L
Has anyone used context and fine-grained security? I seem to remember the performance hit was not minimal when using this functionality.
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: JApplewhite_at_austin.isd.tenet.edu Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Sun Dec 15 2002 - 16:03:38 CST
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Privileged/Confidential information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such case, you should destroy this message and kindly notify the sender by reply e-mail or by telephone on (61 3) 9612-6999. Please advise immediately if you or your employer does not consent to Internet e-mail for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of Transurban City Link Ltd shall be understood as neither given nor endorsed by it. <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Richard INET: mrichard_at_transurban.com.au Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
 |
 |