🎯 Objective (with VPD)
Allow users to only see data for their own region in the sales_data table. We'll:
Store the user's region using DBMS_SESSION.set_context
Automatically restrict data using VPD
Use no explicit WHERE clause in application queries — Oracle will handle it!
✅ Step-by-Step VPD Implementation with Context
🔹 Step 1: Create the sales_data Table
CREATE TABLE sales_data (
sale_id NUMBER,
region VARCHAR2(50),
amount NUMBER,
sale_date DATE
);
🔎 Explanation:
This table holds sales records associated with different regions. VPD will restrict access at the row level based on user’s region.
🔹 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 a Custom Application Context
CREATE CONTEXT app_ctx USING ctx_pkg;
🔎 Explanation:
This defines a session-scoped memory structure (app_ctx) for holding values like the current region. Only ctx_pkg is authorized to set this.
🔹 Step 4: Create a Package to Set Context
CREATE OR REPLACE PACKAGE ctx_pkg AS
PROCEDURE set_region(p_region VARCHAR2);
END ctx_pkg;
/
CREATE OR REPLACE PACKAGE BODY ctx_pkg AS
PROCEDURE set_region(p_region VARCHAR2) IS
BEGIN
DBMS_SESSION.set_context('app_ctx', 'region', p_region);
END;
END ctx_pkg;
/
🔎 Explanation:
The procedure set_region will be called to set the current user's region in memory.
🔹 Step 5: Simulate Setting Context for a User
BEGIN
ctx_pkg.set_region('North');
END;
✔️ Sets the current session's region to North.
🔹 Step 6: Confirm Context is Set
SELECT SYS_CONTEXT('app_ctx', 'region') AS current_region FROM dual;
🧠 Output:
North
🔹 Step 7: Create a VPD Policy Function
CREATE OR REPLACE FUNCTION region_predicate (
schema_name VARCHAR2,
table_name VARCHAR2
) RETURN VARCHAR2 IS
BEGIN
RETURN 'region = ''' || SYS_CONTEXT('app_ctx', 'region') || '''';
END;
🔎 Explanation:
This function returns a dynamic WHERE clause, like:
region = 'North'
...based on the user's session context.
🔹 Step 8: Attach VPD Policy to the Table
BEGIN
DBMS_RLS.ADD_POLICY (
object_schema => USER,
object_name => 'sales_data',
policy_name => 'region_filter_policy',
function_schema => USER,
policy_function => 'region_predicate',
statement_types => 'SELECT'
);
END;
🔎 Explanation:
Oracle now automatically appends the WHERE clause from region_predicate to every SELECT query on sales_data.
🔹 Step 9: Run Query — With No Filter!
SELECT * FROM sales_data;
🧠 Output (with region = 'North'):
SALE_ID REGION AMOUNT
1 North 1000
✔️ VPD filters the data transparently based on context. The SQL doesn’t need a WHERE clause!
🔹 Step 10: Change Region and Test Again
BEGIN
ctx_pkg.set_region('South');
END;
SELECT * FROM sales_data;
🧠 Output:
SALE_ID REGION AMOUNT
2 South 1500
✔️ The same query returns different results depending on the context — this is powerful, centralized, and secure.
🧠 Real-Time Analogy
Think of VPD like a smart glass filter: users look through the same window (SELECT * FROM sales_data), but each sees only their permitted rows, depending on their ID badge (context).
✅ Final Summary
Step What You Did
Create Context Built a namespace (app_ctx) for storing session data
Set Region Used ctx_pkg.set_region to simulate login context
Create Policy Function Returned region = '' dynamically
Add VPD Policy Told Oracle to enforce the policy on SELECT
Run Queries Got auto-filtered data with no WHERE clause needed
📌 Resume/Interview Line:
Implemented Oracle Virtual Private Database (VPD) with custom application context using DBMS_SESSION.set_context and SYS_CONTEXT to enforce dynamic row-level security. Developed context-driven policies to restrict data access transparently based on user region, enhancing security and simplifying application logic.
Top comments (0)