Hi,

Is it possible to implement row level security in DB2 UDB v8.1 and linux on a table for different users. The requirement is that a user should be able to see and update the rows belonging to his User ID only. One way to do is views. However, i want to know if we can somehow achieve it through Special registers like USER. Please help

thanks
pbart001

AFIAK, this feature is not available in this release of db2 ...

Hi Satya,

I understand that this feature is not available in db2. However, can we take an indirect approach to implement it at the basic level. Something like, if we can figure out which user is logged in and then attach some security tag to the user name which can further be placed on the table. and then we can match the security tags. Please let me know if this can work.

thanks
pbart001

It can work ...

I have given the example for a view ... You can create INSTEAD OF triggers on the view for UPDATEs and DELETEs ..

You may want to consider performance implications of the approach

Let us know how it goes

Cheers
Sathyaram



create table lbac_main(pid int not null primary key,pdef char(20),row_owner_id char(8),seclabel char(8))

insert into lbac_main values(1,'DB2INST1 ROW','DB2INST1','LB1'),
(2,'DB2INST1 ROW','DB2INST1','LB2'),(3,'SS ROW','SATHYA','LB1')

CREATE TABLE LBAC_RULES(user_id char(8) not null,seclabel char(8) not null,tname char(20) not null,
access char(1) not null)
alter table lbac_rules add primary key(user_id,seclabel,tname,access)

insert into lbac_rules values('SATHYA','LB1','LBAC_MAIN','W'),
('SATHYA','LB2','LBAC_MAIN','R')

insert into lbac_rules values('DB2INST1','LB1','LBAC_MAIN','W'),
('DB2INST1','LB2','LBAC_MAIN','W')

CREATE VIEW LM_MAIN_V AS
select pid,pdef from lbac_rules lr,lbac_main lm where tname='LBAC_MAIN' and lr.seclabel=lm.seclabel and lr.user_id=USER and access>='R'
AND LM.ROW_OWNER_ID=USER