Home » RDBMS Server » Security » VPD fine grain access requirement (10g)
VPD fine grain access requirement [message #432947] Fri, 27 November 2009 09:48 Go to next message
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
icon12.gif  Re: VPD fine grain access requirement [message #435136 is a reply to message #432947] Mon, 14 December 2009 09:29 Go to previous messageGo to next message
LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL
Senior Member

You can achieve all your requirements configuring VPD, just check out the fine Oracle® Database Security Guide.
Shocked
Re: VPD fine grain access requirement [message #435138 is a reply to message #435136] Mon, 14 December 2009 10:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
LKBrwn_DBA wrote on Mon, 14 December 2009 16:29

You can achieve all your requirements configuring VPD, just check out the fine Oracle® Database Security Guide.
Shocked

Very useful answer, can you show us how you achieve the following point:
Quote:
the function can identify the Mart from the named tables in the SQL FROM list.

Regards
Michel

Re: VPD fine grain access requirement [message #435141 is a reply to message #435138] Mon, 14 December 2009 10:55 Go to previous messageGo to next message
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 #435142 is a reply to message #435141] Mon, 14 December 2009 11:02 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the feedback.
Indeed it is surprising and worth a deeper investigation.

Regards
Michel
icon12.gif  Re: VPD fine grain access requirement [message #435160 is a reply to message #435138] Mon, 14 December 2009 14:59 Go to previous messageGo to next message
LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL
Senior Member
Michel Cadot wrote on Mon, 14 December 2009 11:25
Very useful answer, can you show us how you achieve the following point:
Quote:
the function can identify the Mart from the named tables in the SQL FROM list.

Regards
Michel


It's a VPD functionality, check out the DBMS_RLS.ADD_POLICY () procedure.

Razz
Re: VPD fine grain access requirement [message #435175 is a reply to message #435160] Mon, 14 December 2009 23:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Irrelevant answer (once more).
It is a VPD policy to " identify the Mart from the named tables in the SQL FROM list"?
Please post the link.

Regards
Michel

[Updated on: Tue, 15 December 2009 00:00]

Report message to a moderator

Re: VPD fine grain access requirement [message #442712 is a reply to message #432947] Wed, 10 February 2010 02:00 Go to previous messageGo to next message
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


    MART A
    MART B
    MART C



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 #443879 is a reply to message #432947] Wed, 17 February 2010 16:56 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
I would be wary of basing a production solution off a virtual performance table. Access to these tables is not guarenteed in the future and oracle is free to change their format, content, and even their existence.

I guess if it is all you have and your are resolute in your desire to achieve your ends then you go with it.

Just for perspective purposes, if this were a system I was in charge of designing/developing/supporting, I would say no, you may not use any oracle dictionary table in your production solutions. If they say "we have no other way" then I would say, look harder. If they say "we did look harder" then I would asy, OK then you you have failed to find an acceptable implementation so you don't get the feature.

But I act like I have real authority, and maybe I am being overly cautious. Anyone have a different opinion.

Kevin
Re: VPD fine grain access requirement [message #443961 is a reply to message #432947] Thu, 18 February 2010 02:52 Go to previous message
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
Previous Topic: Oracle Label Security
Next Topic: User Password change
Goto Forum:
  


Current Time: Thu Mar 28 07:50:53 CDT 2024