Virtual Private Database (VPD) and DBMS_RLS in Oracle

🔐 Understanding Virtual Private Database (VPD) and DBMS_RLS in Oracle

In today's world, data security isn’t just about who can log in — it’s also about what each user can see once they’re in. That’s where Oracle’s Virtual Private Database (VPD) and the DBMS_RLS package come into play.

Let’s simplify and break it down.

---

💡 What is VPD?

Virtual Private Database (VPD) is a feature in Oracle that lets you restrict data access at the row level, based on who is querying the table.

➡️ It means: User A and User B can run the exact same query, but see different rows, depending on the rules set.

---

🔧 When Does VPD Come Into Picture?

VPD doesn't work by default. It comes into action only when a security policy is defined using the DBMS_RLS package.

So:

No policy → No filtering

With policy → Oracle filters rows behind the scenes, based on your logic

---

🛠️ How Does VPD Work?

Here’s the flow:

1. (Optional) Create a session context using DBMS_SESSION.SET_CONTEXT

2. Create a policy function that returns a WHERE clause string (predicate)

3. Use DBMS_RLS.ADD_POLICY to attach the policy to a table

4. When a user queries the table, Oracle automatically appends the predicate returned by the function

5. Users see only the data they’re allowed to see, without changing the query

---

📚 Real-Life Analogy

Imagine a shared Google Sheet with data from all departments.

Without VPD, everyone sees everything.

With VPD, when a Sales user opens it, they only see Sales rows.
It’s like Google Sheet filtering itself automatically per user!

---

🧪 Example Use Case

Suppose you have a table:

CREATE TABLE employees (
emp_id NUMBER,
emp_name VARCHAR2(50),
dept VARCHAR2(20)
);

You want users to see only employees from their department.

1. Create the Context

EXEC DBMS_SESSION.SET_CONTEXT('my_ctx', 'user_dept', 'SALES');

1. Create the Policy Function

CREATE OR REPLACE FUNCTION dept_policy_fn (
schema_name IN VARCHAR2,
table_name IN VARCHAR2
) RETURN VARCHAR2 AS
BEGIN
RETURN 'dept = SYS_CONTEXT(''my_ctx'', ''user_dept'')';
END;

1. Attach the Policy

BEGIN
DBMS_RLS.ADD_POLICY (
object_schema => 'HR',
object_name => 'EMPLOYEES',
policy_name => 'EMP_VPD_POLICY',
function_schema => 'HR',
policy_function => 'dept_policy_fn',
statement_types => 'SELECT'
);
END;

Now, when a user queries:

SELECT * FROM employees;

Oracle adds:

SELECT * FROM employees WHERE dept = 'SALES';

Automatically, without them knowing.

---

✅ Benefits of VPD

Centralized security — defined at the DB layer

Transparent filtering — no need to modify queries or application code

Context-aware — logic can be based on login, role, session, etc.

Fine-Grained Control — each row is protected dynamically

---

🧠 Final Thoughts

If you're building a multi-tenant app, working in a secure data environment, or handling role-based access, VPD is your go-to mechanism for securing data at the finest level. It’s powerful, flexible, and invisible to users.

Implement it once — and let Oracle do the filtering for you.