Virtual Private Database (VPD) enables you to create security policies to control database access at the row and column level. Essentially, Oracle Virtual Private Database adds a dynamic WHERE clause to a SQL statement that is issued against the table, view, or synonym to which an Oracle Virtual Private Database security policy was applied.
It present in Enterprise Edition of Oracle with no extra Cost.
Apply Oracle Virtual Private Database policies to SELECT, INSERT, UPDATE, INDEX, and DELETE statements.
Attaching Oracle Virtual Private Database security policies to database tables, views, or synonyms, rather than implementing access controls in all your applications, provides the following benefits:
Attaching a Policy to a Database Table, View, or Synonym
object_schema => 'hr',
object_name => 'employees',
policy_name => 'secure_update',
policy_function => 'check_updates',
statement_types => 'SELECT,INDEX');
dbms_rls.add_policy : Adds a policy to a table or view
dbms_rls.drop_policy : Drops a policy from a table or view
dbms_rls.refresh_policy : Forces a reparse of open cursors associated with a policy, so that a new policy or change to a policy can be implemented immediately
dbms_rls.enable_policy : Enables or disables a policy that was previously added to a table or view
Application Context is a namespace with a corresponding set of attribute value pairs and is bound to a PL/SQL package for setting values in the context.
Steps to create the context with VPD policy
1. Creating the package APP_SECURITY_CONTEXT This will set the attribute
called “empno” in the user context APP_CONTEXT. The employee ID for the
employee is retrieved from a table based on the current user.
Create or replace package APP_SECURITY_CONTEXT is
create or replace package body APP_SECURITY_CONTEXT is
select EMPNO into EMP_ID from EMP_TAB
where EMPLOYEE_NAME =
2. Create a unique context and associate it with the PL/SQL Package. Context
names must be unique within the entire database, not just the schema. Contexts
are owned by SYS.
Create context APP_CONTEXT using APP_SECURITY_CONTEXT;
3. Set the Context. You can set the user’s security automatically by using and
event trigger to pull session info into the context.
4. Use the context in a policy function.
create or replace package body APP_SECURITY as
/*limits select statements based on employee number*/
function EMPNO_SEC (D1 varchar2, D2 varchar2) return varchar2
D_PREDICATE varchar2 (2000);
D_PREDICATE = 'EMPNO = SYS_CONTEXT
Finally, we add the policy for the user NELSON: