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;