Null Application Context Values and Recursive Lookups - Oracle

There are two areas that consistently trap newcomers to VPD. The problem occurs when an RLS policy function references an application context that is null. The reason the application context is null is because the namespace manager is trying to set the context on the table to which the RLS policy is being applied.

This example begins by illustrating the working context. Recall the user’s department number is set by a logon trigger. The trigger calls the namespace manager, which sets the value by querying the LOOKUP_DEPT table:

sec_mgr@KNOX10g> CONN scott/tiger
Connected.
scott@KNOX10g> -- Context value is set on logon
scott@KNOX10g> COL deptno format a6 scott@KNOX10g> SELECT SYS_CONTEXT ('people_ctx',
  2                      'deptno') deptno
  3   FROM DUAL;
DEPTNO
------
20
scott@KNOX10g> CONN sec_mgr/oracle10g Connected.
sec_mgr@KNOX10g> -- Current view on table sec_mgr@KNOX10g> SELECT * FROM lookup_dept
  2   WHERE username = 'SCOTT';
USERNAME      DEPTNO
---------- -----------
SCOTT             20

Normally, the lookup table would not exist. It was created specifically to bypass this problem. Nevertheless, the mistake comes next when an RLS policy is added to the LOOKUP_DEPT table. To help with this example, an update policy will be added to the EMP table using the same policy function. Most often the context will be set from the same table the RLS policy will be enforced from which, as you will see, can’t be done:

sec_mgr@KNOX10g> -- Add Dept level policy to lookup table.
sec_mgr@KNOX10g> -- Policy references people_ctx context.
sec_mgr@KNOX10g> BEGIN
  2    DBMS_RLS.add_policy
  3          (object_schema   => SEC_MGR',
  4           object_name     => 'lookup_
   dept',   
  5           policy_name     => 'lookup_
   dept_sel',
  6           policy_function => 
   'dept_only',
  7           statement_types => 'SELECT');
  8  END;
  9  /
PL/SQL procedure successfully completed.
sec_mgr@KNOX10g> -- Add Dept level policy to EMP table.
sec_mgr@KNOX10g> -- Policy references people_ctx context.
sec_mgr@KNOX10g> BEGIN
  2    DBMS_RLS.add_policy
  3         (object_schema   => 'SCOTT',
  4          object_name     => 'EMP',
  5          policy_name     => 'EMP_UPD',
  6          policy_function => 'dept_
   only',  
  7          statement_types => 'UPDATE');
  8  END;
  9  /
PL/SQL procedure successfully completed.

Querying from the lookup table shows the expected results. If you are the security administrator, you may feel nothing is wrong:

sec_mgr@KNOX10g> -- rows are no longer visible
sec_mgr@KNOX10g> -- since SEC_MGR has not context
sec_mgr@KNOX10g> SELECT * FROM lookup_dept;
no rows selected

Connecting as the SCOTT user, you can see the application context wasn’t set. The ramifications of this are that any RLS policies that are based on this context will not behave properly:

sec_mgr@KNOX10g> CONN scott/tiger
Connected.
scott@KNOX10g> -- Context no longer exists scott@KNOX10g> COL deptno format A6 scott@KNOX10g> SELECT SYS_CONTEXT ('people_ctx',
  2                      'deptno') deptno
  3    FROM DUAL;
DEPTNO
------
scott@KNOX10g> -- Any RLS policies that used the context value will not
cott@KNOX10g> -- behave correctly. scott@KNOX10g> -- This update would normally succeed for all dept 20 records scott@KNOX10g> UPDATE emp
  2     SET ename = ename;
0 rows updated.

Recall the policy function being added restricts the user’s records to the department in which they work. The department number is stored in the context that is populated from the same table being restricted by the RLS policy. This recursive condition prevents the application context from being populated.

The solution to this is to create a different object to be used for populating the application context. The object can be a table or a view. A view is the best because there is no data synchronization that needs to occur. If you create a view, the application context lookup should occur on the table, and the RLS policy should be applied to the view. If you did this the other way, it wouldn’t work because the RLS on the base table would still be in effect when the table was accessed by way of the view.

Oracle introduced partitioned fine-grained access control in Oracle9i  Database, which allows you to group together multiple policies and then enable and disable them easily. This is a useful concept when multiple policies exist on the same objects but have conflicting return values.

