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.klaver@aklaver.com