Oracle VPD in Action: Building Role-Based Row-Level Security from Scratch
Row-level security is one of those requirements that shows up in almost every enterprise database — Finance shouldn’t see HR’s salary data, HR shouldn’t see Finance’s, and no team should see another’s unless explicitly authorized.
Most teams solve this at the application layer, hard-coding WHERE clauses into every query. It works — until someone connects directly through SQL Developer or a reporting tool and bypasses the app entirely.
Oracle’s Virtual Private Database (VPD), also known as Fine-Grained Access Control (FGAC), solves this properly: it enforces row-level and column-level security inside the database engine itself, so it’s enforced everywhere, every time — no exceptions.
In this post, I’ll walk through how I implemented it, mapping database roles to departments on a sample emp_test table.
How VPD Works
- Policy Association – A security policy is bound to a table or view using the
DBMS_RLS(Row Level Security) package. - Dynamic Interception – Any
SELECT,INSERT,UPDATE, orDELETEagainst the protected table is intercepted by Oracle. - Policy Evaluation – A PL/SQL policy function runs to determine the security rule for the current session.
- Predicate Appending – The function returns a string (e.g.
department = 'FINANCE'), which Oracle silently appends to the query. - Execution – The user only ever sees rows they are authorized for — completely transparent to the client application.
Key Benefits
- Transparent security — no custom query logic needed in the application
- Granular control — restrict by role, session attribute, application context, or even time of day
- Protection against direct access — enforced even via SQL*Plus, SQL Developer, or third-party reporting tools
- Reduced maintenance — no more duplicate views/tables per user segment
Step 1: Create the Test Table and Sample Data
CREATE TABLE emp_test (
emp_id NUMBER,
emp_name VARCHAR2(50),
department VARCHAR2(50),
salary NUMBER
);
INSERT INTO emp_test VALUES (1, 'Munish', 'IT', 85000);
INSERT INTO emp_test VALUES (2, 'Deepak', 'MAKT', 90000);
INSERT INTO emp_test VALUES (3, 'Sanjay', 'FINANCE', 75000);
INSERT INTO emp_test VALUES (4, 'Bikash', 'HR', 65000);
COMMIT;Step 2: Create the Application Context
An application context securely stores session-specific attributes — such as the user’s mapped department — so the policy function can read them efficiently.
CREATE OR REPLACE PACKAGE emp_ctx_pkg AS
PROCEDURE set_department;
END emp_ctx_pkg;
/
CREATE OR REPLACE PACKAGE BODY emp_ctx_pkg AS
PROCEDURE set_department IS
v_role VARCHAR2(50);
v_dept VARCHAR2(50);
BEGIN
IF v_role = 'IT' THEN
v_dept := 'IT';
ELSIF v_role = 'MAKT' THEN
v_dept := 'MAKT';
ELSIF v_role = 'FINANCE' THEN
v_dept := 'FINANCE';
ELSIF v_role = 'HR' THEN
v_dept := 'HR';
ELSE
v_dept := 'ALL'; -- Admin / Manager
END IF;
DBMS_SESSION.SET_CONTEXT('emp_security_ctx', 'user_dept', v_dept);
END set_department;
END emp_ctx_pkg;
/
CREATE CONTEXT emp_security_ctx USING emp_ctx_pkg;Step 3: Create the Policy Function
The policy function returns the predicate (WHERE clause) that Oracle appends to any query against the protected table.
CREATE OR REPLACE PACKAGE emp_sec_fn_pkg AS
FUNCTION f_security_predicate (
schema_p IN VARCHAR2,
table_p IN VARCHAR2
) RETURN VARCHAR2;
END emp_sec_fn_pkg;
/
CREATE OR REPLACE PACKAGE BODY emp_sec_fn_pkg AS
FUNCTION f_security_predicate (
schema_p IN VARCHAR2,
table_p IN VARCHAR2
) RETURN VARCHAR2 IS
v_user_dept VARCHAR2(50);
v_predicate VARCHAR2(4000);
BEGIN
v_user_dept := SYS_CONTEXT('emp_security_ctx', 'user_dept');
IF v_user_dept = 'ALL' THEN
RETURN ''; -- No predicate added (returns all rows)
END IF;
v_predicate := 'department = ''' || v_user_dept || '''';
RETURN v_predicate;
END f_security_predicate;
END emp_sec_fn_pkg;
/Step 4: Add the VPD Policy Using DBMS_RLS
BEGIN
DBMS_RLS.ADD_POLICY (
object_schema => 'SYSTEM',
object_name => 'emp_test',
policy_name => 'emp_dept_policy',
function_schema => 'SYSTEM',
policy_function => 'emp_sec_fn_pkg.f_security_predicate',
statement_types => 'SELECT, UPDATE, DELETE'
);
END;
/
BEGIN
DBMS_RLS.ENABLE_POLICY(
object_schema => 'SYSTEM',
object_name => 'emp_test',
policy_name => 'emp_dept_policy',
enable => TRUE
);
END;
/Key parameters:
statement_types— which DML operations trigger the policyupdate_check— when TRUE, Oracle validates that inserted/updated rows still satisfy the policy predicate, preventing a user from writing a row that violates their own security rule
Step 5: Test the Implementation
-- Create Database Roles
CREATE ROLE it;
CREATE ROLE makt;
CREATE ROLE finance;
CREATE ROLE hr;
-- Grant Privileges
GRANT connect, resource TO it;
GRANT connect, resource TO makt;
GRANT connect, resource TO hr;
GRANT connect, resource TO finance;
GRANT SELECT, INSERT, UPDATE ON system.emp_test TO finance;
GRANT SELECT, INSERT, UPDATE ON system.emp_test TO makt;
GRANT SELECT, INSERT, UPDATE ON system.emp_test TO hr;
GRANT SELECT, INSERT, UPDATE ON system.emp_test TO it;
-- Create test database users
CREATE USER finance_user IDENTIFIED BY password123;
CREATE USER it_user IDENTIFIED BY password123;
CREATE USER makt_user IDENTIFIED BY password123;
CREATE USER hr_user IDENTIFIED BY password123;
-- Grant roles to users
GRANT finance TO finance_user;
GRANT it TO it_user;
GRANT makt TO makt_user;
GRANT hr TO hr_user;
-- Allow every role to execute the context package
CREATE OR REPLACE PUBLIC SYNONYM emp_ctx_pkg FOR SYSTEM.emp_ctx_pkg;
GRANT EXECUTE ON emp_ctx_pkg TO it;
GRANT EXECUTE ON emp_ctx_pkg TO makt;
GRANT EXECUTE ON emp_ctx_pkg TO finance;
GRANT EXECUTE ON emp_ctx_pkg TO hr;Once each user logs in and their session context is initialized, querying emp_test transparently returns only the rows belonging to their department — with zero application-level filtering.
Closing Thoughts
VPD is one of the most underused security features in Oracle — it moves access control out of fragile application code and into the database itself, where it can’t be bypassed. For any organization handling multi-department or multi-tenant data, it’s worth serious consideration.
Screenshots of the role grants, user creation, and query verification are included in the original documentation — feel free to reach out if you’d like the full write-up or a walkthrough.
Labels/Tags: Oracle, VPD, Fine-Grained Access Control, Row Level Security, PL/SQL, DBMS_RLS, Database Security, Oracle DBA
Comments
Post a Comment