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.