Schritt für Schritt: SQL 2016 – Dynamic Data Masking


Es weihnachtet! Gerade bekam ich von einer Kollegin Plätzchen angeboten mit der Größe eines Diskus und ca 2,5 Milliarden Kalorien. Ich sehe schon das Hüftgold anrollen, oder wie die Franzosen es liebevoll nennen: „poignées d’amour“. Der Brite übrigens love handles. Was mich zu dem Schluss bringt, dass wir in good old germany scheinbar ein Problem mit unseren Körperzonen haben.

Solche Problemzonen gibt’s aber nicht nur zu den Feiertagen und auch nicht nur um die Hüfte. Im komkreten Fall spreche ich vom Hüftspeck – Marke „geheime Daten“.

Bisher war es gelinde gesagt, etwas umständlich Leseberechtigten den Zugriff auf bestimmte Spalten zu nehmen oder die Daten zu verschlüsseln. Mit SQL Server 2016 (CTP 3.1) wird die Geschichte schon deutlich einfacher.

Wir wäre es denn, wenn wir ohne zusätzlichen Code, die Daten – je nach Berechtigung – verschlüsselt oder unverschlüsselt sehen könnten? Das hört sich für mich nach einem ziemlich coolen Securityfeature an dem Dynamic Data Masking – kurz DDM.

Maskierfunktionen

SQL Server 2016 stellt für DDM folgende Funktionen zur Verfügung:

  • Default():
    Gibt bei Textdatentypen entweder XXXX aus oder weniger, falls das Feld eine geringer Größe aufweist

    Bei numerischen Daten wird eine 0 ausgegeben.

    Bei Datumsfeldern das entgegen der Doku der 1.1.1900

  • Email():
    maskiert Emailadressen in folgender Form: [email protected]. Lediglich der erste Buchstabe der Emailadresse wird korrekt ausgegeben, der Rest wird „ausgext“ und mit der Domäne .com versehen
  • Random():
    Für numerische Datentypen eine Zufallsmaskierung, bei der Zufallszahlen innerhalb des Angegeben Start- und Endwertes zurückgegeben werden
  • Custom():
    Hier können sie ein Prä- und Suffix angeben und in welcher Form der Teil dazwischen dargstellt werden soll. Wie etwa partial(2,“XXX“,3) die ersten 2 und die letzten 3 Zeichen korrekt ausgibt. Der Teil dazwischen mit dem gewünschten string maskiert wird.

Wollten wir nicht die Problemzonen entfernen? Nun denn, wie geht’s?

Anlegen der Demotabelle

Create database DataMasking;
GO
use DataMasking;
GO

