Re: Key encryption and relational integrity

[Moreno Andreo <moreno.andreo@xxxxxxxxxx>]

Il 27/03/2019 07:42, Tony Shelver ha scritto:

Not in Europe, but have worked a bit with medical records systems in the USA, including sharing across providers.

The primary key of the user is _should_ be system generated, and this is meaningless from a user identity standpoint. 

It is, I was planning to use UUID

If you encrypt user name and other significant personal data on the user id record, you can query the id column all day long, and there would be no way to identify who the user is without the encryption key.

That's the first idea that came in my mind. Unfortunately, the customer needs to do "real time" search in personal data, so for each key that's pressed, I need to query the whole table filtering the encrypted value (1st layer of slowness) LIKE (2nd layer of slowness) the value the user is typing. I ran a test on my i7-2660 with SSD and 16G RAM and on a 2500 rows table these queries last about 2 seconds each keypress. So it's not the way to go, we have to find something different. Same thing with clinical data, it would be a mess because when I open the patient clinical record I need to see all treatments, or all drugs that have been prescripted, all diseases and so on, and it would be not-so fast if every clinical data name (i.e. all drug names) is encrypted.

The only other way to do it would be to store the encrypted key value in both user.id and medications.user_id.  That would encrypt the data and maintain relational integrity.

Hmmm... if user.id and medications.user_id are the same, I can link user with medication... and GDPR rule does not apply..... or am I missing something?

For US medical record systems, there is a strict act (HIPAA) that specifies both privacy and security rules, with lists of what is regarded as sensitive or private information, what can be shared with various providers and outside organizations, and so on..  As far as user name goes, that is almost never a decisive form of identification for any person in a system.

GDPR is almost the same concept, even if some rules may differ

While GDPR is relatively young and untested, surely someone in your organization (or outside) has a list of the relevant rules broken down to specific requirements.

You sure? :-)

Also, securing the data internally on a database system MAY have very different requirements to making that data visible to applications or transmitting it to external parties.

Profiling, IMHO, has to be designed in application, here I'm trying to find a way so nobody can, without the use of the application, match a patient with their clinical records (i.e. someone breaking into the server -- data breach)

Storing the user id in plain on the medications record and encrypted on the user primary key would seem meaningless, assuming some meaning could be derived from a system generated ID.

It is a system generated ID, obviously the query is more logical and quicker if i look from master into detail, so I SELECT something FROM medications WHERE medications.user_id = encrypt(user.id). Encrypting the (ex-)FK appears the best solution so far, but I'm afraid of the consequences of losing RI.

 

I would suggest you sit down with the application / legal guys generating the requirements to see what the real rules are.  if they want everything encrypted, then automatic primary key generation in the DB has to be invalidated, and they should provide that from the application side.

Having everything encrypted would be a big performance hit. We are trying to achieve best performance with the right masking.

Thanks

Moreno.-