Column data masking security problem
0
votes
2
answers
254
views
In Db2 v11.5.7 on Linux I have simple table:
create table admin.patient_data (
patientid int not null primary key,
patient_name varchar(10),
illness varchar(15),
doctor_name varchar(10)
);
insert into admin.patient_data values (1, 'Alice', 'illness A', 'DOCTOR1');
insert into admin.patient_data values (2, 'Bob', 'illness B', 'DOCTOR2');
select * from admin.patient_data;
Result:
PATIENTID PATIENT_NAME ILLNESS DOCTOR_NAME
----------- ------------ --------------- ----------
1 Alice illness A DOCTOR1
2 Bob illness B DOCTOR2
Column ILLNESS is sensitive data. I would like to allow this column to be displayed only if column DOCTOR_NAME matches Db2 logged-in user.
create mask admin.patient_data on admin.patient_data
for column illness return
case when doctor_name = USER then illness else 'Masked data' end
enable;
alter table admin.patient_data activate column access control;
Now user DOCTOR1 connects to database and checks the data with:
select * from admin.patient_data
it is returned:
PATIENTID PATIENT_NAME ILLNESS DOCTOR_NAME
----------- ------------ --------------- -----------
1 Alice illness A DOCTOR1
2 Bob Masked data DOCTOR2
In row PATIENTID=2 column ILLNESS is expected to be masked.
But doctor is educated, so he/she knows all the illnesses and now target "illness B"
db2 "select * from admin.patient_data where illness = 'illness B'"
and it gets:
PATIENTID PATIENT_NAME ILLNESS DOCTOR_NAME
----------- ------------ --------------- -----------
2 Bob Masked data DOCTOR2
Field ILLNESS is still marked as expected, but now because of where conditional DOCTOR1 knows that patient Bob has "illness B".
I expected that end-users could filter by values they are returned with where condition. That is: "illness A" and "Marked data".
Is there some simple solution to prevent this? I expect to have no record returned for last select statement.
Asked by folow
(523 rep)
Apr 26, 2022, 12:52 PM
Last activity: Apr 28, 2022, 05:38 AM
Last activity: Apr 28, 2022, 05:38 AM