Oracle Label Security (OLS) works with many other security features in the database to meet fine-grained data access requirements. Virtual Private Database (VPD) works with OLS to protect sensitive data in columns.

Oracle Label Security consists of two parts: User clearance labels and data classification labels (row labels). The policy itself determines access rights to rows by comparing the user's clearance with data classification labels. In this example though, the OLS policy will not be applied to a table, and there is no additional column appended to the protected table, which would otherwise hold the row labels. But in order to initially create the policy, the name of the additional column can not be omitted. Since this policy will never be attached to a table, the default policy enforcement option can be set to 'NO_CONTROL', which minimizes any overhead caused by a truly active policy.

The same could be achieved using the following script:

Begin
SA_SYSDBA.CREATE_POLICY
policy_name => 'PROTECT_PII'
column_name => 'OLS_COLUMN',
default_options => 'NO_CONTROL');
END;
/

Labels consists of three components: Required Levels, and optional Compartments and Groups. In this example, the levels will be 'Confidential' and 'Sensitive', and the compartment will be 'PII'; no groups are used:

The same could be achieved using the following scripts:

 
 
BEGIN
  SA_COMPONENTS.CREATE_LEVEL (
  policy_name => 'PROTECT_PII',
  level_num => 1000,
  short_name => 'C',
  long_name => 'CONFIDENTIAL');
END;
/

BEGIN
 SA_COMPONENTS.CREATE_COMPARTMENT (
  policy_name => 'PROTECT_PII',
  comp_num => 100,
  short_name => 'PII',
  long_name => 'PERS_INFO');
END;
/

BEGIN
 SA_COMPONENTS.CREATE_LEVEL (
  policy_name => 'PROTECT_PII',
  level_num => 2000,
  short_name => 'S',
  long_name => 'SENSITIVE');
END;
/
 
 

Only the user 'SKing' will get a label which is equal to or dominates the 'S:PII' label, which will allow him to see all columns of the table which contains PII information; all other users ('LDoran' for example) have labels that do not dominate the 'S:PII' label; for them, the 'salary' column will be empty. In this Oracle-by-Example, an OLS policy is applied to non-database users.

The same could be achieved using the following scripts:

 
BEGIN
 SA_USER_ADMIN.SET_USER_LABELS (
  policy_name => 'PROTECT_PII',
  user_name => 'SKING',
  max_read_label => 'S:PII',
  max_write_label => 'S:PII',
  min_write_label => 'C',
  def_label => 'S:PII',
  row_label => 'S:PII');
END;
/ 

BEGIN
 SA_USER_ADMIN.SET_USER_LABELS (
  policy_name => 'PROTECT_PII',
  user_name => 'LDORAN',
  max_read_label => 'C',
  max_write_label => 'C',
  min_write_label => 'C',
  def_label => 'C',
  row_label => 'C');
END;
/
 
 

The VPD policy will do the following:

Get the numerical label tag from the user's current label
Get the numerical label tag from the 'S:PII' label
User label ≥ 'S:PII' → access to all rows in sensitive columns
User label < 'S:PII' → access to all rows, but sensitive PII column is blank
In this example, the VPD policy will be applied to the hr.EMPLOYEES table:

 BEGIN
 DBMS_RLS.ADD_POLICY(
 object_schema => 'HR',
 object_name => 'EMPLOYEES',
 policy_name => 'vpd_protect_pii',
 function_schema => 'LBACSYS',
 policy_function => 'f_protect_pii',
 statement_types => 'select',
 sec_relevant_cols => 'SALARY',
 sec_relevant_cols_opt => dbms_rls.ALL_ROWS,
 policy_type => dbms_rls.CONTEXT_SENSITIVE);
END;
/
 
 
 

Download the entire demo script from here.