Virtual Private Database (VPD) with Oracle

 

refer:http://www.adp-gmbh.ch/ora/security/vpd/

 

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

-- -----------------------------------------------------------------------------
-- File Name   : vpd2.sql
-- -----------------------------------------------------------------------------
-- Maintainer  : Pete Finnigan (http://www.petefinnigan.com)
-- Copyright   : Copyright (C) 2009 PeteFinnigan.com Limited. All rights
--               reserved. All registered trademarks are the property of their
--               respective owners and are hereby acknowledged.
-- -----------------------------------------------------------------------------
--  Usage      : The script provided here is available free. You can do anything 
--               you want with it commercial or non commercial as long as the 
--               copyright and this notice are not removed or edited in any way. 
--               The scripts cannot be posted / published / hosted or whatever 
--               anywhere else except at www.petefinnigan.com/vpd2.sql
-- ------------------------------------------------------------------------------

prompt [*] connect as SYS
pause

connect sys/oracle1 as sysdba

prompt [*] Drop the test user
drop user pxf cascade;

prompt [*] Recreate the test user

create user pxf identified by pxf default tablespace users temporary tablespace temp;

grant create session to pxf;

grant create any context to pxf;

grant create table to pxf;

grant unlimited tablespace to pxf;

grant create procedure to pxf;

grant execute on dbms_rls to pxf;

grant execute on dbms_session to pxf;

create table pxf.emp as select * from scott.emp;

prompt [*] Now connect as PXF
pause

connect pxf/pxf

prompt [*] Select from the sample table - should be 15 rows
pause

select * from emp;

Prompt [*] Let everyone see the table and connect as SCOTT and select again
prompt [*] should still be 15 rows

grant select on pxf.emp to public;

connect scott/tiger

select * from pxf.emp;

prompt [*] connect as PXF again and set up a simple VPD
prompt [*] that restricts access to dept 10
pause

connect pxf/pxf

create or replace function predicate (pv_schema in varchar2, pv_object in varchar2)
return varchar2
as
begin
	return 'deptno != ''10''';
end;
/

begin
	dbms_rls.add_policy(
		object_schema => 'PXF',
		object_name => 'EMP',
		policy_name => 'PXFTEST',
		policy_function => 'PREDICATE');
end;
/

prompt [*] Finally connect as SCOTT and see if he is blocked from seeing the data
prompt [*] should now be 12 rows!
pause

connect scott/tiger

select * from pxf.emp;

prompt [*]
pause
sho user

prompt [*] set up trace and dump the predicate
pause
alter session set sql_trace=true;

alter session set events '10730 trace name context forever';

prompt [*] Dump the data from the emp table
select * from pxf.emp;

prompt [*] Turn trace off
alter session set events '10730 trace name context off';
alter session set sql_trace=false;

prompt [*] Lets look at the trace file
pause

prompt [*]
pause

prompt [*] View the predicate
pause

select object_owner,object_name,policy_name,
       pf_owner,pf_owner,function
from all_policies;

set serveroutput on size 1000000

declare
	predic varchar2(1000);
begin
	dbms_output.put_line('The predicate is :'||pxf.predicate('PXF','EMP'));
end;
/

prompt [*] I have seen this type of design:
prompt [*] where the predicate functions are executable by all
pause

connect pxf/pxf
grant execute on predicate to public;
connect scott/tiger

set serveroutput on size 1000000

declare
	predic varchar2(1000);
begin
	dbms_output.put_line('The predicate is :'||pxf.predicate('PXF','EMP'));
end;
/

prompt [*] Access the data directly
pause

-- -------------------------------------------------------
-- change the file number and block number to suit your
-- database not mine.
-- -------------------------------------------------------

select distinct dbms_rowid.rowid_block_number(rowid) blk,
   dbms_rowid.rowid_relative_fno(rowid) fno
from pxf.emp;

select file_name from dba_data_files
where file_id=4;

alter system dump datafile 4 block 444;

prompt [*] Lets try again as monitor
pause

connect monitor/monitor

select distinct dbms_rowid.rowid_block_number(rowid) blk,
   dbms_rowid.rowid_relative_fno(rowid) fno
from pxf.emp;

select file_name from dba_data_files
where file_id=4;

alter system dump datafile 4 block 444;