• Login
• Become a member
• RSS
• Part of the TechTarget network

The Oracle Virtual Private Database (VPD) is a security feature within Oracle Database that can promote multi-tenancy in Oracle Database, and therefore encourage Oracle database consolidation.

Oracle VPD enables data access controls at the row and column level of tables and views.  At first glance, the name VPD is a bit misleading.  After all, the Oracle database is already a multi-user database with each user unaware they are sharing the database with other users.  It would be more accurate to call this feature Virtual Private Schema Object (VPSO) as it virtualizes tables, views and synonyms within the database. But for the purpose of this article, we’ll call it a VPD.

Whereas Oracle’s user/role security privilege model grants access to schema objects, VPD enforces fine-grained access of schema object data through access policies. Once access policies are in place, VPD dynamically modifies SQL statements such as SELECT, UPDATE and INSERT by simply appending a WHERE condition to filter the results.  VPD is not only transparent to users; its security cannot be bypassed.  Best of all, it is a free option with the Oracle Database Enterprise Edition.

The following VPD use case will show how to store multiple companies' data within the same schema without them knowing about it.

Using the Oracle VPD

The ABC Gum Company has an opportunity to cash in on a number of startup companies looking to hit the market quickly using hosted business systems.   They decide to offer their Customer Information System (CIS) as a centrally hosted Internet application and data service.  For this cloud-based Software as a Service (SaaS) delivery model to be successful, they must engineer a solution that has no changes to their existing application code, provides data security, allows rapid provisioning and maximizes return on investment (ROI).

The team dismisses deploying separate databases for each client, realizing this strategy won’t meet all the requirements.  They consider sharing a single database.  One of their top DBAs suggests Oracle’s VPD to store multiple clients’ data within the same database schema.  He explains VPD would be transparent to the application, provide row-level data security, meet the agility needs of the sales team and be the most cost-effective deployment strategy.  He describes how clients will be unaware that they’re sharing the same schema objects.

He goes on to describe how the primary components of the design would be implemented (listing 1).  First, he explains that users would continue to connect to the database through the existing Web application by using client-specific connection pools as a proxy on behalf of the users.  An application context would be set when database sessions are established, effectively caching a client identifier attribute.  A security access policy, based on a policy function, would be applied to schema objects enforcing logical separation of client data.  The policy function would use the application context and a client identifying column to return a predicate, limiting row access to CIS tables, views and synonyms. 

Listing 1 ABC’s VPD Setup

-- Logon as the database VPD Administrator

-- Create an Application Context to hold the Client Identifier

create context client_ctx using client_ctx_pkg;

context CLIENT_CTX created.

-- Create a PL/SQL package to Set the Application Context

create or replace package client_ctx_pkg is procedure set_client_id; end;

/

create or replace package body client_ctx_pkg is procedure set_client_id as client_id number;

  begin

   select client_id into client_id

     from CIS.client where client_name = sys_context('userenv', 'session_user');     

   dbms_session.set_context('client_ctx', 'client_id', client_id);

   exception

      when no_data_found then null;

   end;

  end;

/

PACKAGE client_ctx_pkg compiled

PACKAGE BODY client_ctx_pkg compiled

-- Create a Logon Trigger to Run the Application Context PL/SQL Package

create trigger set_client_id_ctx_trg after logon on database

  begin

    client_ctx_pkg.set_client_id;

  end;

/

TRIGGER set_client_id_ctx_trg compiled

-- Create a PL/SQL Policy Function to Limit Data Access

create or replace function filter_client_data( schema_p in varchar2, table_p in varchar2)

  return varchar2

  as client_predicate varchar2 (100);

  begin

    client_predicate := 'client_id = sys_context(''client_ctx'', ''client_id'')';

    return client_predicate;

  end;

/

FUNCTION filter_client_data compiled

--  Create a Security Policy Based on the Policy Function and Apply it to the Customer Table

begin

  dbms_rls.add_policy (object_schema   => 'cis'

                      ,object_name     => 'customer'

                      ,policy_name     => 'client_policy'

                      ,function_schema => 'vpd_admin'

                      ,policy_function => 'filter_client_data'

                      );

end;

/

anonymous block completed

That’s it. The VPD would now dynamically add the predicate to each query, effectively filtering each client’s data (Listing 2).

 

Listing 2 ABC’s VPD in Action

-- 1. As a Big Bubble Company user, select your customers

select customer_name from CIS.customer;

CUSTOMER_NAME                 

------------------------------

The Gum Shop

Jaime’s Candy

Shannon’s Pharmacy

-- 2. As a Chew IT Enterprises user, select your customers

select customer_name from CIS.customer;

CUSTOMER_NAME                 

------------------------------

Sweet Tooth

Gum Emporium

-- Validate VPD is dynamically appending a predicate to each query upon execution 

explain plan

set statement_ID = 'XXX'

for select * from CIS.customer;

select * from table(dbms_xplan.display('PLAN_TABLE','XXX','TYPICAL'));

------------------------------------------------------------------------------

| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |          |     2 |    60 |     3   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| CUSTOMER |     2 |    60 |     3   (0)| 00:00:01 |

------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("CLIENT_ID"=TO_NUMBER(SYS_CONTEXT('client_ctx','client_id')))

 

Jeff McCormick is an architecture director at a major health service company and president of the Connecticut Oracle User Group. McCormick has worked in IT for more than 20 years as a data and infrastructure architect/administrator. He holds several Oracle, Microsoft and Sybase professional certificates. 

Join the conversationComment

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.

All Rights Reserved,Copyright 2003 - 2013, TechTarget