What Is Virtual Private Database
Virtual Private Database (VPD) is an Oracle Database feature that transparently appends a WHERE clause to every SQL statement against a table or view. This means that users only see rows they are authorized to access, regardless of how the query is written. For APEX applications, VPD provides an elegant way to implement row level security without modifying every query in the application.
How VPD Works
VPD uses a policy function that returns a predicate string. Oracle appends this predicate to every query, insert, update, or delete on the protected table:
CREATE OR REPLACE FUNCTION dept_security_policy(
p_schema IN VARCHAR2,
p_table IN VARCHAR2
) RETURN VARCHAR2
IS
l_predicate VARCHAR2(4000);
BEGIN
IF SYS_CONTEXT('APEX$SESSION', 'APP_USER') = 'ADMIN' THEN
l_predicate := '1=1';
ELSE
l_predicate := 'department_id IN (SELECT department_id ' ||
'FROM user_departments WHERE username = ' ||
'SYS_CONTEXT(''APEX$SESSION'', ''APP_USER''))';
END IF;
RETURN l_predicate;
END;
/
BEGIN
DBMS_RLS.ADD_POLICY(
object_schema => 'HR',
object_name => 'EMPLOYEES',
policy_name => 'DEPT_SECURITY',
function_schema => 'HR',
policy_function => 'DEPT_SECURITY_POLICY',
statement_types => 'SELECT,INSERT,UPDATE,DELETE'
);
END;
/Integrating VPD With APEX
The key to making VPD work with APEX is setting the application context correctly. APEX automatically populates the APEX$SESSION context with session information including APP_USER. Your policy function reads this context to determine the current user and returns the appropriate predicate.
For more complex scenarios where you need to pass additional context beyond APP_USER, create a custom application context and populate it in an APEX application process that runs on session creation.
Performance Considerations
VPD policy functions execute on every SQL statement, so they must be fast. Avoid complex queries in the policy function. Instead, populate a context variable at session start and reference it in the policy. Oracle caches VPD predicates for identical policy function return values, so deterministic policies benefit from this caching automatically.
When VPD Is the Right Choice
Use VPD when you need row level security that is enforced at the database level regardless of how data is accessed, whether through APEX, SQL Developer, or any other tool. This is stronger than application level filtering because it cannot be bypassed by writing a custom query. Use application level filtering for simpler scenarios where database level enforcement is not required.