Using Oracle Virtual Private Database to Control Data Access

Oracle Virtual Private Database (VPD) enables you to create security policies to control database access at the row and column level. Essentially, Oracle Virtual Private Database adds a dynamic WHERE clause to a SQL statement that is issued against the table, view, or synonym to which an Oracle Virtual Private Database security policy was applied.

Oracle Virtual Private Database enforces security, to a fine level of granularity, directly on database tables, views, or synonyms. Because you attach security policies directly to these database objects, and the policies are 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 that is protected with an Oracle Virtual Private Database policy, Oracle Database dynamically modifies the SQL statement of the user. This modification creates a WHERE condition (called a predicate) returned by a function implementing the security policy. Oracle Database modifies the statement dynamically, transparently to the user, using any condition that can be expressed in or returned by a function. You can apply Oracle Virtual Private Database policies to SELECT, INSERT, UPDATE, INDEX, and DELETE statements.

For example, suppose a user performs the following query:

SELECT * FROM HR.BILLS;

The Oracle Virtual Private Database policy dynamically appends the statement with a WHERE clause. For example:

SELECT * FROM HR.BILLS
WHERE SALES_REP_ID = 159;

In this example, the user can only view orders by Sales Representative 159.

If you want to filter the user based on the session information of that user, such as the ID of the user, then you can create the WHERE clause to use an application context. For example:

SELECT * FROM HR.BILLS
WHERE SALES_REP_ID = SYS_CONTEXT (‘USERENV’,’SESSION_USER’);

Benefits of Using Oracle Virtual Private Database Policies

Oracle Virtual Private Database policies provide the following benefits:

Basing Security Policies on Database Objects Rather Than Applications

Attaching Oracle Virtual Private Database security policies to database tables, views, or synonyms, rather than implementing access controls in all your applications, provides the following benefits:

Security. Associating a policy with a database table, view, or synonym can solve a potentially serious application security problem. 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 applications.

Flexibility. You can have one security policy for SELECT statements, another for INSERT statements, and still others for UPDATE and DELETE For example, you might want to enable Human Resources clerks to have SELECT privileges for all employee records in their division, but to update only salaries for those employees in their division whose last names begin with A through F. Furthermore, you can create multiple policies for each table, view, or synonym.

Controlling How Oracle Database Evaluates Policy Functions

Running policy functions multiple times can affect performance. You can control the performance of policy functions by configuring how Oracle Database caches the Oracle Virtual Private Database predicates. The following options are available:

Evaluate the policy once for each query (static policies).

Evaluate the policy only when an application context within the policy function changes (context-sensitive policies).

Evaluate the policy each time it is run (dynamic policies).

Setup Test Environment

First we must create a user to act as the schema owner for this example. Obviously, you will perform the following tasks using your current schema owner.

CONNECT sys/password@service AS SYSDBA;
CREATE USER logix IDENTIFIED BY logix
DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;
GRANT connect, resource TO logix;

CREATE USER user1 IDENTIFIED BY user1
DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;
GRANT connect, resource TO user1;

CREATE USER user2 IDENTIFIED BY user2
DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;
GRANT connect, resource TO user2;

GRANT EXECUTE ON DBMS_RLS TO PUBLIC;

CONN logix/logix@service

CREATE TABLE users
(id NUMBER(10) NOT NULL,
ouser VARCHAR2(30) NOT NULL,
first_name VARCHAR2(50) NOT NULL,
last_name VARCHAR2(50) NOT NULL);

CREATE TABLE user_data
(column1 VARCHAR2(50) NOT NULL,
user_id NUMBER(10) NOT NULL);

INSERT INTO users VALUES (1,’USER1′,’User’,’One’);
INSERT INTO users VALUES (2,’USER2′,’User’,’Two’);
COMMIT;

GRANT SELECT, INSERT ON user_data TO user1, user2;

Create an Application Context

Grant CREATE ANY CONTEXT to the schema owner then create the context and context package.

CONNECT sys/password@service AS SYSDBA;
GRANT create any context, create public synonym TO logix;

CONNECT logix/logix@service;

CREATE CONTEXT LOGIX USING LOGIX.context_package;

CREATE OR REPLACE PACKAGE context_package AS
PROCEDURE set_context;
END;
/

Next we create the context_package body which will actually set the user context.

CREATE OR REPLACE PACKAGE BODY context_package IS
PROCEDURE set_context IS
v_ouser VARCHAR2(30);
v_id NUMBER;
BEGIN
DBMS_SESSION.set_context(‘LOGIX’,’SETUP’,’TRUE’);
v_ouser := SYS_CONTEXT(‘USERENV’,’SESSION_USER’);

