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

2

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.

Share.

About Author

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director for Fusion Middleware. Consultant, trainer and instructor on diverse areas including Oracle Database (SQL & PLSQL), Service Oriented Architecture, BPM, ADF, Java in various shapes and forms and many other things. Author of the Oracle Press book: Oracle SOA Suite 11g Handbook. Frequent presenter on conferences such as JavaOne, Oracle OpenWorld, ODTUG Kaleidoscope, Devoxx and OBUG. Presenter for Oracle University Celebrity specials.

2 Comments

  1. Wilfred, you may have a point there. The problem, since we are aiming at more than one user interface technology, we prefer to implment the Business Rule in the Database Server rather than the client. The original implementation using VPD seemed a good idea at the time. Now we have to find another way obviously. Perhaps in case of Forms, we have to it in in the client, but it is not ideal.

  2. I don’t know how complex your DML rule is, but wouldn’t it be more user friendly to determine in the when-new-record-instance whether the record is updateable. If not, make the record read-only in Forms. This prevents the users from changing the record at the beginning without waiting for an error message at commit time.