SelectWhereQuery auf verschlüsselten Spalten durch Methodik beschleunigen

[UntoterGeist]

Gegeben ist: Eine Tabelle mit Personendaten, wobei die Spalten mit AES 128 verschlüsselt sind, die zur Identifikation verwendet werden könnten. Verknüpfte Daten aus anderen Tabellen spielen erstmal keine wesentliche Rolle. DBMS ist postgres und den Schlüssel kennt nur der Anwender und ist nur lokal oder im LAN verfügbar. Das Problem ist aber eigentlich allgemeiner Natur. Falls es überhaupt eine Lösungsweg gibt. Gefunden hab ich keinen richtigen.

Der Query ist reichlich simple: Abfrage einer Person dessen Name "beliebige Zeichenfolge" enthalten könnte. Also Where .. ILike .. Eine Optimierung Aufgrund der Einfachheit praktisch nicht möglich.

Ergebnis: Eine unerträglich lange Wartezeit auf das Ergebnis. Aber man kann es technisch machen.

Ursache: Jede einzelne Zeile muss entschlüsselt werden bevor sie verglichen werden kann.

Soweit sogut. Das war ja zu erwarten.

Nur die Anforderungen aus der Cybersecurity an Datensicherheit und Verfügbarkeit wiedersprechen sich da. Auf der einen Seite sollen Personenbezogene Daten geschützt sein (gut), und sie dürfen nur von berechtigten Personen gelesen werden (der technische Service, DBA oder sonstwer gehört nicht dazu), auf der anderen Seite soll es auch nicht ewig dauern bis man eine bestimmte Person findet, wenn man sie suchen sollte (nachvollziehbar). Wenn man jetzt 15.000 Personen in der Datenbank hat, was nicht wirklich viel ist, dann dauert ein Full Table Scan nach Liesschen Müller gefühlt ewig, was sonst ca. 100 ms dauern würde. Falls nicht vorher schon ein Query Timeout kommt.

Aufwand für die Umsetzung der Anforderungen ist kein Argument, sagen die die das Fordern. Dass die Security Anforderungen im medizinischen Bereich strenger gehandhabt werden sollen, ist eigentlich nicht so neu und es wird sich zeigen, was die Praxis davon überhaupt realistisch umsetzen kann. Für Legacy Software im medizinischen Bereich ist aber das Ende schon geplant. Ob es durchführbar ist oder es immer wieder Fristverlängerungen für diese gibt, ist eine andere Sache.

Jetzt müsste man mal innovativ sein.

Bei meiner Recherche hab ich eine Arbeit gefunden die sich genau damit beschäftigt hat. Also mit Personendatenbanken in medizinischen Anwendungen in denen die sensitiven Daten verschlüsselt werden müssen. Also Namen, Adressen, Befunde usw. Allerdings haben die primär in ihren Testqueries immer mit IDS gesucht. Was natürlich deutlich schneller ist. Das finden und entschlüsseln eines Datensatzes geht dann fast so schnell wie jeder andere Query. Leider ist es nicht wirklich nur ein worstcase Scenario nach einer beliebigen Person über Namensbestandteile suchen zu wollen. Es ist einfach nicht sichergestellt, dass die Software eine nehmen wir mal an in der DB nicht verschlüsselte PatientenID + PatientenDaten aus der Praxis EDV erhält (über wahrscheinlich ungesicherte Kanäle). Die Software muss trotzdem das Arbeiten ermöglichen. Kein Befund = Schaden für Patient.

