DEV Community

Pranav Bakare
Pranav Bakare

Posted on

Virtual Private Database (VPD) | DBMS_RLS | fine-grained access control (FGAC) | mrcaption49

Virtual Private Database (VPD) | DBMS_RLS | fine-grained access control (FGAC) | mrcaption49

We use Virtual Private Database (VPD) to enforce dynamic, row-level security directly within the database, ensuring that users access only the data they are authorized to see.
This approach centralizes access control, eliminates hardcoded filters in application logic, and securely isolates data based on session-specific context.


🔐 Fine-Grained Access Control in Oracle Using VPD and DBMS_RLS- Project Overview

In our project, we implemented Virtual Private Database (VPD) to enforce secure and dynamic fine-grained access control at the database level. This was achieved using Oracle's DBMS_RLS package, which allowed us to apply row-level security policies on specific tables without modifying the application logic. We began by defining a custom application context to hold session-based attributes that govern access. A corresponding policy function was created to return predicate conditions based on the current context, ensuring that only the relevant subset of data is accessible per session. This function was then attached to the target table using DBMS_RLS.ADD_POLICY, enabling the database to automatically append the necessary security filters to all queries at runtime. This approach ensures data isolation and compliance, while maintaining a centralized and transparent access control mechanism.


🔐Implementation Overview

This solution uses Oracle's Virtual Private Database (VPD) with DBMS_RLS to apply fine-grained access control (FGAC) on the employee49 table. A custom application context stores user-specific department IDs, set dynamically via a procedure. A policy function restricts access by appending a predicate (dept_id = SYS_CONTEXT(...)) to user queries. The policy is applied using DBMS_RLS.ADD_POLICY for SELECT operations. As a result, users only see rows for their department without altering the application logic, and no access is granted if the context isn't set—ensuring secure, row-level data isolation.

This setup is a secure and elegant way to enforce row-level security transparently, ensuring that users can only access data relevant to their department or role, and nothing else — all without modifying application queries or relying on front-end filtering.


🔐 Fine-Grained Access Control in Oracle Using VPD and DBMS_RLS A Step-by-Step Guide to Row-Level Security with Context-Based Access Filtering

🧠 Introduction

In today’s data-sensitive environments, securing data at the row level is critical. Oracle Database provides a powerful feature called Virtual Private Database (VPD) to enforce fine-grained access control (FGAC) on tables and views — ensuring users can only access data they're authorized to see, regardless of how they query it.


In this post, we'll break down:

  • What is DBMS_RLS
  • What is a Virtual Private Database (VPD)
  • How Fine-Grained Access Control works

A complete hands-on example using a table called employee49, with simulated HR users and dynamic access filtering using application context

📘 What Is DBMS_RLS?

DBMS_RLS is an Oracle-supplied package used to implement row-level security policies. It allows you to attach a security policy (predicate) to a table or view. Oracle will automatically append this predicate to all queries, transparently filtering rows based on conditions you define — usually through a function that references session-level context.


🏛 What Is VPD (Virtual Private Database)?

VPD is Oracle's implementation of row-level security. It enforces data access rules at the database level using:

  • Policies defined via DBMS_RLS
  • Predicate functions that return WHERE conditions
  • Application context for user/session-level filtering

VPD ensures that data protection is enforced regardless of how data is queried, whether directly through SQL, via a report, or from an application.


🔍 What Is Fine-Grained Access Control (FGAC)?

FGAC lets you define conditions that limit access to specific rows in a table, usually based on who the user is, their role, department, or other metadata.

It gives administrators the ability to enforce dynamic filtering — different users can see different slices of the same data set, all controlled by the context set in their session.

🛠 Practical Walkthrough:

Securing employee49 Table Using VPD
Let’s go through an end-to-end implementation. We’ll create an employee49 table, simulate 3 HR users, and restrict their access to data based on their department using context-driven RLS.

✅ Step 1: Create the Table

CREATE TABLE employee49 (
  emp_id     NUMBER PRIMARY KEY,
  emp_name   VARCHAR2(100),
  dept_id    NUMBER,
  salary     NUMBER
);
Enter fullscreen mode Exit fullscreen mode

📥 Step 2: Insert Sample Data

INSERT INTO employee49 VALUES (1, 'Alice',   10, 50000);  -- Dept 10
INSERT INTO employee49 VALUES (2, 'Bob',     10, 55000);  -- Dept 10
INSERT INTO employee49 VALUES (3, 'Charlie', 20, 60000);  -- Dept 20
INSERT INTO employee49 VALUES (4, 'David',    5, 45000);  -- Dept 5
INSERT INTO employee49 VALUES (5, 'Eva',      5, 47000);  -- Dept 5

