Securing sensitive data with Oracle VPD

What is VPD?

Virtual Private Database (VPD) is a database security feature that is built into an Oracle database server, as opposed to being part of an application that is accessing the data. The user is only allowed to see the data they have been given permission to see. In VPD, security policies are attached directly to tables, views, and synonyms and hence there is no way for users to bypass the security settings. Any SQL used to access a table, view or synonym protected by VPD policy is dynamically modified to include a limiting condition such as where clause or and clause. The modification occurs transparently, and the user sees only the data that passes the limiting conditions in the clause. The fine-grained access allows a great deal of control over the access to application tables. VPD policies can be applied to select, insert, update, index and delete commands. Different security policies for each type of access, one for selects, another for inserts and so on can be created.

Why VPD?

• Scalability:

  • Let’s say a table Customers contains 1000 customer records. Suppose we want customers to access their own records but not other customers. If we use views to achieve this functionality, we need to create 1000 views. Using VPD, it can be done with a single policy function.

• Simplicity:

  • Say, we have a table T and many views are based on T. Suppose we want to restrict access to some information in T. Without VPD, all view definitions have to be changed. Using VPD, it can be done by attaching a policy function to T; as the policy is enforced in T, the policy is also enforced for all the views that are based on T.

• Security:

  • Server enforced security (as opposed to application enforced)
  • Cannot be bypassed

How does VPD work?

A VPD consists of:

1. A VPD Policy function – this returns the predicate to be applied at run time.
2. A VPD Policy – Name of the VPD Policy.

When a user accesses a table (or view or synonym) which is protected by a VPD policy (function):

1. The Oracle server invokes the policy function.
2. The policy function returns a predicate based on session attributes or database contents.
3. The server dynamically rewrites the submitted query by appending the returned predicate to the WHERE clause.
4. The modified SQL query is executed.

Now, let’s look at a couple of examples of using VPD to secure data

Securing HR data:

Example 1: Row Level VPD:

Let’s say we want to secure the HR table per_phones so that no database user except APPS can see the data.
Step 0: Create a non APPS user

create user vpd_test identified by vpd_test;
grant connect, create session to vpd_Test;
grant select on hr.per_phones to vpd_test;
grant select on hr.per_all_people_f to vpd_test;

Step 1: Create a policy function that will return the predicate that is dynamically used when querying the table.

create or replace function
xxen_vpd_per_phones_fnc (p_schema varchar2, p_obj varchar2)
return varchar2 as
l_user varchar2(20);
l_predicate varchar2(100);
begin
  select user into l_user from dual;
 
  if l_user!='APPS' then 
    l_predicate:='1=2';
  end if;
 
  return l_predicate;
end xxen_vpd_per_phones_fnc;

Step 2: Create the VPD Policy

begin
  dbms_rls.add_policy
    (object_schema=>'HR'  -- specify the schema containing the object 
    ,object_name=>'PER_PHONES'  -- specify the object name within the schema.
    ,policy_name=>'xxen_vpd_per_phones_plc'  -- specify the policy name. Policy name is unique for an object. 
    ,function_schema=>'APPS'  -- specify the schema in which the policy function is created
    ,policy_function=>'xxen_vpd_per_phones_fnc'  -- specify the name of the policy function
    ,statement_Types=>'SELECT'  -- Operations when this policy applies. INSERT, SELECT, DELETE, UPDATE
    );
end;

Step 3: Query the table “PER_PHONES” from “APPS” User:

We can see that the rows are returned when the table is queried from APPS user.

Step 4: Query the table “PER_PHONES” from VPD_TEST user.

Connect to the database as vpd_test user and query the table

The query returns no rows because of the VPD Policy applied on the table.

Example 2: Column Level VPD

Let’s say we want to secure some of the columns data for HR table “PER_ALL_PEOPLE_F”. Again, let’s consider we want only APPS user to see the data and not the others. We hide the following two columns:

1. DATE_OF_BIRTH
2. NATIONAL_IDENTIFIER

The other columns data apart from the one mentioned above is available to all the users.

Step 1: Create the VPD Policy function.

create or replace function 
xxen_vpd_per_people_fnc (p_schema varchar2, p_obj varchar2) 
return varchar2 as
l_user  varchar2(20);
l_predicate varchar2(100);
begin
  select user into l_user from dual;
 
  if l_user!='APPS' then 
    l_predicate:='1=2';
  end if;
 
  return l_predicate;
end xxen_vpd_per_people_fnc;

Step 2: Create the VPD Policy.

