Virtual Private Database (VPD) with Oracle

Virtual Private Database is also known as fine graind access control (FGAC). It allows to define which rows users may have access to.

A simple example

In this example, it is assumed that a company consists of different departments (with each having an entry in the departments table). An employee belongs to exactly on department. A department can have secrets that go into the department_secrets table.

create table department (
  dep_id int primary key,
  name    varchar2(30)
);

create table employee (
  dep_id references department,
  name    varchar2(30)
);

create table department_secrets (
  dep_id references department,
  secret varchar2(30)
);

Filling in some truly confidential secrets:

insert into department values (1, 'Research and Development');
insert into department values (2, 'Sales'                   );
insert into department values (3, 'Human Resources'         );

insert into employee values (2, 'Peter');
insert into employee values (3, 'Julia');
insert into employee values (3, 'Sandy');
insert into employee values (1, 'Frank');
insert into employee values (2, 'Eric' );
insert into employee values (1, 'Joel' );

insert into department_secrets values (1, 'R+D Secret #1'  );
insert into department_secrets values (1, 'R+D Secret #2'  );
insert into department_secrets values (2, 'Sales Secret #1');
insert into department_secrets values (2, 'Sales Secret #2');
insert into department_secrets values (3, 'HR Secret #1'   );
insert into department_secrets values (3, 'HR Secret #2'   );

For any employee, it must be possible to see all secrets of his department, but no secret of another department.

In order to make that happen with Oracle, we need to create a package, a trigger, and set a policy.

First, the package is created.

create or replace package pck_vpd
as
  p_dep_id department.dep_id%type;

  procedure set_dep_id(v_dep_id department.dep_id%type);

  function predicate (obj_schema varchar2, obj_name varchar2) return varchar2;
end pck_vpd;
/

create or replace package body pck_vpd as 
  
  procedure set_dep_id(v_dep_id department.dep_id%type) is
  begin
    p_dep_id := v_dep_id;
  end set_dep_id;


  function predicate (obj_schema varchar2, obj_name varchar2) return varchar2 is
  begin
    return 'dep_id = ' || p_dep_id;
  end predicate;
  
end pck_vpd;
/

Then the trigger is defined. This trigger fires whenever someone log on to the database. It finds the user's departement id (dep_id) and calls set_dep_id in the package.

create or replace trigger trg_vpd
  after logon on database
declare
  v_dep_id department.dep_id%type;
begin
  select dep_id into v_dep_id
  from employee where upper(name) = user;

  pck_vpd.set_dep_id(v_dep_id);
end;
/

Finally, the policy is defined. The policy states which procedure is used to add a where clause part to the where clause if someone executes a select statement.

begin
dbms_rls.add_policy  (
  user,
  'department_secrets',
  'choosable policy name',
  user,
  'pck_vpd.predicate',
  'select,update,delete');
end;
/

To test the setup, some users are created.

create user frank identified by frank default tablespace users temporary tablespace temp;
create user peter identified by peter default tablespace users temporary tablespace temp;
create user julia identified by julia default tablespace users temporary tablespace temp;

The necessary privileges are granted.

grant all on department_secrets to frank;
grant all on department_secrets to peter;
grant all on department_secrets to julia;

grant create session to frank;
grant create session to peter;
grant create session to julia;

A public synonym is created.

create public synonym department_secrets for department_secrets;

Frank (belonging to R+D) executes a query....

connect frank/frank;

select * from department_secrets;

    DEP_ID SECRET
---------- ------------------------------
         1 R+D Secret #1
         1 R+D Secret #2

Peter (belonging to Sales) executes a query....

connect peter/peter;

select * from department_secrets;

    DEP_ID SECRET
---------- ------------------------------
         2 Sales Secret #1
         2 Sales Secret #2


	René Nyffenegger's collection of things on the web
	René Nyffenegger on Oracle - Most wanted - Feedback