Home » RDBMS Server » Security » VPD function for column masking with inner-select (Oracle 11g R2)
VPD function for column masking with inner-select [message #595612] Thu, 12 September 2013 07:47 Go to next message
pythian
Messages: 2
Registered: September 2013
Junior Member
I'm writing a VPD function to be used for column masking. The predicate (WHERE-clause) it generates may take many different forms. In particular, it may contain inner-selects; for example,
   "exists(select '*' from B where B.VAL = '123' and A.KEY = B.KEY)"

where A is the table that is associated to the VPD function, and B is some other table.

I wonder if this is OK for column masking? If not, my VPD function may sometimes work and sometimes fail, in unexpected ways.

The Oracle Database Security Guide (11g Release 1) says

Quote:

Column-masking conditions generated by the policy function must be simple Boolean expressions, unlike regular Oracle Virtual Private Database predicates".

This seems to indicate that there are cases where a VPD function works for row-level security, but not for column masking.

Does anyone have an example of a 'regular Oracle VPD predicate' that doesn't work for column-masking?

Thanks
Re: VPD function for column masking with inner-select [message #595616 is a reply to message #595612] Thu, 12 September 2013 07:55 Go to previous messageGo to next message
Michel Cadot
Messages: 59081
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Scroll your link down to "Tutorial: Creating a Simple Oracle Virtual Private Database Policy" section.

Regards
Michel
Re: VPD function for column masking with inner-select [message #595617 is a reply to message #595612] Thu, 12 September 2013 08:03 Go to previous messageGo to next message
John Watson
Messages: 4552
Registered: January 2010
Location: Global Village
Senior Member
I think you are trying to select from a policy protected table, A, in the policy function: that is not possible.
Re: VPD function for column masking with inner-select [message #595619 is a reply to message #595617] Thu, 12 September 2013 08:46 Go to previous messageGo to next message
pythian
Messages: 2
Registered: September 2013
Junior Member
Thanks for the quick answers.

[@John]
It is true, the VPD predicate cannot select from the policy protected table. Such a restriction applies to VPD predicates regardless of whether they are used for row-level security (RLS) or column-masking.

However, notice that in my example the predicate selects from table B, which is not the policy protected table.

[@Michel]
I should have probably clarified that I've already written VPD functions for RLS, with inner-selects. My concern is with the differences between RLS and column-masking.

Cheers,
Pablo
Re: VPD function for column masking with inner-select [message #595624 is a reply to message #595619] Thu, 12 September 2013 09:02 Go to previous messageGo to next message
Michel Cadot
Messages: 59081
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't understand your question.
If you already did both genuine RLS and column masking you should know the difference, won't you?
The difference is in the call of ADD_POLICY which defines what you protect.
In your link:
BEGIN
 DBMS_RLS.ADD_POLICY(
   object_schema         => 'scott', 
   object_name           => 'emp',
   policy_name           => 'hide_sal_policy', 
   policy_function       => 'hide_sal_comm',
   sec_relevant_cols     =>' sal,comm',            --\__ there
   sec_relevant_cols_opt => dbms_rls.ALL_ROWS);    --/
END;
/

BEGIN
  DBMS_RLS.ADD_POLICY (
    object_schema    => 'oe',
    object_name      => 'orders',
    policy_name      => 'orders_policy',
    function_schema  => 'sys',
    policy_function  => 'auth_orders',
    statement_types  => 'select, insert, update, delete' --> and there
   );
 END;
/

Regards
Michel
Re: VPD function for column masking with inner-select [message #595625 is a reply to message #595619] Thu, 12 September 2013 09:03 Go to previous message
John Watson
Messages: 4552
Registered: January 2010
Location: Global Village
Senior Member
Well, Pablo, you can try. But I would say that you are SELECTING ROWS from A and B, but PROJECTING COLUMNS only from B.
Previous Topic: ORA-01720: grant option does not exist for '%%'
Next Topic: GRANT ANY OBJECT PRIVILEGE
Goto Forum:
  


Current Time: Tue Sep 16 06:46:51 CDT 2014

Total time taken to generate the page: 0.08704 seconds