begin
  dbms_rls.add_policy
    (object_schema=>'HR'  -- specify the schema containing the object 
    ,object_name=>'PER_ALL_PEOPLE_F'  -- specify the object name within the schema.
    ,policy_name=>'xxen_vpd_per_people_plc'  -- specify the policy name. Policy name is unique for an object. 
    ,function_schema=>'APPS'  -- specify the schema in which the policy function is created
    ,policy_function=>'xxen_vpd_per_people_fnc'  -- specify the name of the policy function
    ,statement_Types=>'SELECT'  -- Operations when this policy applies. SELECT
    ,sec_relevant_cols=>'DATE_OF_BIRTH,NATIONAL_IDENTIFIER'  -- ALL relevant columns to be hidden from users
    ,sec_relevant_cols_opt	=> dbms_rls.ALL_ROWS
    );
end;

Step 3: Query the table PER_ALL_PEOPLE_F from APPS User.

We can see that all the rows are returned and all the information is displayed well.

Step 4: Query the table “PER_ALL_PEOPLE_F” with “VPD_TEST” user

We can see that all the rows are fetched. However, the columns date_of_birth and national_identifier have NULL values displayed in them.

This setting is especially useful when we want developers to have access to data in order to help them write queries, and at the same time, hide sensitive information from them.

Securing data while using Blitz Report

In the above examples, we saw how to hide data if the user is not APPS.

Blitz Report however, runs as a background concurrent process using the APPS database user. To protect data from being displayed in Blitz Reports output files, but having it accessible for Oracle standard functionality, we need to modify the policy function to make use of application context or system contexts to detect if the current session is a Blitz Report or other process.

Blitz Report includes setup and concurrent programs to automatically create VPD policies and database functions that protect sensitive table row or column data when running Blitz Reports.

What is an Application Context?

An application context is a set of name-value pairs that Oracle Database stores in memory. The application context has a label called a namespace, for example, empno_ctx for an application context that retrieves employee IDs. An application can use the application context to access session information about a user, such as a user ID or other user-specific information, or a client ID, and then securely pass this data to the database. We can then use this information to either permit or prevent the user from accessing data through the application. We can use application contexts to authenticate both database and non-database users.

To retrieve the user session information, we can use the SYS_CONTEXT SQL function. The SYS_CONTEXT function returns the value of the parameter associated with the context namespace. We can use this function in both SQL and PL/SQL statements. Typically, we will use the built-in USERENV namespace to retrieve the session information of a user.

Now, having said the above and in order to secure data while using Blitz Report, we use sys_context check while defining the policy function.

The modified function looks like below:

create or replace function 
xxen_vpd_per_people_fnc (p_schema varchar2, p_obj varchar2) 
return varchar2 as
l_predicate varchar2(100);
begin
  if sys_context ('userenv','module') like 'XXEN_REPORT - %' then
    l_predicate:='1=2';
  end if;
 
  return l_predicate;
end xxen_vpd_per_people_fnc;

When we query the table using Blitz Report (before modifying the policy function to include sys_context), the query returns all the values.

The setup of the report is in the below image:

Sample output of the VPD_Test report before applying the VPD Policy function:

It can be seen that all the information is displayed correctly.

Now, the result of the same query after applying the VPD Policy:

We can see that the data in columns appear blank after the VPD Policy is applied to the table.

Point to Note: The above example only considers the application context to check if the table is queried from the Blitz Report. It does not differentiate between users who are running the report. If we would like to provide only a few selected users access to the data and not others (for e.g. people in the HR department should have access to data while developers do not need access), we can do so by adding another condition in the VPD policy function to check for a suitable condition. Examples of such conditions can be

• The responsibility through which Blitz Report is being accessed
• A profile option setting for users
• Or any custom defined setting that separates users (or) roles (or) hierarchies

Since the first condition checked is for the application context of whether the report is run from / table is queried from the Blitz Report, it does not affect any database users who have access to the tables. If we would like to secure data while being queried from the database by a database user, create another policy function that checks for the database user and add this policy function to the VPD Policy created for that table. Multiple functions can be applied to a single policy.

Oracle provides many subprograms within the DBMS_RLS package for working with VPD. A subset of the programs is given below:

Which data to secure?

How to know which data in the organization to secure?

This whitepaper lists a few data elements that might be considered sensitive and hence access to them should be secured and restricted. There are other sources on the internet that list Personally Identifiable Information (PII) and debate on whether the data needs to be safeguarded. An example can be found here.

We compiled a list of elements that we believe might need to be secured, in the table below. Please note that this is not a comprehensive list and we will add to this list as and when we come across a scenario where additional data elements need to be secured as well.

Additional information and useful links

This oracle article explains the different types of sensitive data and their life cycle and how they should be secured.

This YouTube video helps to understand data masking if you need to mask your data.

Oracle whitepaper discussing the challenges in data privacy and possible solutions.

Oracle Database Security Guide as a very good starting point to learn about and how to use VPD.

Application contexts and how they can be used.

Oracle note 2542049.1 with an example on how to use VPD with E-Business Suite data.

Blitz Report user guide VPD section.