Oracle 8i | Oracle 9i | Oracle 10g | Oracle 11g | Oracle 12c | Miscellaneous | PL/SQL | SQL | Oracle RAC | Oracle Apps | Linux

Virtual Private Databases (VPD) and Fine-Grained Access Control

Virtual Private Databases (VPD) allow multiple users to access a single schema whilst preventing them from accessing data that is not relevant to them. Although this type of access can be controlled by the application, access via other methods (SQL*Plus) would leave the data open to abuse. VPD uses Fine-Grained Access Control to limit which data is visible to specific users. Setting up a VPD involves the following steps.

Related articles.

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 schemaowner IDENTIFIED BY schemaowner
DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;
GRANT connect, resource TO schemaowner;

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 schemaowner/schemaowner@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 schemaowner;

CONNECT schemaowner/schemaowner@service;

CREATE CONTEXT SCHEMAOWNER USING SCHEMAOWNER.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('SCHEMAOWNER','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('SCHEMAOWNER','USER_ID', v_id);
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        DBMS_SESSION.set_context('SCHEMAOWNER','USER_ID', 0);
    END;
    
    DBMS_SESSION.set_context('SCHEMAOWNER','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 SCHEMAOWNER.context_package TO PUBLIC;
CREATE PUBLIC SYNONYM context_package FOR SCHEMAOWNER.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 SCHEMAOWNER.set_security_context
AFTER LOGON ON DATABASE
BEGIN
  SCHEMAOWNER.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 schemaowner/schemaowner@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') = 'SCHEMAOWNER') THEN
      predicate := NULL;
    ELSE 
      predicate := 'USER_ID = SYS_CONTEXT(''SCHEMAOWNER'',''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') = 'SCHEMAOWNER') THEN
      predicate := NULL;
    ELSE 
      predicate := 'USER_ID = SYS_CONTEXT(''SCHEMAOWNER'',''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 SCHEMAOWNER.security_package TO PUBLIC;
CREATE PUBLIC SYNONYM security_package FOR SCHEMAOWNER.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('SCHEMAOWNER', 'USER_DATA', 'USER_DATA_INSERT_POLICY', 
                      'SCHEMAOWNER', 'SECURITY_PACKAGE.USER_DATA_INSERT_SECURITY',
                      'INSERT', TRUE);
  DBMS_RLS.add_policy('SCHEMAOWNER', 'USER_DATA', 'USER_DATA_SELECT_POLICY',
                      'SCHEMAOWNER', 'SECURITY_PACKAGE.USER_DATA_SELECT_SECURITY',
                      'SELECT');
END;
/

Test VPD

Finally, test that the VPD is working correctly.

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

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

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

Notice that:

What Next

Once you're happy with the basic mechanism you can extend the Security_Package to cover all tables where restricted access is neccessary, remembering to apply all security policies to the relevant tables.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.