Enforcing row level security using Virtual Private Database (VPD)

Scenario

In an organization, employees are categorised in three domain verticals.

(a) Finance
(b) Insurance
(c) Healthcare
(d) HR

Every vertical have a HR lead. HR leads report to HR head.

Problem statement

(1) HR leads should only be able to view/modify the details of the employees only for his/her domain.
(2) HR head can view modify the details of any employee (i.e. no domain restriction)
(3) HR leads should not be able to modify the salary of the employees more than 25000.

We’ll see how to enforce these rules using Virtual Private Database (VPD).

First create the required users & roles in the database.

SQL> create user HR_F identified by password;
SQL> create user HR_I identified by password;
SQL> create user HR_H identified by password;
SQL> create user HR_HEAD identified by password;
SQL> create user hr_data identified by password;

SQL> create role HR_ROLE;

SQL> grant connect, resource to hr_data;
SQL> grant execute on dbms_session hr_data;
SQL> grant create session, HR_ROLE to HR_F;
SQL> grant create session, HR_ROLE to HR_I;
SQL> grant create session, HR_ROLE to HR_H;
SQL> grant create session, HR_ROLE to HR_HEAD;

Create tables, required to demonstrate row level policies

$ sqlplus hr_data/password

CREATE TABLE PROJECT_DOMAIN
(
DOMAIN_ID NUMBER,
DONAIN_NAME VARCHAR2(30),
PRIMARY KEY (DOMAIN_ID)
)
/
Table created.

SQL> insert into project_domain values (1,’Finance’);
SQL> insert into project_domain values (2,’Insurance’);
SQL> insert into project_domain values (3,’Healthcare’);
SQL> insert into project_domain values (4,’HR head’);
SQL> insert into project_domain values (5,’HR Leads’);

SQL> commit;
Commit complete.

CREATE TABLE EMP
(
EMPID NUMBER,
EMPNAME VARCHAR2(30),
SALARY NUMBER,
DOMAIN_ID NUMBER REFERENCES PROJECT_DOMAIN(DOMAIN_ID)
)
/

Table created.

SQL> grant select, update, insert on emp to HR_ROLE;
Grant succeeded.

SQL> insert into emp values (101,’EMP1′,1,10000);
SQL> insert into emp values (102,’EMP2′,2,12000);
SQL> insert into emp values (103,’EMP3′,3,15000);
SQL> insert into emp values (104,’HRL1′,5,15000);
SQL> insert into emp values (105,’HRL2′,5,15000);
SQL> insert into emp values (106,’HLR3′,5,15000);
SQL> insert into emp values (107,’HHD’,4,50000);

SQL> commit;
Commit complete.

— This package will be used to enforce the rules in VPD

CREATE OR REPLACE PACKAGE hr_data.vpd_security_context
IS
PROCEDURE setuserinfo;

FUNCTION domain_filter
(
object_schema IN VARCHAR2 DEFAULT NULL,
object_name IN VARCHAR2 DEFAULT NULL
)
RETURN VARCHAR2;

FUNCTION sal_update_control
(
object_schema IN VARCHAR2 DEFAULT NULL,
object_name IN VARCHAR2 DEFAULT NULL
)
RETURN VARCHAR2;
END;
/

CREATE OR REPLACE PACKAGE BODY hr_data.vpd_security_context
IS

PROCEDURE setuserinfo
IS
username VARCHAR2(64);
action VARCHAR2(64);
module VARCHAR2(64);
service_name VARCHAR2(64);
client_info VARCHAR2(64);
domainid NUMBER;

BEGIN

— Get user information

SELECT
SYS_CONTEXT (‘USERENV’,’SESSION_USER’)
,SYS_CONTEXT (‘USERENV’,’ACTION’)
,SYS_CONTEXT (‘USERENV’,’MODULE’)
,SYS_CONTEXT (‘USERENV’,’SERVICE_NAME’)
,SYS_CONTEXT (‘USERENV’,’CLIENT_INFO’)
INTO
username
,action
,module
,service_name
,client_info
FROM dual;

— Set the appropriate restrictions based on the user account

CASE username
WHEN ‘HR_F’ THEN
domainid := 1;
WHEN ‘HR_I’ THEN
domainid := 2;
WHEN ‘HR_H’ THEN
domainid := 3;
WHEN ‘HR_HEAD’ THEN
domainid := NULL;
— As we do not want any restriction on HR_HEAD, we’ll not append any predicate
END CASE;

— Set context values based on USERENV

DBMS_SESSION.SET_CONTEXT(‘VPD_CONTEXT’, ‘DOMAINID’, domainid);

END setuserinfo;

