Virtual Private Database is a quite powerful concept. It lets you set up a mechanism in the database that dynamically appends additional filter-conditions to the queries performed by end users and applications against tables that are subject to so called VPD Policies. The dynamic part of VPD lies in the fact that these policies can make use of context-settings. These settings can range from the name of the currently active user or the role of the user to the specific module or task that is currently performed.
We make pretty heavy use of VPD in our current WebForms 10g, Designer 10g, RDBMS 10g project. I have written a few earlier posts to discuss some of the challenges we have faced and some of the solutions we implemented. This one presents our latest challenge. It goes like this:
We have a Form that is used to Maintain Request Forms. Such Request Forms can only be manipulated by users that have mutation privileges – defined in an application table – and only while the Request Form has the status In Progress. As soon as the status is changed to either Submitted or Accepted, Denied or Withdrawn, the Request Form can no longer be edited.
In an ON-LOGON trigger, we set up a context with the userid of the current user that we derive from our own table of application users, based on the Oracle Database User Name. When we start any Form in the application, it will set up another value in the Application Context by calling a database package informing it on the form name and possibly the current mode. We have set up VPD Policies that govern the data made available from any table given the circumstances.
This includes a VPD Policiy on the table with Request Forms. This policy will only return Request Forms with Status In Progress and whith mutation privileges for the current user when the Form Maintain Request Forms is running. When the Form Browse Request Forms is running, all Request Forms are returned. So far so good. It works quite allright. However, when the status of the Request Form record is changed from In Progress to Submitted, we have an issue: immediately after saving the change, we get an Oracle 1403 No Data Found error. For making an Update!
Upon closer investigation, we found out the issue: the table definition for Request Forms in Oracle Designer has several Server Derived Columns. The Designer Form Generator has generated a Synchronization Program Unit in the Form that is invoked from the post-update trigger to retrieve the values of these columns immediately following the update. So after the Update has changed the status of the Request Form from In Progress to Submitted, the Form will select the request form to find out about the values of the server derived columns. However, our VPD policy hits relentlessly and instantaneously: this form for editing Request Forms can only retrieve Request Forms with status In Progress. And the have just changed the status of the Request Form that we are no trying to retrieve in this generated piece of synchronization logic. The synchronization code does not have an WHEN NO_DATA_FOUND exception handler – as I have been taught you should always have – and now VPD lightning strikes.
We are not certain yet on the best way to fix this. We are not even entirely sure about what we would like to see: should the Request Form record we have just updated vanish immediately from the Form? That might be confusing to the end-user. On the other hand, we do not want him changing a record with status Submitted, so he should not see the record anymore, since that would be confusing too. Simply handling the NO-DATA-FOUND exception can be done by either bulk-manipulatinig all synchronization program units generated by Oracle Designer through a JDAPI program that we can run on all generated forms. We can also add logic to the POST-INSERT and POST-UPDATE triggers to either handle the exception or set some sort of special synchronization mode in the application context that will temporarily relax the VPD policy.