0 Replies Latest reply: Mar 13, 2013 12:39 AM by 996506 RSS

    Issue with Referential Integrity check in Oracle VPD Policy

    996506
      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 ?