DEV Community

Pranav Bakare
Pranav Bakare

Posted on

DBMS context management

Custom application context (DBMS_SESSION)

Retrieving values using SYS_CONTEXT

Applying it to a real-time security use case using VPD (Row-Level Security)

We’ll simulate a Sales Application, where each user can only view data from their own region. We'll use Oracle Context to store the region dynamically at session level and use that in a security policy — without modifying the application queries.


🎯 Goal

Allow users to access only their regional sales data using context-driven row-level security.


📐 Step-by-Step Setup


✅ Step 1: Create the Sales Table

CREATE TABLE sales_data (
sale_id NUMBER,
region VARCHAR2(50),
amount NUMBER,
sale_date DATE
);


✅ Step 2: Insert Sample Data

INSERT INTO sales_data VALUES (1, 'North', 1000, SYSDATE);
INSERT INTO sales_data VALUES (2, 'South', 1500, SYSDATE);
INSERT INTO sales_data VALUES (3, 'East', 2000, SYSDATE);
INSERT INTO sales_data VALUES (4, 'West', 2500, SYSDATE);
COMMIT;


✅ Step 3: Create an Application Context

CREATE CONTEXT app_ctx USING pkg_ctx;

🔹 app_ctx is the namespace for the context.
🔹 It will store session-specific metadata (like user’s region).
🔹 pkg_ctx is the package authorized to set values into this context.


✅ Step 4: Create the Context-Setter Package

CREATE OR REPLACE PACKAGE pkg_ctx AS
PROCEDURE set_region(p_region VARCHAR2);
END pkg_ctx;
/

CREATE OR REPLACE PACKAGE BODY pkg_ctx AS
PROCEDURE set_region(p_region VARCHAR2) IS
BEGIN
DBMS_SESSION.set_context('app_ctx', 'region', p_region);
END;
END pkg_ctx;
/

🔹 set_region procedure sets the current user's region into memory for the session.


✅ Step 5: Simulate a User Setting Context

This is normally done via login trigger or application layer, but we simulate manually here:

BEGIN
pkg_ctx.set_region('North');
END;

🧠 Now the session has this in memory:
app_ctx.region = 'North'


✅ Step 6: Verify Context Value

SELECT SYS_CONTEXT('app_ctx', 'region') FROM dual;

💬 Output:

North


✅ Step 7: Create a Policy Function for VPD

This function returns a dynamic predicate to filter rows by region:

CREATE OR REPLACE FUNCTION region_predicate (
schema_name VARCHAR2,
table_name VARCHAR2
) RETURN VARCHAR2 IS
BEGIN
RETURN 'region = ''' || SYS_CONTEXT('app_ctx', 'region') || '''';
END;


✅ Step 8: Apply Row-Level Security Using DBMS_RLS

Now we attach the policy to the table:

BEGIN
DBMS_RLS.ADD_POLICY(
object_schema => 'YOUR_SCHEMA',
object_name => 'sales_data',
policy_name => 'sales_region_policy',
function_schema => 'YOUR_SCHEMA',
policy_function => 'region_predicate',
statement_types => 'SELECT'
);
END;

🔐 This means: every SELECT on sales_data will automatically apply
WHERE region = SYS_CONTEXT('app_ctx', 'region')


✅ Step 9: Test the Behavior

SELECT * FROM sales_data;

💡 Output (since context = 'North'):

SALE_ID REGION AMOUNT SALE_DATE


1 North 1000 ...

The other region rows (South, East, West) are automatically hidden from the query without modifying the SQL.


✅ Step 10: Change the Region Dynamically

BEGIN
pkg_ctx.set_region('South');
END;

SELECT * FROM sales_data;

💡 Output:

SALE_ID REGION AMOUNT


2 South 1500

✔️ No change in query — Oracle enforces row visibility based on context.


🎯 Summary

Element Purpose

CREATE CONTEXT Define a namespace for session-level variables
DBMS_SESSION.set_context Set key-value pairs in the session context
SYS_CONTEXT Retrieve the value anywhere in SQL/PLSQL
DBMS_RLS.ADD_POLICY Attach a dynamic predicate to restrict row access
region_predicate() Returns a WHERE clause using the session’s region


🧠 Why This Is Powerful

Zero SQL code changes for business logic.

Fully dynamic, session-aware row filtering.

Centralized access control using policies.

High performance due to context being in-memory.

Top comments (0)