Oracle virtual Private database(VPD)



Last updated on September 15th, 2016 at 05:55 am

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.
Oracle Virtual Private Database enforces security, to a fine level of granularity, directly on database tables, views, or synonyms. Because you attach security policies directly to these database objects, and the policies are automatically applied whenever a user accesses data, there is no way to bypass security.
When a user directly or indirectly accesses a table, view, or synonym that is protected with an Oracle Virtual Private Database policy, Oracle Database dynamically modifies the SQL statement of the user. This modification creates a WHERE condition (called a predicate) returned by a function implementing the security policy. Oracle Database modifies the statement dynamically, transparently to the user, using any condition that can be expressed in or returned by a function. You can apply Oracle Virtual Private Database policies to SELECT, INSERT, UPDATE, INDEX, and DELETE statements.
For example, suppose a user performs the following query:

SELECT * FROM hr.EMP;

The Oracle Virtual Private Database policy dynamically appends the statement with a WHERE clause. For example:

SELECT * FROM hr.EMP WHERE emp_id =100 ;
Now i am going to explain how we can acheive the above objective
1) Create the following function, which will append the WHERE emp_id =100 clause to any SELECT statement on the HR.EMP table.

CREATE OR REPLACE FUNCTION auth_user(
schema_var IN VARCHAR2,
table_var  IN VARCHAR2
)
RETURN VARCHAR2
IS
return_val VARCHAR2 (400);
BEGIN
return_val := ’emp_id =100′;
RETURN return_val;
END auth_user;
/

2) Create the Oracle Virtual Private Database Policy

BEGIN
DBMS_RLS.ADD_POLICY (
object_schema    => ‘hr’,
object_name      => ’emp’,
policy_name      => ’emp_policy’,
function_schema  => ‘sys’,
policy_function  => ‘auth_user’,
statement_types  => ‘select, insert, update, delete’
);
END;
/

2.Enter the following SELECT statement:

SELECT count(*) FROM hr.EMP;
The following output should appear:
COUNT(*)
——–
1

The policy is in effect for user HR:
But users with administrative privileges still have access to all the rows in the table.
SO when Log back on as user SYS.

CONNECT sys/as sysdba
Enter password: password

SELECT COUNT(*) FROM hr.emp;
The following output should appear:
COUNT(*)
———
1000

Now how to remove the VPD policy
As user SYS, remove the function and policy as follows:

DROP FUNCTION auth_user;
EXEC DBMS_RLS.DROP_POLICY(‘hr’,’emp’,’emp_policy’);

Leave a Reply