Oracle PL/SQL for DBAs

Contexts as Predicates in RLS

So far you have learned that a procedure must be used to set a context value, which is akin to a global package variable. You might be tempted to ask, “How is that useful? Doesn’t it increase the complexity rather unnecessarily without achieving any definite purpose?”

No. Because the trusted procedure is the only way to set a context attribute’s value, you can use it to maintain execution control. Inside the trusted procedure, I can place all types of checks to ensure that the variable assignments are valid. I can even completely eliminate the passing of parameters and set the values from predetermined values without any input (and therefore influence) from the user. Going back to the requirement for employee access, for instance, I know that we need to set the application context value to a string of department numbers, picked from the table EMP_ACCESS, not passed in by the user.

To accomplish this, I will use the application context in the policy function itself. First, I need to modify the policy function.

      1 CREATE OR REPLACE FUNCTION authorized_emps (
      2     p_schema_name   IN   VARCHAR2,
      3     p_object_name   IN   VARCHAR2
      4  )
      5     RETURN VARCHAR2
      6  IS
      7     l_deptno       NUMBER;
      8     l_return_val   VARCHAR2 (2000);
      9  BEGIN
     10     IF (p_schema_name = USER)
     11     THEN
     12        l_return_val := NULL;
     13     ELSE
     14        l_return_val := SYS_CONTEXT ('DEPT_CTX', 'DEPTNO_LIST');
     15     END IF;
     16
     17     RETURN l_return_val;
     18  END;

Here the policy function expects the department numbers to be passed through the attribute ...