Trusted context and LBAC

Introduction
I decided to create another example to demonstrate the power of trusted connection. This time I will use LBAC Label Based Access Control in DB2. It enables more granular control on access  to the data than standard SQL GRANT and REVOKE clause. We can limit access not only the whole database objects (like tables and views) but also to the particular rows and columns in tables. Important: LBAC is not available in DB2 Express Edition (free). But it is accessible in demo (90 day) version of DB 9.7.
More information on LBAC: http://www.ibm.com/developerworks/data/tutorials/dm0605wong/

Example
Let's use the standard SAMPLE database. Assume that out of the blue very restrictive access policy in our HR department has been announced - women can only look at the data of female workers and men only male workers (or opposite). How to enforce that policy without costly and time consuming rewrtiting of our HR software ?
The rescue is the usage of LBAC. To cut the long story short it is based on labels which are assigned to the rows in the EMPLOYEE table. Than the same label is assigned to the user and by comparing these labels DB2 limits access to the row in the table.
Enabling LBAC policy on EMPLOYEE table is little bit complicate and requires several steps. But very important: it is done at the database level, does not involve any changed in the software.

Step1 - create LBAC enabled table
In order to avoid spoiling SAMPLE database I will create additional LBAC_EMPLOYEE table being a copy of the EMPLOYEE_table

create table lbac_employee like employee
grant select on table lbac_employee to role samplereadaccess
insert into lbac_employee (select * from employee)

Step 2 - create security label component and policy

CREATE SECURITY LABEL COMPONENT SEX SET {'F','M'}
CREATE SECURITY POLICY SEX_POLICY COMPONENTS SEX WITH DB2LBACRULES
ALTER SECURITY POLICY SEC_POLICY USE ROLE AUTHORIZATIONS

Step 3 - enable LBAC on a table

ALTER TABLE LBAC_EMPLOYEE ADD SECURITY POLICY SEX_POLICY

ALTER TABLE lbac_employee ADD COLUMN SEX_PROTECTION DB2SECURITYLABEL
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL20402N Authorization ID "DB2INST1" does not have the LBAC credentials to
perform the "ALTER" operation on table "DB2INST1.LBAC_EMPLOYEE".
SQLSTATE=42519

The second command failed - it means that our LBAC protection over EMPLOYEE table is working. Only an user having valid LBAC security level can modify the table. Even instance owner (db2inst1) cannot modify LBAC protected table without LBAC authorization !

Step 4 - create role and security labels

CREATE SECURITY LABEL SEX_POLICY.ADM COMPONENT SEX 'F','M'
GRANT SECURITY LABEL SEX_POLICY.ADM TO user db2inst1 FOR WRITE ACCESS
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0554N An authorization ID cannot grant a privilege or authority to itself.
SQLSTATE=42502

Again - the second command failed. Starting from DB2 9.7 security role has been separated from the database administrator role. Only the user having SECAMD (security administrator) role can assign LBAC privileges to users.

Step 5 - create SECADM user

# linux commmand
adduser db2sec1

grant secadm on database to user db2sec1

# connect to SAMPLE database as db2sec1 user and rerun the command

GRANT SECURITY LABEL SEX_POLICY.ADM TO user db2inst1 FOR WRITE ACCESS

(passed this time)

Step 6 - Finalize LBAC table protection
Reconnect as db2inst1 user and rerun the command again

ALTER TABLE lbac_employee ADD COLUMN SEX_PROTECTION DB2SECURITYLABEL

(passed this time)

Step 7 - Create roles and additional security labels
Assume that role LBAC_F enables access to female employees and LBAC_M to male employees.

create role LBAC_F
create role LBAC_M
CREATE SECURITY LABEL SEX_POLICY.F COMPONENT SEX 'F'
CREATE SECURITY LABEL SEX_POLICY.M COMPONENT SEX 'M'
alter security policy sex_policy user role LBAC_F
alter security policy sex_policy user role LBAC_E
GRANT SECURITY LABEL SEX_POLICY.F TO ROLE LBAC_F for all access
GRANT SECURITY LABEL SEX_POLICY.M TO ROLE LBAC_M for all access
GRANT samplereadaccess to role LBAC_F
GRANT samplereadaccess to role LBAC_M

Step 8 - assign labels to the LBAC_EMPLOYEE rows

update lbac_employee set sex_protection=seclabel('SEX_POLICY','M') where Sex='M'
update lbac_employee set sex_protection=seclabel('SEX_POLICY','F') where Sex='F'
select substr(seclabel_to_char('SEX_POLICY',sex_protection),1,6) from lbac_employee

Step 9 - modify trusted connection object
Assume the user 'PETER' is allowed to read only males workers and user 'DOROTHY' only female workers.

ALTER TRUSTED CONTEXT MYTCX ADD USE FOR PETER ROLE LBAC_M WITHOUT AUTHENTICATION
ALTER TRUSTED CONTEXT MYTCX ADD USE FOR DOROTHY ROLE LBAC_F WITHOUT
AUTHENTICATION

Step 10 - run application and watch the output

I modified the application a little bit - look at the source code. It is very simply - just run and print the "SELECT * FROM LBAC_EMPLOYEE" statement. The same code is executed firstly by 'PETER' and secondly by 'DOROTHY'.  The first time the code yields 23 rows and the second time 11 rows. What more important - the user is unaware about the presence of the other rows so the access to this rows is forbidden at the database (not application) level.