Sample Header Ad - 728x90

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