BEGIN
SELECT id
INTO v_id
FROM users
WHERE ouser = v_ouser;

DBMS_SESSION.set_context(‘LOGIX’,’USER_ID’, v_id);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_SESSION.set_context(‘LOGIX’,’USER_ID’, 0);
END;

DBMS_SESSION.set_context(‘LOGIX’,’SETUP’,’FALSE’);
END set_context;
END context_package;
/
SHOW ERRORS

Next we make sure that all users have access to the Context_Package.

GRANT EXECUTE ON LOGIX.context_package TO PUBLIC;
CREATE PUBLIC SYNONYM context_package FOR LOGIX.context_package;

Create Login Trigger

Next we must create a trigger to fire after the user logs onto the database.

CONNECT sys/password@service AS SYSDBA;
CREATE OR REPLACE TRIGGER LOGIX.set_security_context
AFTER LOGON ON DATABASE
BEGIN
LOGIX.context_package.set_context;
END;
/
SHOW ERRORS

Create Security Policies

In order for the context package to have any effect on the users interaction with the database, we need to define a security_package for use with the security policy. This package will tell the database how to treat any interactions with the specified table.

CONNECT logix/logix@service;

CREATE OR REPLACE PACKAGE security_package AS
FUNCTION user_data_insert_security(owner VARCHAR2, objname VARCHAR2)
RETURN VARCHAR2;

FUNCTION user_data_select_security(owner VARCHAR2, objname VARCHAR2)
RETURN VARCHAR2;
END security_package;
/

Next we create the security_package body.

CREATE OR REPLACE PACKAGE BODY Security_Package IS
FUNCTION user_data_select_security(owner VARCHAR2, objname VARCHAR2) RETURN VARCHAR2 IS
predicate VARCHAR2(2000);
BEGIN
predicate := ‘1=2’;
IF (SYS_CONTEXT(‘USERENV’,’SESSION_USER’) = ‘LOGIX’) THEN
predicate := NULL;
ELSE
predicate := ‘USER_ID = SYS_CONTEXT(”LOGIX”,”USER_ID”)’;
END IF;
RETURN predicate;
END user_data_select_security;

FUNCTION user_data_insert_security(owner VARCHAR2, objname VARCHAR2) RETURN VARCHAR2 IS
predicate VARCHAR2(2000);
BEGIN
predicate := ‘1=2’;
IF (SYS_CONTEXT(‘USERENV’,’SESSION_USER’) = ‘LOGIX’) THEN
predicate := NULL;
ELSE
predicate := ‘USER_ID = SYS_CONTEXT(”LOGIX”,”USER_ID”)’;
END IF;
RETURN Predicate;
END user_data_insert_security;
END security_package;
/
SHOW ERRORS

Next we make sure that all users have access to the Security_Package.

GRANT EXECUTE ON LOGIX.security_package TO PUBLIC;
CREATE PUBLIC SYNONYM security_package FOR LOGIX.security_package;

Apply Security Policies to Tables

The DBMS_RlS package is used to apply the security policay, implemented by security_package, to the the relevant tables.

BEGIN
DBMS_RLS.add_policy(‘LOGIX’, ‘USER_DATA’, ‘USER_DATA_INSERT_POLICY’,
‘LOGIX’, ‘SECURITY_PACKAGE.USER_DATA_INSERT_SECURITY’,
‘INSERT’, TRUE);
DBMS_RLS.add_policy(‘LOGIX’, ‘USER_DATA’, ‘USER_DATA_SELECT_POLICY’,
‘LOGIX’, ‘SECURITY_PACKAGE.USER_DATA_SELECT_SECURITY’,
‘SELECT’);
END;
/

Test VPD

Finally, test that the VPD is working correctly

CONNECT user1/user1@service;
INSERT INTO logix.user_data (column1, user_id) VALUES (‘User 1’, 1);
INSERT INTO logix.user_data (column1, user_id) VALUES (‘User 2’, 2);
COMMIT;

CONNECT user2/user2@service
INSERT INTO logix.user_data (column1, user_id) VALUES (‘User 1’, 1);
INSERT INTO logix.user_data (column1, user_id) VALUES (‘User 2’, 2);
COMMIT;

CONNECT logix/logix@service
SELECT * FROM logix.user_data;
CONNECT user1/user1@Service;
SELECT * FROM logix.user_data;
CONNECT user2/user2@Service
SELECT * FROM logix.user_data;

Notice that:

When connected to USER1, only the first insert will work.
When connected to USER2, only the second insert will work.
The failing inserts produce the following error.

ORA-28115: policy with check option violation

Once the inserts are finished, there will be two rows in the table, as seen when connected as LOGIX. When connected as USER1 or USER2, only the single row they inserted will be visible.