Re: Key encryption and relational integrity

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(dot)klaver(at)aklaver(dot)com