Eine Lösungsstrategie die ich gefunden habe war ein Index von Hashwerten zu erstellen. Also Teilstrings zu hashen und für einen Vergleich in einer Tabelle pflegen. Allerdings kann man sich den Aufwand sparen. Ein Buchstabe (den man auch direkt in der Tabelle vorhalten kann) als Index reduziert die zwar Suchergebnisse vielleicht auf 1/15 je nach Buchstabe und Häufigkeit. Das wäre ja schon ganz gut. Aber man könnte auch gleich ein Entschlüsselungswörterbuch neben die Hashwerte legen in der alle Lösungen drin stehen, wenn man sowieso das ganze Alphabet und eventuell Kombinationen gehasht speichern muss damit es funktioniert. Kann man sich das Hashen eigentlich auch sparen und die Anfangsbuchstaben als Klartext ablegen. Da stellt sich dann aber schon die Frage: Weicht man damit dann schon die Verschlüsselung soweit auf das es nicht mehr akzeptabel ist?

Leider waren das schon alle Sachen die ich zu praktischen Lösungsansätzen gefunden habe, wie man das umsetzen könnte. Ansonsten findet man nur die Forderungen was man alles machen muss, wenn man aktuell eine Neuzulassung möchte. Und das ist sehr viel. Wenn es danach geht, würde jede Hausarztpraxis nach innen und außen Fortnox gleichen, was faktisch nicht der Fall ist. Cloud Dienste müssen das ja auch irgendwie umsetzen. Außer sie tun es einfach nicht weil sie eh im Rechenzentrum stehen und sagen ist gesichert.

Den Server an sich zu verschlüsseln schützt die Daten auch nicht vor unberechtigtem Personal. Der DBA muss nun wirklich nicht wissen wer alles Depressionen und Psychosen hat um seine Arbeit machen zu können. Bzw. wären die Daten auch nur dann sicher wenn er auch ausgeschaltet wird.

Es stellt sich mir auch die Frage wozu ich einfach keine Antwort gefunden habe: Was dauert länger viele kleine Texte einzeln entschlüsseln oder die Summe aller Texte einmalig entschlüsseln. Habe das Gefühl wenn ich es ausprobiere komme ich zumindest ansatzweise schneller zur Antwort als wenn ich nach einer Grafik oder so suche.

Leider bringt auch Limit oder Fetch nicht die Lösung. Im schlimmsten Fall sucht er dann doch bis zur letzten Zeile um Zacharias Zander in der gut sortierten Tabelle zu finden. Komischerweise haben die in ihrer Arbeit (ob das Diplom oder sonstwas war weiß ich nicht mehr) geschrieben das sie ihre Queries auf 5 Ergebnisse limitiert haben. <.<

Aber wenn ein Query jetzt 40 Sekunden dauert, ist das eigentlich keine zumutbare Lösung für die Praxis. Kann man eigentlich nur mit vorhergehender Warnung ausführen. Muss der Doc dann halt die PatientenID aufschreiben und mit in den Behandlungsraum nehmen, wenn er sich die nicht merken kann. Im wesentlichen darf man wohl zumindest diese Nummer nicht verschlüsseln. Sonst hat man nicht mal im automatisierten Fall der Patietenzuweisung eine Chance.

Ich glaub ich hab immer nur ganz komplizierte oder als unlösbar deklarierte Probleme.

[Blup]

Die Frage ist ja in erster Linie wer auf welche entschlüsselten Daten Zugriff hat.
Die Mitarbeiter, die mit der Software suchen wollen, hätten damit zumindest Zugriff auf alle Namen der Patienten.
Wenn das zulässig und gewünscht ist, kann man diese Daten (ID + Name) von allen Patienten zusätzlich gemeinsam in einem Blobfeld speichern.
Bei der Suche wird nur dieser eine Blob entschlüsselt und einfach der enthaltene Text durchsucht.
Das geht wesentlich schneller als tausende von Feldern zu entschlüsseln.
Der Aufwand bei Hinzufügen, Ändern oder Löschen von Patienten ist auch nicht gross.

[Jumpy]

Alternativ könnte man die minimalen Daten (ID,Vorname,Nachname,Geburtsdatum (was anderes bin ich noch nie als Patient gefragt worden, wenn die mich beim Arzt in ihrer Software suchen wollten)) auch beim Programmstart in eine geeignete Liste laden, die man dann selber durchsucht und wo man dann eine Liste von passenden IDs bekommt, für die man dann den Rest der Daten aus der DB laden kann.