FUNCTION domain_filter
(
object_schema IN VARCHAR2 DEFAULT NULL,
object_name IN VARCHAR2 DEFAULT NULL
)

RETURN VARCHAR2
IS
predicate VARCHAR2(4000);

BEGIN
IF SYS_CONTEXT(‘VPD_CONTEXT’,’DOMAINID’) IS NOT NULL THEN
predicate := ‘domain_id = ‘ SYS_CONTEXT(‘VPD_CONTEXT’,’DOMAINID’);
ELSE
predicate := NULL;
END IF;

RETURN predicate;

END domain_filter;

FUNCTION sal_update_control
(
object_schema IN VARCHAR2 DEFAULT NULL,
object_name IN VARCHAR2 DEFAULT NULL
)

RETURN VARCHAR2
IS
predicate VARCHAR2(4000);

BEGIN
IF SYS_CONTEXT(‘VPD_CONTEXT’,’DOMAINID’) IS NOT NULL THEN
predicate := ‘domain_id = ‘ SYS_CONTEXT(‘VPD_CONTEXT’,’DOMAINID’) ‘ AND SALARY <=25000’;
ELSE
predicate := NULL;
END IF;

RETURN predicate;

END sal_update_control;

END;
/

GRANT EXECUTE ON hr_data.vpd_security_context TO HR_ROLE
/

— The following statement creates the context namespace VPD_CONTEXT and associates it with the package HR_DATA

DROP CONTEXT VPD_CONTEXT
/
CREATE CONTEXT VPD_CONTEXT USING HR_DATA.VPD_SECURITY_CONTEXT
/

— Create a AFTER LOGON trigger which fires whenever a user login to database

$ sqlplus / as sysdba

CREATE OR REPLACE TRIGGER hr_data.on_logon
AFTER LOGON
ON DATABASE
BEGIN
— Set appropriate security based on user’s logon ID

IF USER IN (‘HR_F’,’HR_I’,’HR_H’,’HR_HEAD’) THEN
hr_data.VPD_SECURITY_CONTEXT.SETUSERINFO();
END IF;
END;
/

— Performing basic tests for SYS_CONTEXT

$ sqlplus hr_f

SQL> select SYS_CONTEXT(‘VPD_CONTEXT’,’domainid’) from dual;

SYS_CONTEXT(‘VPD_CONTEXT’,’DOMAINID’)
——————————————————————————–
1

$ sqlplus hr_i

SQL> select SYS_CONTEXT(‘VPD_CONTEXT’,’domainid’) from dual;

SYS_CONTEXT(‘VPD_CONTEXT’,’DOMAINID’)
——————————————————————————–
2

$ sqlplus hr_h

SQL> select SYS_CONTEXT(‘VPD_CONTEXT’,’domainid’) from dual;

SYS_CONTEXT(‘VPD_CONTEXT’,’DOMAINID’)
——————————————————————————–
3

$ sqlplus hr_head

SQL> select SYS_CONTEXT(‘VPD_CONTEXT’,’domainid’) from dual;

SYS_CONTEXT(‘VPD_CONTEXT’,’DOMAINID’)
——————————————————————————–
NULL

— Create policy to enforce select,insert of data from respective domain_id

BEGIN
DBMS_RLS.DROP_POLICY
(
object_schema => ‘HR_DATA’,
object_name => ‘EMP’,
policy_name => ‘HR_DATA_POLICY’
);
END;
/

BEGIN
DBMS_RLS.ADD_POLICY
(
object_schema => ‘HR_DATA’,
object_name => ‘EMP’,
policy_name => ‘HR_DATA_POLICY’,
function_schema => ‘HR_DATA’,
policy_function=> ‘VPD_SECURITY_CONTEXT.DOMAIN_FILTER’,
statement_types => ‘SELECT, INSERT’,
update_check => TRUE,
enable => TRUE,
static_policy => FALSE,
policy_type => DBMS_RLS.CONTEXT_SENSITIVE,
sec_relevant_cols => ‘DOMAIN_ID, SALARY’,
long_predicate => TRUE
);
END;
/

— Create policy to enforce update of data from respective domain_id & salary < 25000

BEGIN
DBMS_RLS.DROP_POLICY
(
object_schema => ‘HR_DATA’,
object_name => ‘EMP’,
policy_name => ‘HR_DATA_UPD_POLICY’
);
END;
/