Recall that Oracle logically ANDs all RLS policies. That is, if there are two policies, A and B, for the same object, then the resulting SQL will be the output of A and B. If policy A returns “deptno = 20” and policy B returns “deptno = 10”, then the resulting query will be “deptno = 20 and deptno = 10”. Because the deptno is either 10 or 20, this query results in zero rows.

Partitioned fine-grained access control allows you to define which RLS policies you want to enable for the user session. This is done by setting the policy name into an application context. The database will then reference this policy name by looking at the value stored in the user’s application context and applying that RLS policy.

Normally, I would include an example that illustrates some nuance of the technology. Fortunately, the Oracle Database Security Guide 10g has an excellent example of partitioned fine-grained access control. Including a different example here would be redundant.

Oracle Database 10g offers a new feature to VPD called Column Sensitive VPD. The objective of this feature is to invoke the security policy when a specific column is referenced.

Let’s augment the security on the PEOPLE view to allow users to see only their own salaries. You do still want the user to see other columns of other user records. You can use the same PL/SQL function, USER_ONLY, for this new policy. The added parameter is SEC_RELEVANT_COLS.

sec_mgr@KNOX10g> BEGIN
  2    DBMS_RLS.add_policy
  3     (object_schema => 'SCOTT',
  4      object_name   => 'PEOPLE',
  5      policy_name   => 'people_sel_sal',
  6      function_schema => 'SEC_MGR',
  7      policy_function => 'user_only',
  8      statement_types => 'SELECT',
  9      sec_relevant_cols => 'SALARY');
 10  END;
 11  /
PL/SQL procedure successfully completed.

Testing this code, you see a different result than you did with our view. When the SALARY column is queried, the VPD policy is invoked, and only one record is returned; when the SALARY column is not queried, then the policy isn’t invoked and all records are returned:

sec_mgr@KNOX10g> CONN scott/tiger
Connected.
scott@KNOX10g> -- User can see all records when SALARY column is not
scott@KNOX10g> -- queried. Show first five records only.
scott@KNOX10g> SELECT username FROM people
  2   WHERE ROWNUM <= 5;
USERNAME
----------
SMITH
ALLEN
WARD
JONES
MARTIN
scott@KNOX10g> -- Adding the salary column causes
scott@KNOX10g> -- the RLS policy to activate scott@KNOX10g> SELECT username, salary FROM people;
USERNAME       SALARY
--------- -----------
SCOTT            3000

To make VPD behave like the column masking view example you saw in Chapter 10 you can use another new parameter to the ADD_POLICY procedure: SEC_RELEVANT_COLS_OPT. Oracle’s implementation is consistent with what was done in the view. That is, the values for the sensitive columns are null values. All the other columns and rows are returned:

sec_mgr@KNOX10g> BEGIN
  2    -- Remove current policy
  3    DBMS_RLS.drop_policy
  4          (object_schema   => 'SCOTT',
  5           object_name     => 'PEOPLE',
  6           policy_name     =>
   'people_sel_sal');
  7    -- Add policy again but now add the
   SEC_RELEVANT_COLS_OPT
  8    DBMS_RLS.add_policy
  9         (object_schema    => 'SCOTT',
 10          object_name      => 'PEOPLE', 
 11          policy_name      =>
   'people_sel_sal',
 12          function_schema  => 'SEC_MGR',
 13          policy_function  =>
   'user_only',
 14          statement_types  => 'SELECT', 
 15          sec_relevant_cols => 'SALARY',
 16          sec_relevant_cols_opt =>
   DBMS_RLS.all_rows);
 17  END;
 18  /
PL/SQL procedure successfully completed.

The final query then results with null salary values for all but the invoking users:

scott@KNOX10g> -- all_rows added as sec_relevant_cols_opt
scott@KNOX10g> -- Just showing dept20 records for brevity.
scott@KNOX10g> SELECT username, salary FROM people
  2  WHERE deptno = 20;
USERNAME     SALARY
--------- ---------
SMITH
JONES
SCOTT          3000
ADAMS
FORD

NOTE

The SEC_RELEVANT_COLS_OPT is applicable only to select statements.

The column sensitive option is ideal for privacy requirements. The column may be anything sensitive that you want to conceal—salary, a credit card number, patient diagnosis, financial status, and so on. This option allows you to store the sensitive information and the nonsensitive information together with the assurance that anytime someone requests the sensitive data, RLS will remove or mask the values.