Violation of VPD Policy on DML raises FRM-40654 :Record Has Been Changed by Another User error in Oracle Forms

From previous posts, you may have understood that I have been using VPD policies – Virtual Private Database or Fine Grained Access Control – quite extensively in my current project. Since the data visibility is dependent on quite complex conditions, we use VPD Select policies to govern which records are retrievable by a certain user given a specific context. For DML operations, an even more restricive set of policies is required, as it is quite common that a user may see more records than he or she is allowed to updated. In the spirit of VPD, I made liberal use of VPD DML policies on Insert, Update and Delete operations to control which modifications were allowed to users.

The Unit-tests I performed using utPLSQL sailed through successfully: no records were DML-ed that were not allowed and the DML operations that should complete were completed as expected. So everything looked hunky dory, and I happily submitted my work and went on to greener pastures.

Unfortunately, before too long I was hauled back. The end-users had started testing the WebForms application and running into strange error messages: FRM-40654 :Record Has Been Changed by Another User when they attempted some Insert, Update or Delete operations.

It took a while before we realized what was happening: when the user attemped a DML operation that was not allowed by the VPD Policy, the policy does not raise an error or exception it just will not manipulate the records the user cannot touch. The value of SQL%ROWCOUNT will be zero or at least lower than what Oracle Forms expects! Apparently, Oracle Forms will always interpret that situation – the database reports fewer records manipulated than Forms attempted to touch – in this manner, by responding with a FRM-40654.

The consequence is that for reasons of proper user feedback, we cannot continue to use the VPD DML policies. Instead, I have implemented CDM RuleFrame Business Rules that will validate at the end of the transaction whether the DML operations in the transactions are allowed. Needless to say this far more work. However it provides much better user feedback.

We briefly considered trapping the FRM-40654 error and replacing it with our own error message. However, we cannot display a specific message, while the DML operation may have failed on a number of different records. Furthermore, it may actually be correct to display the FM-40654. Finally, it may happen that the primary DML operation initiated by Oracle Forms is successful but some secondary triggered operation is not. A violation of the DML policy by that secondary DML event is not reported back the end-user as Oracle Forms never learns about it. If we want to ensure the end-user is aware of the failure, we need to raise an exception.

In a subsequent post I can tell a little bit more about implementing these DML policies through CDM RuleFrame business rules – based on TAPI triggers and packages.