Oracle Virtual Private Database

ORACLE VIRTUAL PRIVATE DATABASE

This is my scenario:

There are 3 managers and they have access to "HR.MYEMPLOYEES" table to see the details of employees which are working for their own department but we dont want them to see other departments' employees.

Create users for managers and give the required privileges:

create user Neena identified by Neena;
grant connect to Neena;
grant select on hr.myemployees to Neena;

create user Alexander identified by Alexander;
grant connect to Alexander;
grant select on hr.myemployees to Alexander;

create user Nancy identified by Nancy;
grant connect to Nancy;
grant select on hr.myemployees to Nancy;

Check "HR.MYEMPLOYEES" table:

select * from hr.myemployees;

Neena's employee_id........=101
Alexander's employee_id....=103
Nancy's employee_id........=108


We need the function which we'll use it for VPD policy:

CREATE OR REPLACE FUNCTION man_policy_func(user_name IN VARCHAR2,
                                      tab_name  in varchar2)
  RETURN varchar2 IS
  granted_manager NUMBER;
BEGIN
  CASE SYS_CONTEXT('USERENV', 'SESSION_USER')
    WHEN 'NEENA' THEN
      granted_manager := 101;
    WHEN 'ALEXANDER' THEN
      granted_manager := 103;
    WHEN 'NANCY' THEN
      granted_manager := 108;
    ELSE
      granted_manager := 0;
  END CASE;

  RETURN 'manager_id=' || granted_manager;

END;

With this function, Oracle will add the following clause on each query for our table:

where manager_id = &granted_manager;

Now, let's create the VPD policy:

BEGIN
      SYS.DBMS_RLS.ADD_POLICY(
        object_schema   => 'hr',
        object_name     => 'myemployees',
        policy_name     => 'manager_policy_vpd',
        function_schema => 'SYS',
        policy_function => 'man_policy_func',
        statement_types => 'select'
     );
  END;

Check if our VPD policy is working:

• As you see, Neena could only see the employees whose manager_id is 101

• Like Neena, Alexander can see only his employees.