Virtual Private Database in Oracle

Virtual Private Database in Oracle

Virtual Private Database (VPD) is a security feature in Oracle Database that allows you to control access to specific rows or columns in a table based on user identity or other criteria. VPD policies are defined using PL/SQL functions that return a Boolean value indicating whether a user is allowed to access a particular row or column. Here is an example:

CREATE OR REPLACE FUNCTION hr_policy (schema_name VARCHAR2, object_name VARCHAR2)

   RETURN VARCHAR2

AS

BEGIN

   IF schema_name = 'HR' AND object_name = 'employees' THEN

      RETURN 'dept_id = (SELECT dept_id FROM hr.departments WHERE manager_id = SYS_CONTEXT(''USERENV'', ''SESSION_USER''))';

   ELSE

      RETURN '1=1';

   END IF;

END;

/

BEGIN

   DBMS_RLS.ADD_POLICY(

      object_schema => 'HR',

      object_name   => 'employees',

      policy_name   => 'hr_policy',

      function_schema => NULL,

      policy_function => 'hr_policy',

      statement_types => 'SELECT'

   );

END;

/

This example creates a VPD policy named "hr_policy" for the "employees" table in the "HR" schema. The policy limits access to rows in the table based on the user's department. Users can only access rows where the "dept_id" column matches the department of the user's manager.