MASKED WITH FUNCTION

Was tut MASKED WITH FUNCTION?

Nehmen wir einmal an, dass ihr (wie das selbstverständlich der Fall sein sollte) die Real-Namen eurer Ninjas vor dem Feind verbergen wollt. Nehmen wir weiter an, dass ihr sie aber wissen und in einer Datenbank speichern möchtet. Ich weiß, hier wird diese Annahme leicht abstrus, da natürlich jeder große Meister die Namen seiner Ninjas weiß und niemals hinterlegen würde. Doch nehmen wir dennoch einmal an, ihr wolltet sie speichern. Ihr würdet dann natürlich nichts unversucht lassen, um eure Krieger zu schützen. Und mit der dynamischen Datenmaskierung hilft euch der SQL Server ab Version 2016 dabei, die Identität eurer Krieger zu schützen.

Wie könnt ihr MASKED WITH FUNCTION verwenden?

Zunächst müssen wir uns natürlich eine Tabelle anlegen, in der wir die Stammdaten der Ninjas speichern möchten:

DROP TABLE IF EXISTS NinjaWarriors

CREATE TABLE NinjaWarriors 
(
     NinjaId int NOT NULL IDENTITY(1,1)
    ,RealFirstName varchar(250) NOT NULL
    ,NinjaName varchar(250) NOT NULL
    ,SecretPower varchar(250) NOT NULL
)

Fügen wir nun einige Datensätze ein:

INSERT INTO NinjaWarriors 
(RealFirstName, NinjaName, SecretPower)
VALUES 
('Tobi', 'Blackclaw', 'Makes beer disappear'),
('Fred', 'Lethalstain', 'Talks to whales')

Legen wir nun einen Benutzer in unserer Datenbank an, der die Ninja-Tabelle abfragen soll:

DROP USER IF EXISTS Enemy

CREATE USER Enemy WITHOUT LOGIN;
GRANT SELECT ON NinjaWarriors TO Enemy;

Das REVERT am Ende dieses Statements dient dazu, zum originalen Benutzerkontext zurückzukehren. Ändern wir nun die Tabellen-Definition um die Daten in der Tabelle zu schützen. Vom Ninja-Realnamen wollen wir dabei zunächst nur den ersten und den letzten Buchstaben zeigen:

ALTER TABLE NinjaWarriors 
ALTER COLUMN RealFirstName ADD MASKED WITH (FUNCTION = 'partial(1,"XXX",1)')

Ihr seht, dass wir die Definition der Spalte ändern ähnlich, wie wenn wir einen DEFAULT-Wert hinzufügen würden.

Fragt ihr die Tabelle nun mit eurem normalen User ab, so seht ihr nach wie vor alle Daten, die ihr in die Tabelle eingefügt hattet, doch fragt die Tabelle nun wieder mit dem Benutzer Enemy ab:

EXECUTE AS USER = 'Enemy';
SELECT * FROM NinjaWarriors;  
REVERT;

Dann seht ihr, dass die Vornamen maskiert wurden zu “Txxi” und “Fxxd”. Ähnlich wie ihr hier die “Partial”-Funktion verwendet hattet, könnt ihr noch weitere Funktionen zum Schützen eurer Daten verwenden:

ALTER TABLE NinjaWarriors
ALTER COLUMN SecretPower ADD MASKED WITH (FUNCTION = 'default()')

Wenn ihr nun als “Enemy” die Tabelle abfragt, dann seht ihr dass anstelle der Geheimkräfte eurer Ninja-Kämpfer einfach nur noch “xxxx” angezeigt wird.

Wie könnt ihr nun einem User das Recht geben, die Maskierten Daten zu sehen? Dafür gibt es die Berechtigung “umask”, die ihr einem User geben könnt. Legen wir dafür einen weiteren User an:

DROP USER IF EXISTS Friend
CREATE USER Friend WITHOUT LOGIN;
GRANT SELECT ON NinjaWarriors TO Friend;

EXECUTE AS USER = 'Friend';
SELECT * FROM NinjaWarriors;  
REVERT;

Euer Freund kann nun zunächst natürlich die Daten der Tabelle nicht lesen, da er ja identisch mit dem Feind-User angelegt wurde. Geben wir ihm nun das Recht, die maskierten Daten zu lesen:

GRANT UNMASK TO Friend

EXECUTE AS USER = 'Friend';
SELECT * FROM NinjaWarriors;  
REVERT;

Der Benutzer kann nun wieder alle Daten der Tabelle sehen. Wenn ihr nun die Maskierung einer Tabelle rückgängig machen möchtet, so könnt ihr die MASKED-Eigenschaft per DROP löschen:

ALTER TABLE NinjaWarriors
ALTER COLUMN SecretPower DROP MASKED

EXECUTE AS USER = 'Enemy';
SELECT * FROM NinjaWarriors;  
REVERT;

Der Feind kann jetzt wieder alle geheimen Fähigkeiten lesen, die Namen sind aber noch immer für ihn maskiert.

Nun könnte der Feind ja versuchen, clever zu sein und die Maskierung zu umgehen und den SQL Server dazu bringen, die unmaskierten Daten in eine andere Tabelle zu schreiben, wo er sie abrufen kann:

EXECUTE AS USER = 'Enemy';
SELECT * INTO #WantToKnow FROM NinjaWarriors;  
REVERT;

Doch auch hier wird der Feind eine Enttäuschung erleben, denn der SQL Server ist intelligent genug, um zu merken, dass der User Enemy die Daten nicht lesen darf und speichert daher in der neuen Tabelle auch nur die maskierten Daten.

Merkt euch: der Ninja behält immer seine Maske auf, auch in der Datenbank…

Um herauszufinden, welche Spalten in welchen Tabellen eurer Datenbank maskiert sind, könnt ihr übrigens folgende Abfrage verwenden:

SELECT 
     c.name
    ,tbl.name as table_name
    ,c.is_masked
    ,c.masking_function  
FROM sys.masked_columns AS c  
JOIN sys.tables AS tbl   
    ON c.[object_id] = tbl.[object_id]  
WHERE is_masked = 1;  

References

• Dokumentation von Microsoft