OraFAQ Forum: Security » VPD function for column masking with inner-select

Home » RDBMS Server » Security » VPD function for column masking with inner-select (Oracle 11g R2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

VPD function for column masking with inner-select [message #595612]

Thu, 12 September 2013 07:47

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

Report message to a moderator

Re: VPD function for column masking with inner-select [message #595616 is a reply to message #595612]

Thu, 12 September 2013 07:55

Michel Cadot
Messages: 68749
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Scroll your link down to "Tutorial: Creating a Simple Oracle Virtual Private Database Policy" section.

Regards
Michel

Report message to a moderator

Re: VPD function for column masking with inner-select [message #595617 is a reply to message #595612]

Thu, 12 September 2013 08:03

John Watson
Messages: 8968
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.

Report message to a moderator

Re: VPD function for column masking with inner-select [message #595619 is a reply to message #595617]

Thu, 12 September 2013 08:46

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

Report message to a moderator

Re: VPD function for column masking with inner-select [message #595624 is a reply to message #595619]

Thu, 12 September 2013 09:02

Michel Cadot
Messages: 68749
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: VPD function for column masking with inner-select [message #595625 is a reply to message #595619]

Thu, 12 September 2013 09:03

John Watson
Messages: 8968
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.

Report message to a moderator

Previous Topic:	ORA-01720: grant option does not exist for '%%'
Next Topic:	GRANT ANY OBJECT PRIVILEGE

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Mar 11 14:26:57 CDT 2025