[jobo]

Was bedeutet denn "Schlüssel kennt nur der Anwender"? Tippt der den ein? Liegt er in der Anwendung, ..?

Ich nehme an Du lädst alles vollständig runter und entschlüsselst in einer Schleife? (Sonst würde es nicht so lange dauern)

Kannst Du den Schlüssel an den Server schicken, um dort zu suchen? An eine SP? Ist die Verbind selbst sicher? Ist es erlaubt?

[UntoterGeist]

Es müssen insgesamt natürlich alle Aspekte der Sicherheit auf ihr Risiko bewertet werden, so auch Schlüsselmanagement und Verbindungssicherheit sowie weitere die alle ihre Schwierigkeiten mit sich bringen. Wenn man alle Aspekte gleichzeitig betrachtet, dann verzettelt man sich. Für die Machbarkeit oder Fragestellung ist erstmal nur der Aspekt wichtig, ob man mit pgcrypto schutzwürdige Daten verschlüsselt speichern und on Demand verarbeiten kann. Was ja technisch geht. Nur die Verfügbarkeit ist in einigen Fällen nicht angemessen, wenn man beispielsweise suchen muss. Das Problem haben aber alle DBMS die Spaltenweise verschlüsseln und Entwickler die irgendwie Personendaten in Kombination mit sensiblen Daten speichern wollen.

Die Daten werden mit der Funktion pg_sym_decrypt(Spalte,Schlüssel) vom DBMS ver- und entschlüsselt. Ich lade also nicht alles runter, sondern nur die QueryErgebnisse. Der Schlüssel liegt also nur in der Hand des Benutzers.

Der Testquery sieht so aus und dauert bei 15.000 Datensätzen 15 Sekunden (die Suche über 3 Spalten dauert 45 Sekunden):

Code:

SELECT "id" FROM "schema"."personen" WHERE pg_sym_decrypt("name",'geheimerSchlüssel') ILIKE 'Albert%';

Der Query bei 15.000 Datensätzen dauert ca. 200 ms

Code:

SELECT "id","name","vorname" FROM "schema"."personen";

Bei dem Vorschlag von Blub (die Spalten in einer Suchspalte zusammenzufassen) kann ich mir gut vorstellen, dass wenn man in vielen Spalten gleichzeitig suchen möchte, dass das die Rechenzeit enorm reduziert weil er seine 10 Runden nur einmal durchführen muss.

Wenn man diese Suchspalte wie Jumpy sagt beim Programmstart in eine TStringList lädt, kann man sich für die Suche die Zeit des Entschlüsselns 1ms pro Datensatz sparen. Das wirft dann aber evtl. wieder andere Fragen auf. Mit mehreren Clientsinstanzen müsste man diese Liste dann vor Verwendung mit der DB Syncronisieren. Das könnte man aber z.B. mit Count() machen. Eigentlich heißt es auch in den Anforderungen die Daten sollten nur während der Verwendung entschlüsselt sein. Ist die Frage, ob man das Risiko die Daten im Speicher vorzuhalten eingehen möchte bzw. Begründen kann. Letztendlich muss man eine Balance finden zwischen Sicherheit und Verfügbarkeit.

[swestner]

Und warum nicht einfach der DB das Verschlüsseln / Entschlüsseln überlassen?

https://www.postgresql.org/docs/8.1/...n-options.html

Wir machen das mit Firebird so und das funktioniert alles wunderbar...

[UntoterGeist]

Die Funktion wird doch auch von der DB ausgeführt. Die benutzt man wie jede andere Funktion, die von der DB zur Verfügung gestellt wird. pg_sym_decrypt ist eine Funktion von pgcrypto. pgcrypto ist ein offizielles ExtensionModul von postgres und standardmäßig bei der Installation dabei. Steht auch in deinem Link drin.