BEGIN
DBMS_RLS.ADD_POLICY
(
object_schema => ‘HR_DATA’,
object_name => ‘EMP’,
policy_name => ‘HR_DATA_UPD_POLICY’,
function_schema => ‘HR_DATA’,
policy_function=> ‘VPD_SECURITY_CONTEXT.SAL_UPDATE_CONTROL’,
statement_types => ‘UPDATE ‘,
update_check => TRUE,
enable => TRUE,
static_policy => FALSE,
policy_type => DBMS_RLS.CONTEXT_SENSITIVE,
sec_relevant_cols => ‘ SALARY’,
long_predicate => TRUE
);
END;
/

— Time to test the VPD policies

$ sqlplus hr_i

——————-
TEST 1 – SELECT
——————-

SQL> select * from hr_data.emp;

EMPID EMPNAME DOMAIN_ID SALARY
———- —————————— ———- ———-
102 EMP2 2 12000

——————-
TEST 2 – INSERT
——————-

SQL> insert into hr_data.emp values (108, ‘EMP8’, 1, 13000);
insert into hr_data.emp values (108, ‘EMP8’, 1, 13000)
*
ERROR at line 1:
ORA-28115: policy with check option violation

——————-
TEST 3 – UPDATE
——————-

SQL> update hr_data.emp set salary=15000 where domain_id=1;

0 rows updated.

— Salary update test

$ sqlplus hr_f

SQL> update hr_data.emp set salary = 30000 where EMPID=101;
update hr_data.emp set salary = 30000 where EMPID=101
*
ERROR at line 1:
ORA-28115: policy with check option violation

SQL> update hr_data.emp set salary = 20000 where EMPID=101;

1 row updated.

SQL> commit;

Commit complete.

— Test using hr_head user (should be able to do all operations)

$ sqlplus hr_head

——————
TEST 1 – SELECT
——————

SQL> select * from hr_data.emp;

EMPID EMPNAME DOMAIN_ID SALARY
———- —————————— ———- ———-
101 EMP1 1 15000
102 EMP2 2 12000
103 EMP3 3 15000
105 HRL2 5 15000
106 HLR3 5 15000
107 HHD 4 50000
104 HRL1 5 15000

——————-
TEST 2 – INSERT
——————-

SQL> insert into hr_data.emp values (108, ‘EMP8’, 1, 13000);

1 row created.

——————-
TEST 3 – UPDATE
——————-

SQL> update hr_data.emp set salary=15000 where domain_id=1 and empid=101;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from hr_data.emp;

EMPID EMPNAME DOMAIN_ID SALARY
———- —————————— ———- ———-
101 EMP1 1 15000
102 EMP2 2 12000
103 EMP3 3 15000
105 HRL2 5 15000
106 HLR3 5 15000
107 HHD 4 50000
104 HRL1 5 15000
108 EMP8 1 13000

———————————————————————————————-
RELATED ERRORS -1
———————————————————————————————-


$ sqlplus hr_i

SQL> select * from hr_data.emp;
select * from hr_data.emp
*
ERROR at line 1:
ORA-28113: policy predicate has error

To trace the error, we can check the trace file created in $ORACLE_BASE/diag/rdbms/DB/INST/trace

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.1
System name: SunOS
Node name: dg1
Release: 5.10
Version: Generic_125100-09
Machine: sun4v
Instance name: DEVDB
Redo thread mounted by this instance: 1
Oracle process number: 46
Unix process pid: 21890, image: oracle@dg1 (TNS V1-V3)

*** 2008-05-28 14:50:23.922
*** SESSION ID:(390.9951) 2008-05-28 14:50:23.922
*** CLIENT ID:() 2008-05-28 14:50:23.922
*** SERVICE NAME:(SYS$USERS) 2008-05-28 14:50:23.922
*** MODULE NAME:(SQL*Plus) 2008-05-28 14:50:23.922
*** ACTION NAME:() 2008-05-28 14:50:23.922

————————————————————-
Error information for ORA-28113:
Logon user : HR_I
Table/View : HR_DATA.EMP
Policy name : HR_DATA_POLICY
Policy function: HR_DATA.VPD_SECURITY_CONTEXT.DOMAIN_FILTER
RLS view :
SELECT “EMPID”,”EMPNAME”,”DOMAIN_ID”,”SALARY” FROM “HR_DATA”.”EMP” “EMP” WHERE (domainid = 2)
ORA-00904: “DOMAINID”: invalid identifier
————————————————————-

———————————————————————————————-
RELATED ERRORS -2
———————————————————————————————-


$ sqlplus hr_i

SQL*Plus: Release 11.1.0.6.0 – Production on Wed May 28 16:20:35 2008

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Enter password:
ERROR:
ORA-04045: errors during recompilation/revalidation of HR_DATA.ON_LOGON
ORA-01031: insufficient privileges

Always compile database login trigger, if you make change in depending objects 

Advertisements
This entry was posted in Oracle Security and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s