/*******************************************************************************************
Author : Vinayak Vishweshwara Dabgar
www.dabgarvinayakv.com
Oracle Apps DBA - Consultant | DBA - Developer | Database Architect | Performance Tuning Expert
ORACLE DATABASE SECURITY HANDBOOK
PART 15 : ORACLE VIRTUAL PRIVATE DATABASE (VPD)
Topics Covered
---------------
1 Virtual Private Database concept
2 Row-Level Security (RLS)
3 DBMS_RLS package
4 Policy functions
5 Adding VPD policies
6 Department-based access example
7 Policy enforcement
8 Viewing VPD policies
9 Removing VPD policies
10 Enterprise VPD architecture
*******************************************************************************************/
/*******************************************************************************************
QUESTION 1
What is Oracle Virtual Private Database (VPD)?
*******************************************************************************************/
-- Oracle Virtual Private Database (VPD) provides
-- Row-Level Security (RLS).
-- It automatically adds security predicates
-- to SQL statements executed by users.
/*******************************************************************************************
QUESTION 2
What problem does VPD solve?
*******************************************************************************************/
-- VPD restricts data access at row level.
-- Different users see different rows
-- from the same table.
/*******************************************************************************************
QUESTION 3
Example VPD scenario
*******************************************************************************************/
-- HR managers should see only employees
-- belonging to their department.
/*******************************************************************************************
QUESTION 4
Which package manages VPD policies?
*******************************************************************************************/
-- Oracle provides
-- DBMS_RLS
-- package to manage VPD policies.
/*******************************************************************************************
QUESTION 5
What is a VPD policy function?
*******************************************************************************************/
-- A policy function returns a WHERE clause
-- that Oracle appends to SQL queries.
/*******************************************************************************************
QUESTION 6
Create policy function
*******************************************************************************************/
CREATE OR REPLACE FUNCTION training_user.emp_dept_policy
(
schema_name VARCHAR2,
table_name VARCHAR2
)
RETURN VARCHAR2
AS
BEGIN
RETURN 'department_id = SYS_CONTEXT(''USERENV'',''CLIENT_IDENTIFIER'')';
END;
/
/*******************************************************************************************
QUESTION 7
What does this function do?
*******************************************************************************************/
-- The function returns a predicate that filters rows
-- based on department_id.
/*******************************************************************************************
QUESTION 8
Add VPD policy to HR.EMPLOYEES table
*******************************************************************************************/
BEGIN
DBMS_RLS.ADD_POLICY
(
object_schema => 'HR',
object_name => 'EMPLOYEES',
policy_name => 'EMP_DEPT_POLICY',
function_schema => 'TRAINING_USER',
policy_function => 'EMP_DEPT_POLICY'
);
END;
/
/*******************************************************************************************
QUESTION 9
How does VPD modify queries?
*******************************************************************************************/
-- User query
SELECT *
FROM hr.employees;
-- Oracle automatically transforms it into
SELECT *
FROM hr.employees
WHERE department_id = <user department>;
/*******************************************************************************************
QUESTION 10
Set client identifier for testing
*******************************************************************************************/
BEGIN
DBMS_SESSION.SET_IDENTIFIER('50');
END;
/
/*******************************************************************************************
QUESTION 11
Query employees table
*******************************************************************************************/
SELECT employee_id,
first_name,
department_id
FROM hr.employees;
/*******************************************************************************************
QUESTION 12
Expected behavior
*******************************************************************************************/
-- Only employees belonging to department 50
-- will be returned.
/*******************************************************************************************
QUESTION 13
What types of statements can VPD policies control?
*******************************************************************************************/
-- SELECT
-- INSERT
-- UPDATE
-- DELETE
/*******************************************************************************************
QUESTION 14
Create VPD policy for SELECT operations
*******************************************************************************************/
BEGIN
DBMS_RLS.ADD_POLICY
(
object_schema => 'HR',
object_name => 'EMPLOYEES',
policy_name => 'EMP_SELECT_POLICY',
function_schema => 'TRAINING_USER',
policy_function => 'EMP_DEPT_POLICY',
statement_types => 'SELECT'
);
END;
/
/*******************************************************************************************
QUESTION 15
View existing VPD policies
*******************************************************************************************/
SELECT *
FROM DBA_POLICIES;
/*******************************************************************************************
QUESTION 16
View policy functions
*******************************************************************************************/
SELECT *
FROM DBA_POLICY_CONTEXTS;
/*******************************************************************************************
QUESTION 17
Remove VPD policy
*******************************************************************************************/
BEGIN
DBMS_RLS.DROP_POLICY
(
object_schema => 'HR',
object_name => 'EMPLOYEES',
policy_name => 'EMP_DEPT_POLICY'
);
END;
/
/*******************************************************************************************
QUESTION 18
Advantages of VPD
*******************************************************************************************/
-- Transparent to applications
-- Centralized security control
-- Row-level protection
/*******************************************************************************************
QUESTION 19
Enterprise use cases
*******************************************************************************************/
-- Multi-tenant SaaS applications
-- Banking systems
-- Healthcare records
-- HR systems
/*******************************************************************************************
QUESTION 20
Example enterprise rule
*******************************************************************************************/
-- Regional managers see only customers
-- from their region.
/*******************************************************************************************
QUESTION 21
Performance considerations
*******************************************************************************************/
-- Policy functions should be lightweight.
-- Complex logic may impact performance.
/*******************************************************************************************
QUESTION 22
Best practices for VPD
*******************************************************************************************/
-- Keep policy functions simple
-- Use SYS_CONTEXT for session attributes
-- Test policies thoroughly
/*******************************************************************************************
QUESTION 23
Enterprise VPD architecture
*******************************************************************************************/
-- USER
-- |
-- v
-- APPLICATION
-- |
-- v
-- VPD POLICY
-- |
-- v
-- TABLE
/*******************************************************************************************
QUESTION 24
Security benefits
*******************************************************************************************/
-- Prevents unauthorized row access
-- Centralizes row filtering
-- Enhances compliance
/*******************************************************************************************
QUESTION 25
Difference between VPD and Views
*******************************************************************************************/
-- Views hide columns or rows.
-- VPD dynamically filters rows
-- based on user context.
/*******************************************************************************************
QUESTION 26
Example VPD with application context
*******************************************************************************************/
-- Applications set session context
-- to enforce row filtering.
/*******************************************************************************************
QUESTION 27
Check application context
*******************************************************************************************/
SELECT SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER')
FROM dual;
/*******************************************************************************************
QUESTION 28
Enterprise HR example
*******************************************************************************************/
-- HR managers see employees
-- only from their department.
/*******************************************************************************************
QUESTION 29
VPD security summary
*******************************************************************************************/
-- Implements row-level security
-- Uses DBMS_RLS package
-- Transparent to applications
/*******************************************************************************************
QUESTION 30
Interview summary
*******************************************************************************************/
-- VPD adds dynamic predicates
-- to restrict rows based on user context.
/*******************************************************************************************
END OF PART 15
Disclaimer and Limitation of Liability
The author provides all scripts, queries, and related materials βas isβ, without any express or implied warranties, including but not limited to warranties of accuracy, completeness, reliability, merchantability, or fitness for a particular purpose.
By using, executing, or implementing any part of this material, the user acknowledges and agrees that they do so at their own risk. The author shall not be held liable for any direct, indirect, incidental, consequential, special, or exemplary damages, including but not limited to loss of data, loss of profits, system failures, security breaches, or any other damages or losses arising from the use or misuse of these materials.
Users are strongly advised to thoroughly review, validate, and test all scripts, queries, and configurations in a controlled, non-production (test) environment prior to deploying them in any live or production systems.
It is the sole responsibility of the user to ensure that the use of these materials complies with all applicable local, national, and international laws, regulations, and organizational policies.
By proceeding to use these materials, the user agrees to indemnify and hold harmless the author from any claims, damages, liabilities, or expenses arising from their use.
*******************************************************************************************/