Re: Key encryption and relational integrity

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(dot)andreo(at)evolu-s(dot)it>
wrote:

> Hello folks :-)
>
> Is there any workaround to implement key encryption without breaking
> relational integrity?
>
> Thanks
>
> Moreno.-
>
>
>
>