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. 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.
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.
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.
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.
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.
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.
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.
On Tue, 26 Mar 2019 at 16:19, Moreno Andreo <moreno.andreo@xxxxxxxxxx> wrote:
Hello folks :-)
Is there any workaround to implement key encryption without breaking
relational integrity?
Thanks
Moreno.-
