• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
Techgoeasy

Techgoeasy

Learn Oracle, PHP, HTML,CSS,Perl,UNIX shell scripts

  • Home
  • Oracle
    • Oracle database
    • Oracle Ebusiness Suite
    • Oracle weblogic
    • Oracle Performance Tuning
    • Oracle Hyperion
    • Oracle Cloud Tutorials
  • SQL
  • interview questions
  • Linux
  • PHP and HTML
  • Downloads
  • General
Home » Oracle » Oracle Database » Oracle virtual Private database(VPD)

Oracle virtual Private database(VPD)

September 20, 2014 by techgoeasy Leave a Comment

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

Filed Under: Oracle, Oracle Database Tagged With: DBMS_RLS, Oracle virtual Private database, VPD

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar



Subscribe to our mailing list

Enter your email address to subscribe to this blog and receive notifications of new posts by email

Recent Posts

  • find segment name from block in oracle
  • How the sql query is executed in Oracle
  • How to do sql query tuning in Oracle
  • How to enable 10053 trace in Oracle
  • How to find the bind variable of the sql id

Copyright © 2023 : TechGoEasy

  • Hire me
  • Privacy Policy
  • Contact Us
  • New? Start Here
  • About Us