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)