Oracle VPD in Action: Building Role-Based Row-Level Security from Scratch

Row-level security is one of those requirements that shows up in almost every enterprise database — Finance shouldn’t see HR’s salary data, HR shouldn’t see Finance’s, and no team should see another’s unless explicitly authorized.

Most teams solve this at the application layer, hard-coding WHERE clauses into every query. It works — until someone connects directly through SQL Developer or a reporting tool and bypasses the app entirely.

Oracle’s Virtual Private Database (VPD), also known as Fine-Grained Access Control (FGAC), solves this properly: it enforces row-level and column-level security inside the database engine itself, so it’s enforced everywhere, every time — no exceptions.

In this post, I’ll walk through how I implemented it, mapping database roles to departments on a sample emp_test table.

How VPD Works

1. Policy Association – A security policy is bound to a table or view using the DBMS_RLS (Row Level Security) package.
2. Dynamic Interception – Any SELECT, INSERT, UPDATE, or DELETE against the protected table is intercepted by Oracle.
3. Policy Evaluation – A PL/SQL policy function runs to determine the security rule for the current session.
4. Predicate Appending – The function returns a string (e.g. department = 'FINANCE'), which Oracle silently appends to the query.
5. Execution – The user only ever sees rows they are authorized for — completely transparent to the client application.

Key Benefits

• Transparent security — no custom query logic needed in the application
• Granular control — restrict by role, session attribute, application context, or even time of day
• Protection against direct access — enforced even via SQL*Plus, SQL Developer, or third-party reporting tools
• Reduced maintenance — no more duplicate views/tables per user segment

Step 1: Create the Test Table and Sample Data

CREATE TABLE emp_test (
  emp_id NUMBER,
  emp_name VARCHAR2(50),
  department VARCHAR2(50),
  salary NUMBER
);

INSERT INTO emp_test VALUES (1, 'Munish', 'IT', 85000);
INSERT INTO emp_test VALUES (2, 'Deepak', 'MAKT', 90000);
INSERT INTO emp_test VALUES (3, 'Sanjay', 'FINANCE', 75000);
INSERT INTO emp_test VALUES (4, 'Bikash', 'HR', 65000);
COMMIT;

Step 2: Create the Application Context

An application context securely stores session-specific attributes — such as the user’s mapped department — so the policy function can read them efficiently.

CREATE OR REPLACE PACKAGE emp_ctx_pkg AS
  PROCEDURE set_department;
END emp_ctx_pkg;
/

CREATE OR REPLACE PACKAGE BODY emp_ctx_pkg AS
  PROCEDURE set_department IS
    v_role VARCHAR2(50);
    v_dept VARCHAR2(50);
  BEGIN
    IF v_role = 'IT' THEN
      v_dept := 'IT';
    ELSIF v_role = 'MAKT' THEN
      v_dept := 'MAKT';
    ELSIF v_role = 'FINANCE' THEN
      v_dept := 'FINANCE';
    ELSIF v_role = 'HR' THEN
      v_dept := 'HR';
    ELSE
      v_dept := 'ALL'; -- Admin / Manager
    END IF;
    DBMS_SESSION.SET_CONTEXT('emp_security_ctx', 'user_dept', v_dept);
  END set_department;
END emp_ctx_pkg;
/

CREATE CONTEXT emp_security_ctx USING emp_ctx_pkg;

Step 3: Create the Policy Function

The policy function returns the predicate (WHERE clause) that Oracle appends to any query against the protected table.

CREATE OR REPLACE PACKAGE emp_sec_fn_pkg AS
  FUNCTION f_security_predicate (
    schema_p IN VARCHAR2,
    table_p  IN VARCHAR2
  ) RETURN VARCHAR2;
END emp_sec_fn_pkg;
/

CREATE OR REPLACE PACKAGE BODY emp_sec_fn_pkg AS
  FUNCTION f_security_predicate (
    schema_p IN VARCHAR2,
    table_p  IN VARCHAR2
  ) RETURN VARCHAR2 IS
    v_user_dept VARCHAR2(50);
    v_predicate VARCHAR2(4000);
  BEGIN
    v_user_dept := SYS_CONTEXT('emp_security_ctx', 'user_dept');

    IF v_user_dept = 'ALL' THEN
      RETURN ''; -- No predicate added (returns all rows)
    END IF;

    v_predicate := 'department = ''' || v_user_dept || '''';
    RETURN v_predicate;
  END f_security_predicate;
END emp_sec_fn_pkg;
/

Step 4: Add the VPD Policy Using DBMS_RLS

BEGIN
  DBMS_RLS.ADD_POLICY (
    object_schema   => 'SYSTEM',
    object_name     => 'emp_test',
    policy_name     => 'emp_dept_policy',
    function_schema => 'SYSTEM',
    policy_function => 'emp_sec_fn_pkg.f_security_predicate',
    statement_types => 'SELECT, UPDATE, DELETE'
  );
END;
/

BEGIN
  DBMS_RLS.ENABLE_POLICY(
    object_schema => 'SYSTEM',
    object_name   => 'emp_test',
    policy_name   => 'emp_dept_policy',
    enable        => TRUE
  );
END;
/

Key parameters:

• statement_types — which DML operations trigger the policy
• update_check — when TRUE, Oracle validates that inserted/updated rows still satisfy the policy predicate, preventing a user from writing a row that violates their own security rule

Step 5: Test the Implementation

-- Create Database Roles
CREATE ROLE it;
CREATE ROLE makt;
CREATE ROLE finance;
CREATE ROLE hr;

-- Grant Privileges
GRANT connect, resource TO it;
GRANT connect, resource TO makt;
GRANT connect, resource TO hr;
GRANT connect, resource TO finance;

GRANT SELECT, INSERT, UPDATE ON system.emp_test TO finance;
GRANT SELECT, INSERT, UPDATE ON system.emp_test TO makt;
GRANT SELECT, INSERT, UPDATE ON system.emp_test TO hr;
GRANT SELECT, INSERT, UPDATE ON system.emp_test TO it;

-- Create test database users
CREATE USER finance_user IDENTIFIED BY password123;
CREATE USER it_user IDENTIFIED BY password123;
CREATE USER makt_user IDENTIFIED BY password123;
CREATE USER hr_user IDENTIFIED BY password123;

-- Grant roles to users
GRANT finance TO finance_user;
GRANT it TO it_user;
GRANT makt TO makt_user;
GRANT hr TO hr_user;

-- Allow every role to execute the context package
CREATE OR REPLACE PUBLIC SYNONYM emp_ctx_pkg FOR SYSTEM.emp_ctx_pkg;
GRANT EXECUTE ON emp_ctx_pkg TO it;
GRANT EXECUTE ON emp_ctx_pkg TO makt;
GRANT EXECUTE ON emp_ctx_pkg TO finance;
GRANT EXECUTE ON emp_ctx_pkg TO hr;

Once each user logs in and their session context is initialized, querying emp_test transparently returns only the rows belonging to their department — with zero application-level filtering.

Closing Thoughts

VPD is one of the most underused security features in Oracle — it moves access control out of fragile application code and into the database itself, where it can’t be bypassed. For any organization handling multi-department or multi-tenant data, it’s worth serious consideration.

Screenshots of the role grants, user creation, and query verification are included in the original documentation — feel free to reach out if you’d like the full write-up or a walkthrough.

Labels/Tags: Oracle, VPD, Fine-Grained Access Control, Row Level Security, PL/SQL, DBMS_RLS, Database Security, Oracle DBA