COMMIT;
Enter fullscreen mode Exit fullscreen mode

🧠 Step 3: Create Application Context

This context stores session-level info like department ID.

CREATE OR REPLACE CONTEXT emp_ctx USING set_emp_ctx;
Enter fullscreen mode Exit fullscreen mode

🛠 Step 4: Create Procedure to Set the Context

This simulates login behavior — sets the user’s department in the context.

CREATE OR REPLACE PROCEDURE set_emp_ctx(p_user IN VARCHAR2) IS
BEGIN
  IF p_user = 'HR1' THEN
    DBMS_SESSION.set_context('emp_ctx', 'dept_id', 10);
  ELSIF p_user = 'HR2' THEN
    DBMS_SESSION.set_context('emp_ctx', 'dept_id', 20);
  ELSIF p_user = 'HR3' THEN
    DBMS_SESSION.set_context('emp_ctx', 'dept_id', 5);
  ELSE
    DBMS_SESSION.set_context('emp_ctx', 'dept_id', NULL);
  END IF;
END;
Enter fullscreen mode Exit fullscreen mode

🧩 Step 5: Create the RLS Policy Function

This function returns a predicate (a WHERE clause) to restrict access.

CREATE OR REPLACE FUNCTION emp49_dept_policy (
  schema_name  IN VARCHAR2,
  table_name   IN VARCHAR2
) RETURN VARCHAR2 IS
BEGIN
  RETURN 'dept_id = SYS_CONTEXT(''emp_ctx'', ''dept_id'')';
END;
Enter fullscreen mode Exit fullscreen mode

🔐 Step 6: Add the Policy Using DBMS_RLS

This attaches the policy function to the employee49 table for SELECTs.

BEGIN
  DBMS_RLS.ADD_POLICY(
    object_schema   => 'CGO_OWNR',
    object_name     => 'employee49',
    policy_name     => 'emp49_dept_rls',
    policy_function => 'emp49_dept_policy',
    statement_types => 'SELECT'
  );
END;
Enter fullscreen mode Exit fullscreen mode

🔎 Step 7: Test Access Scenarios

🚫 Case 1: No Context Set

SELECT * FROM employee49;
🔴 Returns: No rows
Enter fullscreen mode Exit fullscreen mode

👉 Because the dept_id = SYS_CONTEXT(...) becomes dept_id = NULL, and no row matches that.


✅ Case 2: Context Set for HR3 (dept_id = 5)

BEGIN
  set_emp_ctx('HR3');
END;
/

SELECT * FROM employee49;
 Returns rows with dept_id = 5:
Enter fullscreen mode Exit fullscreen mode
emp_id  emp_name    dept_id salary
4   David   5   45000
5   Eva 5   47000
Enter fullscreen mode Exit fullscreen mode

✅ Case 3: Context Set for HR1 (dept_id = 10)

BEGIN
  set_emp_ctx('HR1');
END;
/

SELECT * FROM employee49;
Enter fullscreen mode Exit fullscreen mode

✅ Returns rows with dept_id = 10:

emp_id  emp_name    dept_id salary
1   Alice   10  50000
2   Bob 10  55000
Enter fullscreen mode Exit fullscreen mode

🔍 Behind the Scenes: How It Works
Oracle appends the following WHERE clause dynamically to every

SELECT query on employee49:


WHERE dept_id = SYS_CONTEXT('emp_ctx', 'dept_id')
Enter fullscreen mode Exit fullscreen mode

If dept_id = 10 is set in the session context, Oracle rewrites the query like:

SELECT * FROM employee49 WHERE dept_id = 10;
Enter fullscreen mode Exit fullscreen mode

This guarantees that only rows matching the user’s department are visible.


✅ Summary of Behavior

User    Context (dept_id)   Query Output
HR1 10  Only dept 10 employees
HR2 20  Only dept 20 employees
HR3 5   Only dept 5 employees
Other   NULL    ❌ No rows returned
Enter fullscreen mode Exit fullscreen mode

🧹 Optional: Removing the Policy

If needed, you can remove the policy with:

BEGIN
  DBMS_RLS.DROP_POLICY(
    object_schema => 'CGO_OWNR',
    object_name   => 'employee49',
    policy_name   => 'emp49_dept_rls'
  );
END;
Enter fullscreen mode Exit fullscreen mode

🎯 Final Takeaway

Oracle’s Virtual Private Database with DBMS_RLS and context-based filtering is a robust and elegant way to implement fine-grained access control. It ensures that data security is enforced at the database level, not just the application layer — and dynamically adapts to user/session identity.

Top comments (0)