Scenario: ExampleHMO using row and column access control - Database tables

This scenario focuses on two tables in the ExampleHMO database: the PATIENT table and the PATIENTCHOICE table.

The PATIENT table stores basic patient information and health information. This scenario considers the following columns within the PATIENT table:

SSN: The patient's insurance number. A patient's insurance number is considered personal information.
NAME: The patient's name. A patient's name is considered personal information.
ADDRESS: The patient's address. A patient's address is considered personal information.
USERID: The patient's database ID.
PHARMACY: The patient's medical information.
ACCT_BALANCE: The patient's billing information.
PCP_ID: The patient's primary care physician database ID

The PATIENTCHOICE table stores individual patient opt-in and opt-out information which decides whether a patient wants to expose his health information to outsiders for research purposes in this table. This scenario considers the following columns within the PATIENTCHOICE table:

SSN: The patient's insurance number is used to match patients with their choices.
CHOICE: The name of a choice a patient can make.
VALUE: The decision made by the patients about the choice.

For example, the row 123-45-6789, drug_research, opt-in says that patient with SSN 123-45-6789 agrees to disclose their information for medical research purposes.

The following example SQL statements create the PATIENT, PATIENTCHOICE, and ACCT_HISTORY tables. Authority is granted on the tables and data is inserted:

--Patient table storing information regarding patient 
CREATE TABLE PATIENT (
	SSN CHAR(11),
	USERID VARCHAR(18),
	NAME VARCHAR(128),
	ADDRESS VARCHAR(128),
	PHARMACY VARCHAR(250),
	ACCT_BALANCE DECIMAL(12,2) WITH DEFAULT,
	PCP_ID VARCHAR(18)
	);


--Patientchoice table which stores what patient opts 
--to expose regarding his health information

CREATE TABLE PATIENTCHOICE (
	SSN CHAR(11),
	CHOICE VARCHAR(128),
	VALUE VARCHAR(128)
);

--Log table to track account balance
CREATE TABLE ACCT_HISTORY(
	SSN 		CHAR(11),
	BEFORE_BALANCE	DECIMAL(12,2),
	AFTER_BALANCE	DECIMAL(12,2),
	WHEN 	DATE,
	BY_WHO	VARCHAR(20)
);


--Grant authority

GRANT SELECT, UPDATE ON TABLE PATIENT TO ROLE PCP;

GRANT SELECT ON TABLE PATIENT TO ROLE DRUG_RESEARCH;

GRANT SELECT, UPDATE ON TABLE PATIENT TO ROLE ACCOUNTING;
GRANT SELECT ON TABLE ACCT_HISTORY TO ROLE ACCOUNTING;

GRANT SELECT, UPDATE, INSERT ON TABLE PATIENT TO ROLE MEMBERSHIP;
GRANT INSERT ON TABLE PATIENTCHOICE TO ROLE MEMBERSHIP;

GRANT SELECT ON TABLE PATIENT TO ROLE PATIENT;

GRANT SELECT, ALTER ON TABLE PATIENT TO USER ALEX;

GRANT ALTER, SELECT ON TABLE PATIENT TO USER PAUL;
GRANT INSERT ON TABLE ACCT_HISTORY TO USER PAUL;

--Insert patient data

INSERT INTO PATIENT
  VALUES('123-55-1234', 'MAX', 'Max', 'First Strt', 'hypertension', 89.70,'LEE');
INSERT INTO PATIENTCHOICE
  VALUES('123-55-1234', 'drug-research', 'opt-out');


INSERT INTO PATIENT
  VALUES('123-58-9812', 'MIKE', 'Mike', 'Long Strt', null, 8.30,'JAMES');
INSERT INTO PATIENTCHOICE
  VALUES('123-58-9812', 'drug-research', 'opt-out');


INSERT INTO PATIENT
  VALUES('123-11-9856', 'SAM', 'Sam', 'Big Strt', null, 0.00,'LEE');
INSERT INTO PATIENTCHOICE
  VALUES('123-11-9856', 'drug-research', 'opt-in');


INSERT INTO PATIENT
  VALUES('123-19-1454', 'DUG', 'Dug', 'Good Strt', null, 0.00,'JAMES');
INSERT INTO PATIENTCHOICE
  VALUES('123-19-1454', 'drug-research', 'opt-in');