Pranav Bakare

Posted on Jul 12 • Edited on Jul 19

DBMS_RLS | Oracle VPD | Row-Level Security in Oracle

#oracle #data #database #datascience

🔐 Enforcing Row-Level Security in Oracle with DBMS_RLS: A Step-by-Step Guide

In enterprise systems, controlling access to only specific rows in a table based on the logged-in user is a common requirement. Oracle provides a powerful feature for this called Virtual Private Database (VPD), implemented using the DBMS_RLS package.

In this post, we’ll walk through how to implement a VPD policy using Oracle's DBMS_RLS.ADD_POLICY and a policy function that dynamically filters data based on the user context.

🧭 Use Case

Let's say you have a table CFR_HDR that stores Cargo Flight Records, and you want users to see only the rows they're authorized to access, based on:

Their username
Airports mapped to them
Current session context (e.g., transaction ID)

Oracle VPD helps enforce this logic automatically — you don’t need to filter manually in every query.

📋 Step 1: Create a Custom Application Context

Oracle requires a named context to securely store session-level attributes. This context is later used inside the policy function to fetch values dynamically.

CREATE OR REPLACE CONTEXT NGCS_CTX USING gen_pkg;

✅ This tells Oracle: "NGCS_CTX is a named context, and only gen_pkg is allowed to set values in it."

⚙️ Step 2: Simulate Setting Context (Normally Done by App Server)

For testing, you can set values manually using DBMS_SESSION.SET_CONTEXT.


EXEC DBMS_SESSION.SET_CONTEXT('NGCS_CTX', 'ngcs_username', 'USR123');
EXEC DBMS_SESSION.SET_CONTEXT('NGCS_CTX', 'ngcs_transaction_id', 'TRX999');
EXEC DBMS_SESSION.SET_CONTEXT('NGCS_CTX', 'ngcs_city', 'DEL');
EXEC DBMS_SESSION.SET_CONTEXT('NGCS_CTX', 'ngcs_role', 'OPS');

In real-world scenarios, your Java or middleware layer will set this context before making a DB call.

🧠 Step 3: Create the Policy Predicate Function

This function builds a WHERE clause based on the session context and returns it as a string.

CREATE OR REPLACE FUNCTION ngcs_policies_gsa_pg.cfr_hdr_security_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('NGCS_CTX', 'ngcs_username');

    IF v_user_id IS NOT NULL THEN
        v_predicate := 'fhd_id IN (
            SELECT fhd_id 
            FROM cfr_leg_seg 
            WHERE leg_seg_orig IN (
                SELECT apt_code 
                FROM GEN_MST_USR_CTY_APT_MAP 
                WHERE usr_name = ''' || v_user_id || '''
            )
        )';
    ELSE
        v_predicate := '1 = 1';  -- Fallback: allow full access
    END IF;

    RETURN v_predicate;
END;
/

✅ This function is what Oracle will call internally to filter rows based on the user.

🧩 Step 4: Attach Policy Using DBMS_RLS.ADD_POLICY

Now link the function to the target table (CFR_HDR) so that Oracle applies the logic automatically.

BEGIN
    DBMS_RLS.ADD_POLICY(
        object_schema   => 'CGO_OWNR',
        object_name     => 'CFR_HDR',
        policy_name     => 'CFR_HDR_POLICY',
        function_schema => 'NGCS_POLICIES_GSA_PG',
        policy_function => 'cfr_hdr_security_fn',
        statement_types => 'SELECT',
        policy_type     => DBMS_RLS.SHARED_CONTEXT_SENSITIVE
    );
END;
/

✅ This tells Oracle:
“Whenever someone queries CGO_OWNR.CFR_HDR, apply the WHERE clause returned by cfr_hdr_security_fn automatically.”

🔍 Step 5: Run Your Queries (No WHERE Clause Needed)

Now, even if the user runs a simple query like:

sql

SELECT * FROM CGO_OWNR.CFR_HDR;

Oracle internally rewrites it like:

SELECT * 
FROM CGO_OWNR.CFR_HDR
WHERE fhd_id IN (
  SELECT fhd_id 
  FROM cfr_leg_seg 
  WHERE leg_seg_orig IN (
    SELECT apt_code 
    FROM GEN_MST_USR_CTY_APT_MAP 
    WHERE usr_name = 'USR123'
  )
);

✅ The user sees only their permitted rows, and they don’t even need to know the filter logic!

✅ Recap: End-to-End Flow
Step Action
1️⃣ Create application context with CREATE CONTEXT
2️⃣ Set context values (via app or manually)
3️⃣ Create a function that returns the RLS predicate
4️⃣ Attach the function to a table using DBMS_RLS.ADD_POLICY
5️⃣ Run your query — Oracle auto-applies the WHERE clause

💡 Final Thoughts

Oracle VPD is a powerful feature that allows centralized, secure, and automatic enforcement of row-level access. By using DBMS_RLS and context-based policy functions, you avoid leaking business logic to the front end and make your security model robust, dynamic, and scalable.

DEV Community