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.
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
b) encrypt columns
MEDICALPROCEDURES.ppid, the columns involved in the foreign-key constraint, which would obscure which patients have had which procedures
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?