Oracle PL/SQL for DBAs

Static RLS Policies

So far the examples have demonstrated the use of a static policy, which means that the value returned by the predicate function does not change even if the circumstances under which it is called change.

Given this fact, RLS need not execute the function every time a query is issued against the table. The value can be determined only once, cached, and then reused from the cache as many times as needed. To make a policy behave that way, you can define it as a static policy to RLS by passing TRUE for the static_policy argument:

      1  BEGIN
      2     DBMS_RLS.ADD_POLICY (
      3        object_name      => 'EMP',
      4        policy_name      => 'EMP_POLICY',
      5        function_schema  => 'HR',
      6        policy_function  => 'AUTHORIZED_EMPS',
      7        statement_types  => 'INSERT, UPDATE, DELETE, SELECT',
      8        update_check     => TRUE,
      
      9        static_policy    => TRUE
     10     );
         11 END;

The default value of the static_policy parameter is FALSE: that value makes the policy dynamic rather than static, and it causes the policy function to be called for each operation on the table. I’ll describe dynamic policies later in the chapter in the section "Defining a Dynamic Policy.” Policy types, in addition to static and dynamic, are supported in Oracle Database 10g. See the section "Other Classes of Dynamism" for more information.

There are many situations that call very precisely for a static policy. Consider a merchandise warehouse that is servicing several customers. Here, a predicate might be used to limit the entries to only the relevant records for that customer. ...