DBMS_RLS | Oracle VPD | Row-Level Security in Oracle

🔐 Enforcing Row-Level Security in Oracle with DBMS_RLS (Oracle VPD)

In enterprise applications, it’s common that different users should only see specific rows of data based on their roles or permissions. For example, a user may only be allowed to view records related to their region, department, or assigned resources.

While many systems implement this logic in the application layer, Oracle provides a powerful database-level feature called Virtual Private Database (VPD). Using the DBMS_RLS package, Oracle can automatically enforce Row-Level Security (RLS) on tables.

This means the database itself ensures that users only see the data they are authorized to access.

In this article, we’ll walk through how to implement Row-Level Security using Oracle VPD with a policy function and DBMS_RLS.ADD_POLICY.

🧭 Use Case

Imagine we have a table called ORDERS_HDR that stores order records in a system.

Different users should only see the orders they are authorized to access based on:

Their username

Regions or locations mapped to them

The current session context

Instead of writing filtering conditions in every SQL query, we can configure Oracle to automatically apply the required filters whenever the table is queried.

📋 Step 1: Create an Application Context

Oracle uses Application Contexts to store session-specific attributes such as username, role, or region.

CREATE OR REPLACE CONTEXT APP_SECURITY_CTX USING app_security_pkg;

This statement means:

APP_SECURITY_CTX is a session-level context

Only the package APP_SECURITY_PKG can set values in this context

This prevents unauthorized modification of session information.

⚙️ Step 2: Set Context Values

Typically, the application server (Java, API, middleware) sets context values when a user logs in.

For testing purposes, we can simulate this using DBMS_SESSION.SET_CONTEXT.

EXEC DBMS_SESSION.SET_CONTEXT('APP_SECURITY_CTX', 'app_username', 'USER_001');
EXEC DBMS_SESSION.SET_CONTEXT('APP_SECURITY_CTX', 'app_transaction_id', 'TXN1001');
EXEC DBMS_SESSION.SET_CONTEXT('APP_SECURITY_CTX', 'app_region', 'REGION_A');
EXEC DBMS_SESSION.SET_CONTEXT('APP_SECURITY_CTX', 'app_role', 'USER');

These values are stored at the session level and can be accessed later using the SYS_CONTEXT function.

🧠 Step 3: Create the Policy Predicate Function

Next, we create a policy function that dynamically generates a filtering condition.

CREATE OR REPLACE FUNCTION app_security_policy_fn(
p_schema VARCHAR2,
p_object VARCHAR2
) RETURN VARCHAR2 IS

v_user_id   VARCHAR2(50);
v_predicate VARCHAR2(4000);

BEGIN

v_user_id := SYS_CONTEXT('APP_SECURITY_CTX', 'app_username');

IF v_user_id IS NOT NULL THEN
    v_predicate := 'order_id IN (
        SELECT order_id
        FROM order_region_map
        WHERE region_code IN (
            SELECT region_code
            FROM user_region_access
            WHERE user_name = ''' || v_user_id || '''
        )
    )';
ELSE
    v_predicate := '1 = 1';
END IF;

RETURN v_predicate;

END;
/

This function determines which rows the user is allowed to see.

Oracle calls this function automatically whenever the protected table is accessed.

🧩 Step 4: Attach the Policy to the Table

Now we attach the policy function to the table using DBMS_RLS.ADD_POLICY.

BEGIN
DBMS_RLS.ADD_POLICY(
object_schema => 'APP_SCHEMA',
object_name => 'ORDERS_HDR',
policy_name => 'ORDERS_SECURITY_POLICY',
function_schema => 'APP_SCHEMA',
policy_function => 'APP_SECURITY_POLICY_FN',
statement_types => 'SELECT',
policy_type => DBMS_RLS.SHARED_CONTEXT_SENSITIVE
);
END;
/

This tells Oracle:

Whenever someone queries APP_SCHEMA.ORDERS_HDR, automatically apply the predicate returned by APP_SECURITY_POLICY_FN.

🔍 Step 5: Run Queries Normally

Users can run a simple query like this:

SELECT * FROM APP_SCHEMA.ORDERS_HDR;

However, Oracle internally rewrites the query by applying the policy predicate.

Example of how Oracle might internally execute the query:

SELECT *
FROM APP_SCHEMA.ORDERS_HDR
WHERE order_id IN (
SELECT order_id
FROM order_region_map
WHERE region_code IN (
SELECT region_code
FROM user_region_access
WHERE user_name = 'USER_001'
)
);

The user does not need to include any filtering logic — the database automatically enforces the security rule.

✅ Recap: Implementation Flow
Step Description
1 Create an application context
2 Set session attributes
3 Create a policy predicate function
4 Attach the policy using DBMS_RLS.ADD_POLICY
5 Oracle automatically filters rows during queries
💡 Benefits of Oracle VPD

Using Oracle Virtual Private Database (VPD) provides several advantages:

Centralized security logic in the database

Automatic row filtering

No changes required in application queries

Strong protection against unauthorized data access

Scalable solution for enterprise systems

🚀 Final Thoughts

Oracle VPD provides a powerful mechanism for implementing Row-Level Security directly at the database level. By combining application contexts, policy functions, and DBMS_RLS policies, organizations can ensure consistent and secure access control across all applications that interact with the database.

This approach keeps security logic centralized, maintainable, and scalable, while ensuring users only see the data they are authorized to access.