| VPD fine grain access requirement [message #432947] |
Fri, 27 November 2009 09:48  |
SimonKirk
Messages: 3
Registered: November 2009
Location: Edinburgh
|
Junior Member |
|
|
Hi
We are enhancing our corporate security model using VPD fine grain access to allow more flexible policies. This will provide different levels of row level access on each set of mart fact tables (Health Board level access on Mart A, GP Practice level on Mart B etc). We also want different column level security (masking) on common dimensions depending on which mart is being queried, e.g. a user might be allowed to see confidential patient columns when querying Mart A, but not on Mart B.
OID groups hold user attributes, and we can retrieve these via logon trigger and policy functions and then set user contexts accordingly.
When a query is submitted to the database (via Business Objects), it triggers the policy function on a particular mart fact table(s), which applies the particular row level constraint based upon the users context. So far so good. Problem is, when any dimension policy functions are being triggered (at the same time), they need to know which particular Mart is being queried, so that they can retrieve the correct user context to apply either confidential or non-confidential column masking.
I basically need a means of interrogating the SQL before (or as) it reaches the dimension policy functions, from which the function can identify the Mart from the named tables in the SQL FROM list. Is there a way of doing this, or some other mechanism entirely for delivering this level of access control?
One solution is to have a separate dimension view specific to each Mart. A particular view would join to a particular mart (in Business Objects), and the policy function amended for each. However we would rather avoid this as it could mean up to 20 + views for each dimension, and require a substantial maintenance overhead.
Thanks
Simon
Edinburgh
|
|
|
|
|
|
|
|
| Re: VPD fine grain access requirement [message #435141 is a reply to message #435138] |
Mon, 14 December 2009 10:55  |
SimonKirk
Messages: 3
Registered: November 2009
Location: Edinburgh
|
Junior Member |
|
|
Michael,
I found a way of identifying the submitted query with the following SQL from within the policy function:
select upper(v1.sql_fulltext)
into l_sql_text
from v$sql v1,
v$session v2
where v2.sid = sys_context('USERENV', 'SID')
and v2.sql_id = v1.sql_id;
I was surprised that the SQL was visible (and presumably parsed) prior to the addition of any RLS conditions, however it's behaviour appears to be consistent.
Simon
|
|
|
|
|
|
|
|
|
|
| Re: VPD fine grain access requirement [message #442712 is a reply to message #432947] |
Wed, 10 February 2010 02:00 |
trueman_ktk
Messages: 2
Registered: March 2009
Location: Pakistan
|
Junior Member |
|
|
Hello Simon,
You Must use ORACLE LABEL SECURITY for that where you could do for example
LEVELS
UNSECURED
CONFIDENTIAL
SENSITIVE
HIGH_SENSITIVE
TOP_SECRET
And compartments
AND furthermore you could create groups and trusted store programe units as well
check out the ORACLE LABEL SECURITY ADMINISTRATOR GUIDE
Regards
|
|
|
|
|
|
| Re: VPD fine grain access requirement [message #443961 is a reply to message #432947] |
Thu, 18 February 2010 02:52 |
SimonKirk
Messages: 3
Registered: November 2009
Location: Edinburgh
|
Junior Member |
|
|
Kevin,
Thanks for the advice. I must admit to not being totally happy with it myself, concerned firstly that it is overcomplicated and secondly that and two fact tables with the same name in differnet schemas will break it(I can't guarantee that non-Business Objects users won't submit queries without the schema qualification). However it does demand very consistent fact table naming conventions, which I guess is a bonus.
I may have another look at the Label Security as suggested above, although I think I did look at this and dismissed it for reasons I can't remember now.
On another slightly unrelated note, I notice from the dbms_output, that the fact table policy funtions appear to fire 2 or 3 times. I.e I get the dbms_output 2 or 3 times for a single run of a query. I plan to look at a trace to see if this is manifested in the resulting additional predicates, but are you aware of why this might happen?
Thanks
Simon
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
