Simple demonstration
Define SQL*Plus/sqlcl variables
define dbService=localhost:1521/ora21
define sysPw=elCarosSecret
define tablespaceName=users
Create users
connect sys/&sysPw@&dbService as sysdba
create user vpd_admin identified by secretGarden;
create user data_owner
default tablespace &tablespaceName
temporary tablespace temp
identified by secretGarden
quota unlimited on &tablespaceName;
grant
connect,
create table
to
data_owner;
grant
connect,
create procedure,
create trigger,
administer database trigger -- Required for 'after logon to database' trigger.
to
vpd_admin;
grant
administer row level security policy on schema data_owner
to
vpd_admin;
grant
execute on dbms_rls
to
vpd_admin;
create user frank identified by secretGarden;
create user peter identified by secretGarden;
create user julia identified by secretGarden;
grant create session to frank;
grant create session to peter;
grant create session to julia;
Create tables
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.
connect data_owner/secretGarden@&dbService
create table data_owner.department (
dep_id int primary key,
name varchar2(30)
);
create table data_owner.employee (
dep_id references department,
name varchar2(30)
);
create table data_owner.department_secrets (
dep_id references department,
secret varchar2(30)
);
grant select on department to vpd_admin;
grant select on employee to vpd_admin;
grant all on department_secrets to frank;
grant all on department_secrets to peter;
grant all on department_secrets to julia;
Insert some data into the tables (of which the data in department_secrets is confidential and private to the associated department):
begin
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' );
commit;
end;
/
Create the VPD/policy package
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:
connect vpd_admin/secretGarden@&dbService
create or replace package vpd_admin.pck_vpd
authid definer
as
dep_id data_owner.department.dep_id%type;
procedure set_dep_id(v_dep_id data_owner.department.dep_id%type);
function predicate (obj_schema varchar2, obj_name varchar2) return varchar2;
end pck_vpd;
/
create or replace package body vpd_admin.pck_vpd as
procedure set_dep_id(v_dep_id data_owner.department.dep_id%type) is
begin
dep_id := v_dep_id;
end set_dep_id;
function predicate(obj_schema varchar2, obj_name varchar2) return varchar2 is
begin
return 'dep_id = ' || dep_id;
end predicate;
end pck_vpd;
/
Create a logon trigger
This trigger fires whenever someone logs on to the database. It finds the user's departement id (dep_id) and calls set_dep_id in the VPD package:
create or replace trigger vpd_admin.trg_vpd
after logon on database
declare
v_dep_id data_owner.department.dep_id%type;
begin
select
dep_id into v_dep_id
from
data_owner.employee
where
upper(name) = user;
pck_vpd.set_dep_id(v_dep_id);
end;
/
Add a policy to the sensitive table
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
--
-- Requires
-- administer row level security policy on schema data_owner
-- privilage
--
sys.dbms_rls.add_policy (
object_schema => 'DATA_OWNER',
object_name => 'department_secrets',
policy_name => 'pol_dept_secrets',
function_schema => 'VPD_ADMIN',
policy_function => 'pck_vpd.predicate',
statement_types => 'select,update,delete'
);
end;
/
Different users executing the same select statement:
Frank
Frank (belonging to R+D) executes a query:
connect frank/secretGarden@&dbService
@ select-stmt
Julia
Julia (belonging to HR) executes a query:
connect julia/secretGarden@&dbService
@ select-stmt
Peter
Peter (belonging to Sales) executes a query:
connect peter/secretGarden@&dbService
@ select-stmt
Cleaning up
connect sys/&sysPw@&dbService as sysdba
drop user frank cascade;
drop user peter cascade;
drop user julia cascade;
drop user data_owner cascade;
drop user vpd_admin cascade;