Zitat:

The /contrib function library pgcrypto allows certain fields to be stored encrypted. This is useful if only some of the data is sensitive. The client supplies the decryption key and the data is decrypted on the server and then sent to the client.

[Blup]

Eine Suchspalte ist bei der Suche über mehrere Spalten oder Subtabellen auch eine Möglichkeit der Optimierung.

Aber das war nicht mein Vorschlag.
Ich würde alle Daten der relevanten Spalten als Ergebnis noch einmal speichern.
Einmal abfragen und entschlüsseln:

Code:

Select id, name, vorname from patienten

Dann eine zusätzliche Tabelle mit einem Blobfeld und nur einem Datensatz wo das Ergebnis der Abfrage z.B. als CSV wieder verschlüsselt abgelegt wird.
Dieses Blobfeld muss bei jeder Änderung der orginalen Tabelle aktualisiert werden, in der selben Transaktion die auch den Patienten speichert.
(lesen, entschlüsssln, geänderte Zeile austauschen, verschlüsseln, speichern)
Im Prinzip eine materialisierte View.
Auf diese Weise braucht der Schlüssel den Client nie verlassen.
Bei 15.000 Patienten sollte dieser Blob etwa 1MB groß sein, da dürfte das Entschlüsseln kaum spürbar verzögern.

[Uwe Raabe]

Ich konnte jetzt keine Aussage dazu finden, aber das hast du sicher schon versucht?

SQL-Code:

SELECT "id","name","vorname" FROM "schema"."personen" WHERE "name" ILIKE 'Albert%';

[UntoterGeist]

@Blup
Achso war das gemeint, jetzt versteh ich deinen Ansatz.

@Uwe
Die relevanten Spalten haben den Datentyp bytea und sind durch die DB verschlüsselt worden. Man kann also nicht direkt auf die Spalten ILike anwenden. Man muss die Spalte schon vor dem Vergleich entschlüsseln lassen. Sonst sucht man (wenn man das in Text umcastet) nur in Datensalat herum. Die Datenbank macht das also nicht automatisch, kann sie auch garnicht, weil sie den Schlüssel dann nicht hat. Bei Oracle ist der Masterkey z.B. in der Datenbank hinterlegt. An dem Rechenaufwand ändert sich für Oracle dadurch aber auch nichts.

Da AES nicht homomorph ist kann man keine Suchoperationen auf den Verschlüsselten Text durchführen. Homomorphe Verschlüsselung ist aber glaube auch weniger sicher, weil die Strukturen erhalten bleiben. So wird das jedenfalls beschrieben. Bei diesen Verschlüsselungen könnte man den Suchtext verschlüsseln und das Ergebnis direktin den Spalten suchen.

Deshalb muss man das so schreiben:

Code:

SELECT
"id",
pgp_sym_decrypt("name",'geheimerSchlüssel') AS name,
pgp_sym_decrypt("vorname",'geheimerSchlüssel') AS vorname

FROM "schema"."personen"

WHERE pgp_sym_decrypt("name",'geheimerSchlüssel') ILIKE 'Albert%';

Postgres ist übrigens so schlau, das Ergebnis einer Spalte die schon entschlüsselt wurde zwischen zu speichern. Deshalb kostet der folgende Query nicht wirklich mehr Zeit als der vorige.

Code:

SELECT
"id",
pgp_sym_decrypt("name",'geheimerSchlüssel') AS name,
pgp_sym_decrypt("vorname",'geheimerSchlüssel') AS vorname

FROM "schema"."personen" WHERE pgp_sym_decrypt("name",'geheimerSchlüssel') ILIKE 'Albert%'
OR "schema"."personen" WHERE pgp_sym_decrypt("name",'geheimerSchlüssel') ILIKE 'Peter%'
OR "schema"."personen" WHERE pgp_sym_decrypt("name",'geheimerSchlüssel') ILIKE 'Daniel%';