Oracle virtual Private database(VPD)

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’);