On 3/28/19 10:36 AM, Moreno Andreo wrote:
Il 26/03/2019 18:08, Adrian Klaver ha scritto:
On 3/26/19 9:08 AM, Moreno Andreo wrote:
Il 26/03/2019 15:24, Adrian Klaver ha scritto:
On 3/26/19 7:19 AM, Moreno Andreo wrote:
Hello folks :-)
Is there any workaround to implement key encryption without
breaking relational integrity?
This is going to need more information.
OK, I'll try to be as clearer as I can
For starters 'key' has separate meanings for encryption and RI. I
could make some guesses about what you want, but to avoid false
assumptions a simple example would be helpful.
In a master-detail relation, I need to encrypt one of master table PK
or detail table FK, in order to achieve pseudonimization, required by
GDPR in Europe when managing particular data
Imagine I have
Table users
id surname last name
1 John Doe
2 Jane Doe
3 Foo Bar
Table medications
id user_id med
1 1 Medication
2 1 Ear check
...
...
medications.user_id is FK on users.id
we should achieve
Table medications
id user_id med
1 sgkighs98 Medication
2 sghighs98 Ear check
or the opposite (users.id encryption and medications.user_id kept plain)
At a first glance, it IS breaking relational integrity, so is there a
way to manage this encryption internally so RI is kept safe?
Not that I know of. RI is based on maintaining a link between parent
and child. So by definition you would be able to get to the parent
record via the child.
That's what I was afraid of :-(
A quick search on pseudonymisation found a boatload of interpretations
of how to implement this:
"Pseudonymisation' means the processing of personal data in such a
manner that the personal data can no longer be attributed to a
specific data subject without the use of additional information,
provided that such additional information is kept separately and is
subject to technical and organisational measures to ensure that the
personal data are not attributed to an identified or identifiable
natural person."
To me it would seem something like:
Table medications
id user_id med
1 sgkighs98 Medication
2 sghighs98 Ear check
Table users
id surname last name
sgkighs98 John Doe
jkopkl1 Jane Doe
uepoti21 Foo Bar
Where there is no direct link between the two.
Are you sure there isn't?... the key "sgkighs98" is present on both
tables and I can join tables on that field, so the pseudonimysation does
not apply, it's just "separation" (that was OK with the last privacy
act, but not with GDPR
Yes but you can use permissions to make the user table is unreachable by
folks with insufficient permission.
The problem is not on the application side... there you can do almost
anything you want to do. The prolem is that if someone breaks in the
server (data breach) it is easy to join patients and their medications.
That really depends on what level of user they break in as. That is a
separate security issue. It also is the difference between
pseudonymisation and anonymization, where the latter makes the data
totally unrelated to an individuals personal information.
Instead permissions would prevent linking from medications to users
even via a SELECT. One could also use pgcrypto:
https://www.postgresql.org/docs/10/pgcrypto.html
on the users table to further hide the personal info.
That's what I used to try to encrypt first name, last name, street
address and some other fields (that would be the best solution because
RI was not impacted at all), but the customer stated that they have to
perform real-time search (like when you type in the Google search box),
and the query that has to decrypt all names and return only the ones
that begin with a certain set of characters is way too slow (tried on a
good i7 configuration, that's about 2 seconds for each key pressed on a
2500-row table). So I dropped this approach.
*NOTE* I am not a lawyer so any advice on my part as to meeting legal
requirements are just me thinking out loud. I would suggest, if not
already done, getting proper legal advice on what the section quoted
above actually means.
Relax, I'm not here to ask and then sue anyone :-)
Hey, I live in the US its just best policy to make that clear:)
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