CREATE TABLE Angestellte
(AngID int IDENTITY PRIMARY KEY,

Vorname varchar(100) MASKED WITH (FUNCTION =‚partial(1,“XXXXXXX“,0)‘)NULL,
Nachname varchar(100NOT NULL,
tel varchar(12) MASKED WITH (FUNCTION =‚default()‘)NULL,
Email varchar(100) MASKED WITH (FUNCTION =‚email()‘)NULL,
Gebdatum date MASKED WITH (FUNCTION=‚default()‘)NULL,
Kennwort varchar(100) MASKED WITH (FUNCTION=‚partial(5,“XXXXXXX“,0)‘)NULL
);
GO

Arbeiten mit Dynamic Data Mask

Ich sehe die Speckröllchen schon schwinden. Sie auch? In der Tabelle wurden bereits die Maskierungen angegeben. Natürlich kann jederzeit eine maskierte/unmaskierte Spalte hinzugefügt werden.

ALTER TABLE Angestellte
ADD HireDate date MASKED WITH(FUNCTION=‚default()‘)NULL;

Oder natürlich auch eine bestehende geändert werden:

ALTER TABLE Angestellte
ADD Gehalt money MASKED WITH(FUNCTION=‚random(1,12)‘)NULL;

ALTER TABLE Angestellte
ALTER COLUMN Gehalt MASKED WITH(FUNCTION=‚default()‘)NULL;

..und wenn man möchte bekommt man sie auch wieder los

ALTER TABLE Angestellte
ALTER COLUMN DROP MASKED;

Ergebnisse. Wir wollen Ergebnisse sehen! OK .. guggst du..:

Fügen wir zunächst mal ein paar Daten ein:

INSERT Angestellte (Vorname, Nachname, Tel, Email, GebDatum, Kennwort ) VALUES
(‚Robert‘,‚Lindner‘,‚08676-9876‘,[email protected],‚5.1.2012‘,‚145@qwe‘),
(‚Hans‘,‚Blatter‘,‚089-984576‘,[email protected],‚6.6.2014‘,’sdfsf324543′),
(‚Sepp‘,‚Beckenbauer‘,‚08677-455646‘,[email protected],‚7.7.2000‘,‚flkjfd@lkdfdl‘);

Kurze Abfrage:

Haben sie etwas Anderes erwartet? Wir haben das Recht die Daten zu lesen. Für Benutzer mit reinen Leserechten sieht das schon anders aus:

Legen wir einen Benutzer MrX an und statten ihn mit Leserechte aus:

CREATE USER MrX WITHOUT LOGIN;
GRANT SELECT ON Angestellte TO MrX;
EXECUTE AS USER =‚MrX‘;

SELECT * FROM Angestellte;
REVERT;

Hier kommt der Aha-Effekt. Keine Codeänderung, nur von den Rechten des Benutzers abhängig! Super Sache. Speckröllchen ohne Diät weg. Da schmeckt mir die Schweinshaxe wieder..

Wer Recht hat, hat Recht

Natürlich können wir Benutzern das leserechte auf maskierte Daten geben.. und wieder nehmen:

–Rechte geben..

GRANT UNMASK TO MrX;
EXECUTE AS USER =‚MrX‘;
SELECT * FROM Angestellte;
REVERT;

–und Rechte wieder nehmen

REVOKE UNMASK TO Mrx;
EXECUTE AS USER =‚MrX‘;
SELECT * FROM Angestellte;
REVERT;

Und welches recht benötigt man, um GRANT UNMASK bzw REVOKE UMASK vergeben zu können?

Die notwendige Rechte dazu sind: ALTER ANY MASK und ALTER TABLE.

Plan B

Ich nehme an, dass den meisten TSQL Schreiberlingen auch die Problemzone der Funktionen bekannt sind. Nachdem eine Funktion zuständig ist, um die Daten zu maskieren, sollte doch bei Abfragen eine Suche per Index zu einem Scan führen. Das passiert allerdings tatsächlich nicht. Die Daten indiziert werden und gesucht werden. Lediglich die Ausgabe wird verschlüsselt.

Bei Tests auf ca 20 Mio Zeilen ergaben sich keine nennenswerte Unterschiede. Für testzwecke habe ich eine Datumsspalte einmal maskiert und einmal unmaskiert in der Tabelle. Auf beide Spalten wurde die gleichen Indizes angelegt. Non Clustered mit Included Columns, so dass ein Covered Index entstehen müßte.

Das Ergebnis:

In beiden Fällen, ob Benutzer mit Leserecht oder auch ohne bekommen wir einen IX Seek.

Die Statistikmessungen kommen zum selben Ergebnis:

Auch im Falle einer größeren Menge (1 MIo Ergebniszeilen) ändert daran nichts. Auch keine höhere CPU Last.

Cool… noch L

Was geht ab, Mann

Oder was geht eben nicht ab.. Mit folgenden Limits müssen wir aktuell in der CTP 3.1 leben..

Kein Support von DDM für

  • Encrypted columns (Always Encrypted)
  • FILESTREAM
  • COLUMN_SET

Ebenfalls sinnlos ist für Mrx die Daten zu kopieren. Mittels eines

SELECT into Angestellte2 FROM Angestellte;

bekommt man lediglich maskierte Daten in die Zieltabellen. Die Maskierfunktionen werden nicht übernommen.

Ein Update wird MrX zweifelsohne gelingen, aber die Daten wird er wegen der Maskierfunktion nicht lesen können.

Der „Bug“ aus früheren CTPs, dass ein Konvertieren in einen von DDM nicht unterstützten Datentyp, die Daten im Klartext anzeigt, war in der CTP 3.1 bisher nicht mehr nachzuvollziehen. Vor allem, da jetzt alle wichtigen Datentypen supported sind.

Ohje – Weiter Problemzonen

Nun schien doch alles in Ordnung zu sein? Doch genau dort wo wir hurra auf 10 Kilo weniger geschrieben haben, enau dort liegt auch der Hund begraben. (Wieso begräbt eigentlich immer den Hund?)

Schauen wir uns das mal an…:

EXECUTE AS USER =‚MrX‘;
select * from angestellte where Gehalt between 30001 and 50001
select * from angestellte where gehalt = 50000
REVERT;

select * from angestellte where Gehalt between 30001 and 50001
REVOKE UNMASK TO Mrx

Achnee.., oder? Doch! Wer fragt bekommt Antwort. Logischerweise muss es möglich sein die Daten abfragen und korrekt Ergebnisse zu bekommen. Tja das Resultat heißt hier im billigsten Fall: Mit Ausdauer und Fleiß komm ich auch an die Gehälter…

Author: Fumus

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert