AGB  ·  Datenschutz  ·  Impressum  







Anmelden
Nützliche Links
Registrieren
Zurück Delphi-PRAXiS Programmierung allgemein Datenbanken SelectWhereQuery auf verschlüsselten Spalten durch Methodik beschleunigen

SelectWhereQuery auf verschlüsselten Spalten durch Methodik beschleunigen

Ein Thema von UntoterGeist · begonnen am 14. Sep 2021 · letzter Beitrag vom 16. Okt 2021
Antwort Antwort
Seite 1 von 2  1 2   
UntoterGeist

Registriert seit: 18. Sep 2019
25 Beiträge
 
#1

SelectWhereQuery auf verschlüsselten Spalten durch Methodik beschleunigen

  Alt 14. Sep 2021, 00:41
Datenbank: postgres • Version: 13 • Zugriff über: ado
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.
  Mit Zitat antworten Zitat
Blup

Registriert seit: 7. Aug 2008
Ort: Brandenburg
1.327 Beiträge
 
Delphi 10.4 Sydney
 
#2

AW: SelectWhereQuery auf verschlüsselten Spalten durch Methodik beschleunigen

  Alt 14. Sep 2021, 10:42
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.
  Mit Zitat antworten Zitat
Jumpy

Registriert seit: 9. Dez 2010
Ort: Mönchengladbach
1.696 Beiträge
 
Delphi 6 Enterprise
 
#3

AW: SelectWhereQuery auf verschlüsselten Spalten durch Methodik beschleunigen

  Alt 14. Sep 2021, 11:53
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.
Ralph
  Mit Zitat antworten Zitat
jobo

Registriert seit: 29. Nov 2010
2.963 Beiträge
 
Delphi 2010 Enterprise
 
#4

AW: SelectWhereQuery auf verschlüsselten Spalten durch Methodik beschleunigen

  Alt 14. Sep 2021, 19:37
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?
Gruß, Jo
  Mit Zitat antworten Zitat
UntoterGeist

Registriert seit: 18. Sep 2019
25 Beiträge
 
#5

AW: SelectWhereQuery auf verschlüsselten Spalten durch Methodik beschleunigen

  Alt 15. Sep 2021, 12:10
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.
  Mit Zitat antworten Zitat
swestner

Registriert seit: 31. Aug 2012
Ort: Hallstadt
35 Beiträge
 
Delphi 10.4 Sydney
 
#6

AW: SelectWhereQuery auf verschlüsselten Spalten durch Methodik beschleunigen

  Alt 15. Sep 2021, 17:14
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...
Stefan Westner
  Mit Zitat antworten Zitat
UntoterGeist

Registriert seit: 18. Sep 2019
25 Beiträge
 
#7

AW: SelectWhereQuery auf verschlüsselten Spalten durch Methodik beschleunigen

  Alt 15. Sep 2021, 19:21
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.
  Mit Zitat antworten Zitat
Blup

Registriert seit: 7. Aug 2008
Ort: Brandenburg
1.327 Beiträge
 
Delphi 10.4 Sydney
 
#8

AW: SelectWhereQuery auf verschlüsselten Spalten durch Methodik beschleunigen

  Alt 16. Sep 2021, 09:21
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.
  Mit Zitat antworten Zitat
Benutzerbild von Uwe Raabe
Uwe Raabe

Registriert seit: 20. Jan 2006
Ort: Lübbecke
8.656 Beiträge
 
Delphi 10.4 Sydney
 
#9

AW: SelectWhereQuery auf verschlüsselten Spalten durch Methodik beschleunigen

  Alt 16. Sep 2021, 11:21
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%';
Uwe Raabe
Certified Delphi Master Developer
Embarcadero MVP
Blog: The Art of Delphi Programming
  Mit Zitat antworten Zitat
UntoterGeist

Registriert seit: 18. Sep 2019
25 Beiträge
 
#10

AW: SelectWhereQuery auf verschlüsselten Spalten durch Methodik beschleunigen

  Alt 16. Sep 2021, 13:28
@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%';
  Mit Zitat antworten Zitat
Antwort Antwort
Seite 1 von 2  1 2   

Themen-Optionen Thema durchsuchen
Thema durchsuchen:

Erweiterte Suche
Ansicht

Forumregeln

Es ist dir nicht erlaubt, neue Themen zu verfassen.
Es ist dir nicht erlaubt, auf Beiträge zu antworten.
Es ist dir nicht erlaubt, Anhänge hochzuladen.
Es ist dir nicht erlaubt, deine Beiträge zu bearbeiten.

BB-Code ist an.
Smileys sind an.
[IMG] Code ist an.
HTML-Code ist aus.
Trackbacks are an
Pingbacks are an
Refbacks are aus

Gehe zu:

Impressum · AGB · Datenschutz · Nach oben
Alle Zeitangaben in WEZ +2. Es ist jetzt 04:51 Uhr.
Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
LinkBacks Enabled by vBSEO © 2011, Crawlability, Inc.
Delphi-PRAXiS (c) 2002 - 2021 by Daniel R. Wolf