I have a kind of weird use case here, and I'm not sure how to deal with it.
I'm working on a project that stores medical data. The following requirement has been addressed by another team, but hasn't been much documented. I'm trying to understand if this is an acceptable solution, if there are any obvious vulnerabilities I may have missed, and if there's a better way to do it.
The database stores medical data about patients.
We need to be able to query on the patient's name, surname and birth date.
On the other hand, in the case where someone would gain unauthorized access to the data, for example in the form of a database dump, we need to prevent that person from matching with certainty identities (name, surname and birth date) with medical information.
We are not required to protect the data against someone having admin privileges on the SGBD.
The Patient entity is divided in two tables. The first table holds the name, surname and birth date, while the second is the actual medical record, with all the relations to other entities.
The association is made through a hash of the identity stored in the medical record.
Identity Record |id|name|surname|birthDate| |id|hash|Some other fields...|
Basically, to get both the identity and the record, the SQL query looks like this:
SELECT * FROM Identity i JOIN Record r ON r.hash = hash(i.id || i.name || i.surname || i.birthDate)
As stated in the comments, this provides virtually no security, since anyone knowing the code can compute the association.
Since we're using a single one-way hash, navigating from the identity to the medical record requires only one operation, but the opposite requires computing all the hashes.
The probability of simply guessing the association is inversely proportional to the number of records, although there is no way to check if that guess is true. (Unlike when guessing a password, where a public login form can give you confirmation.)
What I found so far
If querying on the name, surname or birth date wasn't necessary, we could just encrypt these fields.
If we encrypt these fields, the only way of querying them would be decrypt everything first.
I don't know why they used hashing instead of deterministic encryption for the association. Or they could have put a hash of the medical record into the patient table, so navigating from the record to the table would only require one operation.
The hash needs to be keyed or anyone seeing the code can compute the association.
The key would at the very least be stored somewhere on the application server.
The key would be passed to the SGBD in the query. That means it could be intercepted or logged, so the protection would work against someone getting, for example, a dump of the tables, but not against someone with admin rights on the DB.
Adding fake data and randomizing the order of the records can make it harder to guess the association.
First of all, is this viable at all?
Are there any obvious flaws I didn't mention?
There are a number of recommendations when dealing with hashes (algorithm type, salt, etc), but they usually apply to use cases where the data itself is not present in the database. Is it worth it to put them into practice here?
Is there a better general approach to this problem?
Are there any reference documents about handling medical data?