Virtual Private Database (VPD) of Oracle

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.

Benefits:
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:
Security
simplicity
Flexibility

Attaching a Policy to a Database Table, View, or Synonym
DBMS_RLS.ADD_POLICY procedure
BEGIN
DBMS_RLS.ADD_POLICY(
object_schema => 'hr',
object_name => 'employees',
policy_name => 'secure_update',
policy_function => 'check_updates',
statement_types => 'SELECT,INDEX');
END;

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
Procedure SET_EMPNO;
end;

create or replace package body APP_SECURITY_CONTEXT is
procedure SET_EMPNO
IS
EMP_ID number;
Begin
select EMPNO into EMP_ID from EMP_TAB
where EMPLOYEE_NAME =
SYS_CONTEXT('USERENV','SESSION_USER');
dbms_session.set_context ('APP_CONTEXT','EMPLOYEE_NO',
EMP_ID);
end;
end;
/

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
is
D_PREDICATE varchar2 (2000);
begin
D_PREDICATE = 'EMPNO = SYS_CONTEXT
("APP_CONTEXT","EMPLOYEE_NO");
Return D_PREDICATE;
end EMPNO_SEC;
end APP_SECURITY;

Finally, we add the policy for the user NELSON:

dbms_rls.add_policy
('NELSON','EMP_TIME_TAB','EMP_POLICY','SECUSR','APP_SECURITY,EMP
NO_SEC','SELECT')

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.