Scenario: ExampleHMO using row and column access control - Column masks

Alex, the security administrator, further restricts data access on the ExampleHMO database by using column masks, a part of row and column access control. Column masks hide data returned to users by column unless they are permitted to view the data.

Patient payment details must only be accessible to the users in the accounts department. The account balance must not be seen by any other database users. Alex is asked to prevent access by anyone other than users belonging to the ACCOUNTING role.

Alex implements the following column mask so that a user in each role is restricted to view a result set that they are privileged to view:

--Create a Column MASK ON ACCT_BALANCE column on the PATIENT table

CREATE MASK ACCT_BALANCE_MASK ON PATIENT FOR
------------------------------------------------------------
-- Accounting information:
-- Role ACCOUNTING is allowed to access the full information
-- on column ACCT_BALANCE.
-- Other roles accessing this column will strictly view a
-- zero value.
------------------------------------------------------------
COLUMN ACCT_BALANCE RETURN
	CASE WHEN VERIFY_ROLE_FOR_USER(SESSION_USER,'ACCOUNTING') = 1
			THEN ACCT_BALANCE
		ELSE 0.00
	END
ENABLE;

Alex observes that even after creating a column mask, the data can still be viewed by the other employees. A column mask is not applied until it is activated on the table for which it was defined. Alex must now activate the mask:

--Activate column access control to implement column masks

ALTER TABLE PATIENT ACTIVATE COLUMN ACCESS CONTROL;

Alex is asked by management to hide the insurance number of the patients. Only a patient, physician, accountant, or people in the MEMBERSHIP role can view the SSN column.

Also, to protect the PHARMACY detail of a patient, the information in the PHARMACY column must only be viewed by a drug researcher or a physician. Drug researchers can see the data only if the patient has agreed to disclose the information.

Alex implements the following column masks so that a user in each role is restricted to view a result set that they are privileged to view:

CREATE MASK SSN_MASK ON PATIENT FOR
----------------------------------------------------
-- Personal contact information:
-- Roles PATIENT, PCP, MEMBERSHIP, and ACCOUNTING are allowed
-- to access the full information on columns SSN, USERID, NAME,
-- and ADDRESS. Other roles accessing these columns will
-- strictly view a masked value.
-----------------------------------------------------
COLUMN SSN RETURN
	CASE WHEN 
		VERIFY_ROLE_FOR_USER(SESSION_USER,'PATIENT') = 1 OR
		VERIFY_ROLE_FOR_USER(SESSION_USER,'PCP') = 1 OR
		VERIFY_ROLE_FOR_USER(SESSION_USER,'MEMBERSHIP') = 1 OR
		VERIFY_ROLE_FOR_USER(SESSION_USER,'ACCOUNTING') = 1
	THEN SSN
		ELSE CHAR('XXX-XX-' || SUBSTR(SSN,8,4))	END
ENABLE;

CREATE MASK PHARMACY_MASK ON PATIENT FOR
--------------------------------------------------------
-- Medical information:
-- Role PCP is allowed to access the full information on
-- column PHARMACY.
-- For the purposes of drug research, Role DRUG_RESEARCH can
-- conditionally see a patient's medical information
-- provided that the patient has opted-in.
-- In all other cases, null values are rendered as column
-- values.
----------------------------------------------------
COLUMN PHARMACY RETURN
		CASE WHEN 
		VERIFY_ROLE_FOR_USER(SESSION_USER,'PCP') = 1 OR
		(VERIFY_ROLE_FOR_USER(SESSION_USER,'DRUG_RESEARCH')=1
		AND
			EXISTS (SELECT 1 FROM PATIENTCHOICE C
	WHERE PATIENT.SSN = C.SSN AND C.CHOICE = 'drug-research' AND C.VALUE = 'opt-in'))
		THEN PHARMACY
		ELSE NULL
END
ENABLE;

Alex observes that after creating these two column masks that the data is only viewable to the intended users. The PATIENT table already had column access control activated.