Oracle PL/SQL for DBAs

Performing an Update Check

Let’s consider a slight twist on our earlier example. Instead of updating the COMM column, the user now updates the SAL column. Because SAL is the column used in the predicate, it will be interesting to see the result.

    SQL> UPDATE hr.emp SET sal = 1200;
    7 rows updated.

    SQL> UPDATE hr.emp SET sal = 1100;
    7 rows updated.

Only seven rows are updated, as expected. Now, let’s change the updated amount. After all, everyone deserves a better salary.

    SQL> UPDATE hr.emp SET sal = 1600;
    7 rows updated.

    SQL> UPDATE hr.emp SET sal = 1100;
    0 rows updated.

Note the last update. Why were no rows updated?

The answer lies in the first update. The first one updated the SAL column to 1,600, which is not satisfied by the filtering predicate “SAL <= 1500”. Thus, after the first update, all of the rows became invisible to the user.

This is a potentially confusing situation: the user can execute a SQL statement against rows, and the statement changes the access to those rows. During application development, this seeming data instability may create bugs or at least introduce a degree of unpredictability that makes debugging a challenge. To counter this behavior, we can take advantage of another ADD_POLICY parameter, update_check. Let’s take a look at the impact of setting this parameter to TRUE when we create a policy on the table.

 BEGIN DBMS_RLS.add_policy (object_name => 'EMP', policy_name => 'EMP_POLICY', function_schema => 'HR', policy_function => 'AUTHORIZED_EMPS', statement_types ...