<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body text="#000000" bgcolor="#FFFFFF">
<div class="moz-cite-prefix">Il 27/03/2019 07:42, Tony Shelver ha
scritto:<br>
</div>
<blockquote type="cite"
cite="mid:CAG0dhZCLGR2YB1TckUFaP5OHwDr6UdmBc7j=g0kK6jZz-2sB8Q(at)mail(dot)gmail(dot)com">
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<div dir="ltr">
<div dir="ltr">
<div>Not in Europe, but have worked a bit with medical records
systems in the USA, including sharing across providers.<br>
</div>
<div><br>
</div>
<div>The primary key of the user is _should_ be system
generated, and this is meaningless from a user identity
standpoint. </div>
</div>
</div>
</blockquote>
It is, I was planning to use UUID<br>
<blockquote type="cite"
cite="mid:CAG0dhZCLGR2YB1TckUFaP5OHwDr6UdmBc7j=g0kK6jZz-2sB8Q(at)mail(dot)gmail(dot)com">
<div dir="ltr">
<div dir="ltr">
<div>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.</div>
</div>
</div>
</blockquote>
That's the first idea that came in my mind. Unfortunately, the
customer needs to do "real time" search in personal data, so for
each key that's pressed, I need to query the whole table filtering
the encrypted value (1st layer of slowness) LIKE (2nd layer of
slowness) the value the user is typing. I ran a test on my i7-2660
with SSD and 16G RAM and on a 2500 rows table these queries last
about 2 seconds each keypress. So it's not the way to go, we have to
find something different. Same thing with clinical data, it would be
a mess because when I open the patient clinical record I need to see
all treatments, or all drugs that have been prescripted, all
diseases and so on, and it would be not-so fast if every clinical
data name (i.e. all drug names) is encrypted.<br>
<blockquote type="cite"
cite="mid:CAG0dhZCLGR2YB1TckUFaP5OHwDr6UdmBc7j=g0kK6jZz-2sB8Q(at)mail(dot)gmail(dot)com">
<div dir="ltr">
<div dir="ltr">
<div><br>
</div>
<div>The only other way to do it would be to store the
encrypted key value in both <a href="http://user.id"
moz-do-not-send="true">user.id</a> and
medications.user_id. That would encrypt the data and
maintain relational integrity.<br>
</div>
</div>
</div>
</blockquote>
Hmmm... if user.id and medications.user_id are the same, I can link
user with medication... and GDPR rule does not apply..... or am I
missing something?<br>
<blockquote type="cite"
cite="mid:CAG0dhZCLGR2YB1TckUFaP5OHwDr6UdmBc7j=g0kK6jZz-2sB8Q(at)mail(dot)gmail(dot)com">
<div dir="ltr">
<div dir="ltr">
<div><br>
</div>
<div>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.</div>
</div>
</div>
</blockquote>
GDPR is almost the same concept, even if some rules may differ<br>
<blockquote type="cite"
cite="mid:CAG0dhZCLGR2YB1TckUFaP5OHwDr6UdmBc7j=g0kK6jZz-2sB8Q(at)mail(dot)gmail(dot)com">
<div dir="ltr">
<div dir="ltr">
<div><br>
</div>
<div>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.<br>
</div>
</div>
</div>
</blockquote>
You sure? :-)<br>
<blockquote type="cite"
cite="mid:CAG0dhZCLGR2YB1TckUFaP5OHwDr6UdmBc7j=g0kK6jZz-2sB8Q(at)mail(dot)gmail(dot)com">
<div dir="ltr">
<div dir="ltr">
<div>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.<br>
</div>
</div>
</div>
</blockquote>
Profiling, IMHO, has to be designed in application, here I'm trying
to find a way so <u>nobody</u> can, without the use of the
application, match a patient with their clinical records (i.e.
someone breaking into the server -- data breach)<br>
<blockquote type="cite"
cite="mid:CAG0dhZCLGR2YB1TckUFaP5OHwDr6UdmBc7j=g0kK6jZz-2sB8Q(at)mail(dot)gmail(dot)com">
<div dir="ltr">
<div dir="ltr">
<div><br>
</div>
<div>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.</div>
</div>
</div>
</blockquote>
It is a system generated ID, obviously the query is more logical and
quicker if i look from master into detail, so I SELECT something
FROM medications WHERE medications.user_id = encrypt(user.id).
Encrypting the (ex-)FK appears the best solution so far, but I'm
afraid of the consequences of losing RI.<br>
<blockquote type="cite"
cite="mid:CAG0dhZCLGR2YB1TckUFaP5OHwDr6UdmBc7j=g0kK6jZz-2sB8Q(at)mail(dot)gmail(dot)com">
<div dir="ltr">
<div dir="ltr">
<div> </div>
<div>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.<br>
</div>
</div>
</div>
</blockquote>
<p>Having everything encrypted would be a big performance hit. We
are trying to achieve best performance with the right masking. <br>
</p>
<p>Thanks</p>
<p>Moreno.-<br>
</p>
<br>
</body>
</html>