This discussion is archived
0 Replies Latest reply: Mar 12, 2013 10:39 PM by 996506 RSS

Issue with Referential Integrity check in Oracle VPD Policy

996506 Newbie
Currently Being Moderated
Hi,

Lets assume I have two tables - Customer and Order, with cust_id in Order table referring to primary key of Customer table.

Example Data;

Customer
-------------
cust_id Name
1 abc
2 def
3 ghi

Order
---------
Order_id cust_id Order_type
1 1 A
2 2 A
3 1 B


Now I have policies defined on both the tables;
- for "Select, Insert, Update" queries on Customer table.
- for "Select" queries on Order Table.

Policy 1 on Order Table;
------------------------------------------

Irrespective of the user, predicate = 'Order_type = ''A'''

Policy 2 on Customer Table;
--------------------------------------

Irrespective of the user, predicate = '(select count(1) from order o where o.cust_id = customer.cust_id and o.order_type = ''B'') > 0'

My intention is to show only those customers who have atleast one order of type 'B'. And this policy works fine in case a user tries to read data from customer table. (for example, record for cust_id = 2 will not be returned as it don't have any orders of type "B")

However, when a user tries to insert record in Order Table, because of the existing referential integrity constraint, the Policy on Customer table is also getting triggered. And an exception is being raised "ORA-28113: policy predicate has error".

Could someone please explain why this is happening ?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points