Column Sensitive VPD

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.



Effective Oracle Database 10g Security by Design
Effective Oracle Database 10g Security by Design
ISBN: 0072231300
EAN: 2147483647
Year: 2003
Pages: 111

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net