Oracle Virtual Private Database

Oracle Database provides the necessary tools to build secure applications. One such tool is Virtual Private Database (VPD), which is the combination of the following:

• Fine-grained access control, which enables you to associate security policies to database objects
• Application context, which enables you to define and access application or database session attributes.

VPD combines these two features, enabling you to enforce security policies to control access at the row level. This control is based on application or session attributes, which can be made available during execution.

Introduction

Virtual private database (VPD) enables you to enforce security, to a fine level of granularity, directly on tables, views, or synonyms. Because security policies are attached directly to tables, views, or synonyms and automatically applied whenever a user accesses data, there is no way to bypass security.

When a user directly or indirectly accesses a table, view, or synonym protected with a VPD policy, the server dynamically modifies the SQL statement of the user. The modification creates a WHERE condition (known as a predicate) returned by a function implementing the security policy. The statement is modified dynamically, transparently to the user, using any condition that can be expressed in or returned by a function.

VPD policies can be applied to SELECT, INSERT, UPDATE, INDEX, and DELETE statements.

Advantages:

Security

Associating a policy with a database table, view, or synonym can solve potentially serious application security Problems.

Suppose a user is authorized to use an application, and then drawing on the privileges associated with that application, wrongfully modifies the database by using an ad-hoc query tool such as sql*plus. By attaching security policies directly to tables, views, or synonyms, fine-grained access control ensures that the same security is in force, no matter how a user accesses the data.

Simplicity

You add the security policy to a table, view or synonym only once, rather than repeatedly adding it to each of your table-based, view-based, or synonym based application

Flexibility

You can have one security policy for SELECT statements, another for INSERT Statement, and still others for UPDATE and DELETE Statements.

Oracle VPD is provided at no cost with the Oracle Enterprise Edition.

Creating a VPD Policy with Oracle Policy Manager

To implement VPD, developers can use the DBMS_RLS package to apply security policies to tables and views. They can also use the CREATE CONTEXT command to create application contexts.

To create VPD policies, users must provide the schema name, table (or view or synonym) name, policy name, the function name that generates the predicate, and the statement types to which the policy applies (that is, SELECT, INSERT, UPDATE, DELETE). Oracle Policy Manager then executes the function DBMS_RLS.ADD_POLICY.

You can create an application context by providing the name of the context and the package that implements the context.

Row Level Security

RLS or VPD or FGAC is one of the features of the Oracle database with lots of different names. Commonly it’s referred to as Virtual Private Database (VPD) but it is implemented as Row Level Security (RLS) hence the names of the supplied package DBMS_RLS.

The marketing term for this feature was (or used to be) Fine Grained Access Control.

What RLS does is transparently add a predicate to every query issued against a table that has a policy defined on it. The package provides a couple of programs to (amongst others) add, remove, enable and disable policies. The actual implementation of the policy should be done in your own package.

Application Context

Application context enables you to define, set, and access variable-length application attributes and values in a secure data cache available in User Global Area (UGA) and System Global Area (SGA).

You configure application context by using the SQL function SYS_CONTEXT with the following syntax:

The following diagram shows a very simple example.

As you can see from the above diagram, the context sensitive access policy is as follows:
“An employee can access her records and her subordinates’ records if she is a manager”

Implementing the Policy

1. Create a Function to Generate the Dynamic WHERE Clause
2. Create a policy to attach the Function to the Objects you want to protect.

To understand how VPD works, let’s take a closer look at the “emp_sec” function below. Here we see that the emp_sec function returns a SQL predicate, in this case, “ENAME=xxxx,” in which XXXX is the current user (in Oracle, we can get a current user ID by calling the sys_context function). This predicate is appended to the WHERE clause of every SQL statement issued by the user when they reference the EMP table.

CREATE OR REPLACE FUNCTION  emp_sec (schema IN varchar2, tab IN varchar2)

RETURN VARCHAR2 ASBEGIN

BEGIN

RETURN   ‘ename=”’ || sys_context( ‘userenv’, ‘session_user’) ||  ””;

END emp_sec;

/

Once the function is created, we call the dbms_rls (row-level security) package. To create a VPD policy, we invoke the add_policy procedure, and below figure shows an example of the invocation of the add_policy procedure. Take a close look at this policy definition:

In this example, the policy dictates that:

1. Whenever the EMP table is referenced
2. In a SELECT query
3. A policy called EMP_POLICY will be invoked
4. Using the SECUSR schema’s “emp_sec” PL/SQL function.

Conclusion

By themselves, each Oracle security mechanism does an excellent job of controlling access to data.

The foremost benefit of VPD is that the database server automatically enforces these security policies, regardless of the how the data is accessed, through the use of variables that are dynamically defined within the database user’s session. The downsides to VPD security are that VPD security policies are required for every table accessed inside the schema, and the user still must have access to the table via traditional GRANT statements.

As Oracle security continues to evolve, we will no doubt see more technical advances in data control methods.

Blog contribution Isaignani C(Technical consultant)