Oracle PL/SQL Programming, 5th Edition

Application Contexts

In the discussion of row-level security in the previous section, I made a critical assumption: the predicate (i.e., the limiting condition that restricts the rows of the table) was the same. In my examples, it was based on the department number of the user. What if I have a new requirement: users can now see employee records based not on department numbers but on a list of privileges maintained for that reason. A table named EMP_ACCESS maintains the information about which users can access which employee information.

SQL> DESC emp_access
 Name              Null?    Type
 ----------------- -------- ------------
 USERNAME                   VARCHAR2(30)
 DEPTNO                     NUMBER

Here is some sample data:

USERNAME                           DEPTNO
------------------------------ ----------
MARTIN                                 10
MARTIN                                 20
KING                                   20
KING                                   10
KING                                   30
KING                                   40

Here I observe that Martin can see departments 10 and 20, but King can see 10, 20, 30, and 40. If an employee’s name is not here, he cannot see any records. This new requirement requires that I generate the predicate dynamically inside the policy function.

The requirements also state that users can be reassigned their privileges dynamically by updating the EMP_ACCESS table, and that it is not an option to log off and log in again. Hence, a LOGON trigger (see Chapter 19) will not help in this case.

Solution? One option is to create a package with a variable to hold the predicate and let the user execute a PL/SQL code segment to assign the value to the variable. Inside the policy function, you will be able to ...