0

Example use case: We want to protect the privacy of potential patients, that is, keep their medical procedures, if any, from being divulged. Here is a simplistic schema to illuminate the issues that I have questions about relating to Microsoft SQL Server encryption options. The questions are at the bottom.

Table1: POTENTIALPATIENTS This is a set of people who are authorized to use the medical services. About 250,000 rows. NOTE: We need to do (rapid response) full-text searching on patientname. We also want to prevent duplicates as best we can in the absence of something like a "unique patient number".

ppid uniqueidentifier  primary key
patientname nvarchar(100)
birthdate date
last4digitsOfSSN char(4)

-- prevent duplicates as best we can
CREATE UNIQUE INDEX UX_POTENTIALPATIENTS on 
POTENTIALPATIENTS(patientname, birthdate, last4digitsOfSSN)

-- for lookups using `like` or `contains` operators
(pseudo) CREATE FULLTEXT INDEX ON PATIENTNAME

Table2: MEDICALPROCEDURES This is the table showing the medical procedure(s) a potential (now actual) patient has had.

id identity(1,1) primary key
ppid uniqueidentifier  
procedurecode varchar(10)
proceduredate  date

ALTER TABLE MEDICALPROCEDURES ADD CONSTRAINT
FK_MEDICALPROCEDURES_POTENTIALPATIENTS
FOREIGN KEY(ppid) REFERENCES POTENTIALPATIENTS(ppid)

-- to quickly find all procedures for a given patient
CREATE INDEX IX_MEDICALPROCEDURES_PPID on MEDICALPROCEDURES(ppid)

Forgetting for a moment about our other application requirements (duplicate prevention, fast name lookups, fast gathering of a specified patient's procedures) we can protect patient privacy (that is, keep hidden what procedures they have had, if any) by doing either of these things:

a) encrypt column patientname, which would obscure the patient name

or

b) encrypt columns POTENTIALPATIENT.ppid and MEDICALPROCEDURES.ppid, the columns involved in the foreign-key constraint, which would obscure which patients have had which procedures

QUESTIONS:

If we encrypt patientname can we do quick patient-name lookups using LIKE/CONTAINS operators? Can patientname participate in a unique composite index?

If we encrypt the uniqueidentifier columns in the foreign-key relationship, can we quickly find all procedures a specified patient has had, avoiding a full table scan?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.