Last updated on September 15th, 2016 at 05:55 am
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 ;|
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_val VARCHAR2 (400);
return_val := ’emp_id =100′;
2) Create the Oracle Virtual Private Database Policy
object_schema => ‘hr’,
object_name => ’emp’,
policy_name => ’emp_policy’,
function_schema => ‘sys’,
policy_function => ‘auth_user’,
statement_types => ‘select, insert, update, delete’
2.Enter the following SELECT statement:
|SELECT count(*) FROM hr.EMP;
The following output should appear:
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;
Now how to remove the VPD policy
As user SYS, remove the function and policy as follows:
|DROP FUNCTION auth_user;