Row permissions and column masks are two new database concepts introduced in DB2 10 that address the shortcomings of traditional security methods. These new features act as a second layer of security that complements the table privileges security model available in prior releases of DB2.
One advantage of row permissions and column masks is that no database user is automatically exempted from them — not even users with the DATAACCESS authority. The ability to manage row permissions and column masks within a database is vested solely in a user with SECADM, thereby ensuring that users with DATAACCESS authority can no longer freely access all data in the database.
Another key advantage is that row permissions and column masks ensure that table data is protected regardless of how the SQL accesses the table, be it through an application, through ad hoc query tools, or through report generation tools.
Finally, and perhaps most significantly, row permissions and column masks require no application changes; RCAC is transparent to existing applications.
In [ ]:
%run db2.ipynb
We populate the database with the EMPLOYEE and DEPARTMENT tables so that we can run the various examples.
In [ ]:
%sql -sampledata
We will use a sample scenario based on the Health-care industry.
Data in a hospital needs to be provided to a number of individuals,including other organizations like insurance companies, clearing house etc. Each of these companies have their own employees, and there are government regulations in place dictating who can see what data or parts of patient data.
As a result security policies must be in place to ensure the rules are being met.
Across the scenario there will be different groups of individuals that access the database and there must be security in place to ensure each group or individual only sees the data they are allowed to see.
This SQL will create the base PATIENT table and populate it with information.
In [ ]:
%%sql -q
DROP TABLE HC.PATIENTS;
CREATE TABLE HC.PATIENTS
(
SIN VARCHAR(11),
USERID VARCHAR(8),
NAME VARCHAR(8),
ADDRESS VARCHAR(12),
PHARMACY VARCHAR(12),
ACCT_BALANCE DEC(9,2),
PCP_ID VARCHAR(8)
);
INSERT INTO HC.PATIENTS VALUES
('123 551 234','MAX','Max','First St.','hypertension',89.7,'LEE'),
('123 589 812','MIKE','Mike','Long St.','diabetics',8.3,'JAMES'),
('123 119 856','SAM','Sam','Big St.','aspirin',12.5,'LEE'),
('123 191 454','DOUG','Doug','Good St.','influenza',7.68,'JAMES'),
('123 456 789','BOB','Bob','123 Some St.','hypertension',9,'LEE');
SELECT * FROM HC.PATIENTS;
The following permissions need to be defined for accessing the PATIENT data:
Normally SQL similar to the following is used to define who can access the data.
verify_role_for_user(SESSION_USER,'PATIENT') = 1
AND patient.userid = SESSION_USERChanging from one user to another requires a number of SECADM features and separate connections, so in order to simplify the demo we will use a ROLE table and a UDF to simulate this capability.
In [ ]:
%%sql
DROP TABLE HC.ROLES;
CREATE TABLE HC.ROLES
(
USERID VARCHAR(8),
ROLE VARCHAR(10)
);
We also need to create a SESSION_USER global variable that can be used to identify the "current" user. Normally you would just use the SESSION_USER variable in the rules, but since we don't want to use SECADM we need to fake the userid.
In [ ]:
%sql CREATE OR REPLACE VARIABLE HC.SESSION_USER VARCHAR(8);
The HC.VERIFY_ROLE_FOR_USER function will mimic what the VERIFY_ROLE_FOR_USER
function does by returning a value of 1 if the user has that particular ROLE.
In [ ]:
%%sql -d
CREATE OR REPLACE FUNCTION
HC.VERIFY_ROLE_FOR_USER(UID VARCHAR(8), IN_ROLE VARCHAR(10))
SECURED NO EXTERNAL ACTION DETERMINISTIC
RETURNS INT
BEGIN ATOMIC
RETURN
SELECT COUNT(*) FROM HC.ROLES H
WHERE H.USERID = UID AND H.ROLE = IN_ROLE;
END@
Create some ROLES for people in the Healthcare scenario.
In [ ]:
%%sql
INSERT INTO HC.ROLES
VALUES
('LEE','PCP'),('JAMES','PCP'),
('MAX','PATIENT'),('MIKE','PATIENT'),('SAM','PATIENT'),
('DOUG','PATIENT'),('BOB','PATIENT'),
('JOHN','ACCOUNTING'),
('TOM','MEMBERSHIP'),
('JANE','RESEARCH'),
('FRED','DBA');
Now we can test to see if a user has a particular role. This first test checks to see the Dr. Lee is a PCP (Primary Care Provider).
In [ ]:
%%sql
SET HC.SESSION_USER = 'LEE';
VALUES
HC.VERIFY_ROLE_FOR_USER(HC.SESSION_USER,'PCP');
At this point in time we can set up some rules on what the various groups can see.
In [ ]:
%%sql
CREATE OR REPLACE PERMISSION HC.ROW_ACCESS ON HC.PATIENTS
FOR ROWS WHERE
(
HC.VERIFY_ROLE_FOR_USER(HC.SESSION_USER,'PATIENT') = 1 AND
HC.SESSION_USER = USERID
)
OR
(
HC.VERIFY_ROLE_FOR_USER(HC.SESSION_USER,'PCP') = 1 AND
HC.SESSION_USER = PCP_ID
)
OR
(
HC.VERIFY_ROLE_FOR_USER(HC.SESSION_USER,'MEMBERSHIP') = 1 OR
HC.VERIFY_ROLE_FOR_USER(HC.SESSION_USER,'ACCOUNTING') = 1 OR
HC.VERIFY_ROLE_FOR_USER(HC.SESSION_USER,'RESEARCH') = 1
)
ENFORCED FOR ALL ACCESS
ENABLE;
The rules now need to be activated in order for them to be enforced.
In [ ]:
%sql ALTER TABLE HC.PATIENTS ACTIVATE ROW ACCESS CONTROL;
In [ ]:
%%sql
SET HC.SESSION_USER = 'LEE';
SELECT * FROM HC.PATIENTS WHERE NAME = 'Sam';
Dr. Lee decides to give Sam some codeine for his pain. The update is successful and we can see the results.
In [ ]:
%%sql
UPDATE HC.PATIENTS SET PHARMACY = 'Codeine' WHERE NAME = 'Sam';
SELECT * FROM HC.PATIENTS WHERE NAME = 'Sam'
In [ ]:
%%sql
SET HC.SESSION_USER = 'LEE';
UPDATE HC.PATIENTS SET PHARMACY = 'Codeine' WHERE NAME = 'Doug';
The UPDATE completes, but no records are modified. To see all of the records, we need to change our userid to someone who can see all records (John in accounting). Note there is no way around this restriction - you must have the proper clearance to see the records.
In [ ]:
%%sql
SET HC.SESSION_USER = 'JOHN';
SELECT * FROM HC.PATIENTS WHERE NAME = 'Doug'
In [ ]:
%%sql
SET HC.SESSION_USER = 'LEE';
SELECT * FROM HC.PATIENTS;
Changing the current user to Dr. James will change the results that are displayed.
In [ ]:
%%sql
SET HC.SESSION_USER = 'JAMES';
SELECT * FROM HC.PATIENTS;
Changing the current user to one of the accounting, research, or fund raising users will result in all records being displayed.
In [ ]:
%%sql
SET HC.SESSION_USER = 'JOHN';
SELECT * FROM HC.PATIENTS;
Patients are able to see only their row.
In [ ]:
%%sql
SET HC.SESSION_USER = 'BOB';
SELECT * FROM HC.PATIENTS;
A DBA (Fred) who is not part of any of these groups will not be able to see any of the records, even though they may performance maintenance on the table itself.
In [ ]:
%%sql
SET HC.SESSION_USER = 'FRED';
SELECT * FROM HC.PATIENTS;
Column access control is implemented in the form of a mask, or lack thereof, on the data.
Using our Health-care scenario as the base, we will implement column access control/rules in two forms:
We MASK the account balance column. Only the ACCOUNTING team can see the account balance in the table and all others see a balance of zero.
We MASK the SIN column (Social Insurance Number column). Only the PATIENT themselves can see the full Social Insurance number and all others see only the last three digits of the number.
This first MASK is on account balance and sets it to zero for everyone except for those people in accounting.
In [ ]:
%%sql
CREATE OR REPLACE MASK HC.ACCT_BALANCE_MASK ON HC.PATIENTS FOR
COLUMN ACCT_BALANCE RETURN
CASE
WHEN HC.VERIFY_ROLE_FOR_USER(HC.SESSION_USER,'ACCOUNTING') = 1
THEN ACCT_BALANCE
ELSE 0.00
END
ENABLE;
The second mask will return the entire SIN number for the PATIENT, but only the last three digits of the SIN for all others.
In [ ]:
%%sql
CREATE OR REPLACE MASK HC.SIN_MASK ON HC.PATIENTS FOR
COLUMN SIN RETURN
CASE
WHEN HC.VERIFY_ROLE_FOR_USER(HC.SESSION_USER,'PATIENT') = 1
THEN SIN
ELSE
'XXX XXX ' || SUBSTR(SIN,9,3)
END
ENABLE;
In order for the MASKS to be effective, they need to be enabled for the table.
In [ ]:
%sql ALTER TABLE HC.PATIENTS ACTIVATE COLUMN ACCESS CONTROL;
When someone from accounting now views the records, they will only see the last three digits of the SIN field but they will see all of the accounting data.
In [ ]:
%%sql
SET HC.SESSION_USER = 'JOHN';
SELECT * FROM HC.PATIENTS;
When a researcher looks at the data, they will also only see the last three digits of the SIN field, but they will get a zero balance in the accounting field.
In [ ]:
%%sql
SET HC.SESSION_USER = 'JANE';
SELECT * FROM HC.PATIENTS;
Dr. Lee will only see his patients (ROW CONTROL) and will see the last three digits of the SIN field and zero for the account balance (COLUMN MASK).
In [ ]:
%%sql
SET HC.SESSION_USER = 'LEE';
SELECT * FROM HC.PATIENTS;
Finally, the patients will be able to see their own SIN field, but the account balance will show as zero (presumably so they don't get sick over the amount!).
In [ ]:
%%sql
SET HC.SESSION_USER = 'BOB';
SELECT * FROM HC.PATIENTS;
In [ ]:
%sql -a SELECT * FROM SYSCAT.CONTROLS;