SQL Server 2016 – Dynamic Data Masking

Kontynuujemy naszą serię na temat nowości w najnowszej wersji SQL Server 2016. Bohaterem dzisiejszego artykułu jest technologia Dynamic Data Masking pozwalająca na ukrywanie wrażliwych danych przed niepowołanymi osobami. technologia ta już od jakiegoś czasu jest ogólnie dostępna w ramach chmurowej bazy SQL Azure, a już od pierwszego czerwca mogą się nią cieszyć użytkownicy najnowszego SQL Server. W ramach niniejszego artykułu postaram się przedstawić schemat działania niniejszej technologii, określić możliwe scenariusze użycia oraz praktycznie pokazać jak tę technologię zaimplementować – zaczynajmy!

Większość z nas pracuje z różnego rodzaju bazami danych. Bazy te przechowują najróżniejsze dane od danych produkcyjnych, przez dane handlowe, aż po dane finansowe. Wspólnym mianownikiem łączącym każdą bazę danych jest fakt, iż znajdują się w niej dane o szczególnej wrażliwości, które same w sobie powinny być zabezpieczone przed dostępem osób trzecich. Istnieje bardzo dużo sposobów na radzenie sobie z tego typu sytuacjami – można zaimplementować algorytmy szyfrujące bądź też po prostu przerzucić sposób wyświetlania danych na aplikację. Alternatywę dla tego typu rozwiązań możemy znaleźć w najnowszej implementacji SQL Server czyli wspomniane Dynamic Data Masking. Technologia ta pozwala na całkowite lub częściowe ukrycie danych według określonych zasad w ramach konkretnej kolumny w tabeli przy niemal całkowitej transparentności dla istniejących aplikacji. Jak to zrobić? Sprawdźmy to na konkretnym przykładzie.

Na samym początku tradycyjnie stworzymy sobie testową bazę danych na potrzeby naszej demonstracji.

CREATE DATABASE [DynamicDataMaskingDemo]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'DynamicDataMaskingDemo', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL16\MSSQL\DATA\DynamicDataMaskingDemo.mdf' , SIZE = 102400KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'DynamicDataMaskingDemo_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL16\MSSQL\DATA\DynamicDataMaskingDemo_log.ldf' , SIZE = 16384KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
GO

Następnie stworzymy sobie testowego użytkownika, którego będziemy używać w celu zilustrowania różnego poziomu dostępu do naszych danych. Użytkownik ten będzie miał możliwość odczytywania danych w ramach naszej testowej bazy danych.

CREATE LOGIN testUser WITH PASSWORD=N'zaq1@WSX', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [DynamicDataMaskingDemo]
GO
CREATE USER [testUser]
GO
USE [DynamicDataMaskingDemo]
GO
ALTER ROLE [db_datareader] ADD MEMBER [testUser]
GO

W kolejnym kroku stworzymy sobie testową tabelę w ramach nowopowstałej bazy danych i wstawimy do niej pojedynczy wiersz zawierający teoretycznie wrażliwe dane:

CREATE TABLE dbo.PersonalData
(
ID INT IDENTITY(1,1) PRIMARY KEY,
FirstName nvarchar(30) NULL,
LastName nvarchar(50) NULL,
EmailAddress nvarchar(60) NULL,
Phone varchar(20) NULL,
SecurityNumber BIGINT NULL,
AccountNumber nvarchar(30) NULL
)
GO

INSERT INTO dbo.PersonalData
VALUES
(
N'Jan',
N'Kowalski',
N'jan.kowalski@outlook.com',
N'665665665',
123456789,
N'398754231232131231321'
)
GO

W tym miejscu przechodzimy do meritum niniejszego artykułu i włączymy mechanizm Dynamic Data Masking dla kolumny AccountNumber.

USE [DynamicDataMaskingDemo]
GO

ALTER TABLE dbo.PersonalData
ALTER COLUMN AccountNumber ADD MASKED WITH (FUNCTION ='default()')
GO

Jak można zauważyć powyżej do włączenia Dynamic Data Masking należy użyć komendy ALTER TABLE.. ALTER COLUMN. Następnie należy wskazać, iż chcemy włączyć maskę danych (ADD MASKED WITH) oraz wskazać funkcję, której chcemy użyć do zamaskowania naszych danych. W powyższym przykładzie użyliśmy funkcji o nazwie default(), która zamaskuje nasze dane w zależności od typu danych jaki ma dana kolumna.

Aby przetestować działanie Dynamic Data Masking stwórzmy procedurę, która będzie uruchamiana jako wcześniej utworzony użytkownik  TestUser.

CREATE PROC dbo.usp_GetPersonalData
WITH EXECUTE AS 'TestUser'
AS
SELECT 
	FirstName
	,LastName 
	,EmailAddress 
	,Phone
	,SecurityNumber
	,AccountNumber 
FROM 
	dbo.PersonalData

Następnie wykonajmy powyższą procedurę i sprawdźmy co otrzymamy w rezultacie.

EXEC dbo.usp_GetPersonalData

Jak można zauważyć dane zostały zamaskowane znakami “x” tak więc testUser może odpytać dane ale nie jest w stanie odczytać ich konkretnej wartości. Co natomiast jeżeli uruchomimy zapytanie naszym bieżącym użytkownikiem będącym sysadminem na serwerze? Sprawdźmy to!

SELECT 
	FirstName
	,LastName 
	,EmailAddress 
	,Phone
	,SecurityNumber
	,AccountNumber 
FROM 
	dbo.PersonalData

Jak widać sysadmin sam w sobie posiada uprawnienia do odczytania niezamaskowanych danych. Spróbujmy taki przywilej nadać naszemu testowemu użytkownikowi. Wraz z technologią Dynamic Data Masking dostaliśmy uprawnienie UNMASK, które możemy nadać poszczególnym użytkownikom – zróbmy to i wywołajmy procedurę w celu weryfikacji.

GRANT UNMASK TO TestUser
GO
EXEC dbo.usp_GetPersonalData

Jak widać uprawnienie zostało nadane poprawnie i testUser może bez problemu odczytywać dane. Ale czy wyświetlanie “x” to jedyna możliwość opisywanego mechanizmu? Oczywiście nie – mamy do dyspozycji , kilka funkcji maskujących, które przedstawię poniżej – zanim to zrobię musimy odebrać uprawnienia, które dopiero co nadaliśmy.

REVOKE UNMASK TO TestUSer

Kolejną funkcją, którą się zajmiemy jest email(), która przeznaczona jest do szyfrowania adresów poczty elektronicznej.

ALTER TABLE dbo.PersonalData
ALTER COLUMN EmailAddress ADD MASKED WITH (FUNCTION ='email()')
GO

EXEC dbo.usp_GetPersonalData

Jak widać, funkcja email() ukryła nazwę adresu email zostawiając widoczne jedynie pierwszą literę i znak @. Spośród dostępnych funckji mamy również partial() umożliwiającą uzyskanie niestandardowego maskowania. Poniższy przykład zwróci dla pola Phone jedynie pierwsze 3 cyfry  następnie znak “X” i ostatnią cyfrę danego numeru telefonu.

ALTER TABLE dbo.PersonalData
ALTER COLUMN PHONE ADD MASKED WITH (FUNCTION ='partial(3,"X",1)')
GO

Ostatnią wbudowaną funkcją Dynamic Data Masking jest funkcja o znajomo brzmiącej nazwie random(), która maskuje dane pole poprzez podstawienie losowego numeru z podanego przez nas zakresu. Poniżej funkcja wybrała liczbę 44518 z zakresu od 10000 do 100000 i podstawiła ją jako maskę dla pola SecurityNumber.

ALTER TABLE dbo.PersonalData
ALTER COLUMN SecurityNumber ADD MASKED WITH (FUNCTION ='random(10000,100000)')
GO

To by było na tyle jeśli chodzi o przygotowane przez Microsoft funkcje związane z Dynamic Data Masking. Myślę, że w większości przypadków podane cztery funkcje w zupełności wystarczą i spełnią określone wymagania biznesowe. Usuwanie maski z kolumny odbywa się również w mało skomplikowany sposób – wystarczy wykonać odpowiednie zapytanie typu DDL tak jak zostało to przedstawione poniżej:

ALTER TABLE dbo.PersonalData
ALTER COLUMN SecurityNumber DROP MASKED 
GO

Otwarta pozostaje kwestia monitorowania, które kolumny mają zawartą w sobię maskę, a które nie – gdyż nie jesteśmy w stanie wizualnie ocenić czy maska została zastosowana czy też nie. Oczywiście istniejące widoki systemowe zostały zaktualizowane tak aby takiej informacji nam dostarczać. Poniższe zapytanie zwróci nam nazwę kolumny w ramach konkretnej tabeli, która została zamaskowana oraz dodatkowo nazwę funkcji użytej do jej zamaskowania.

SELECT
	c.name AS ColumnName,
	c.is_masked AS IsMasked,
	c.masking_function AS MaskingFunction,
	t.name as TableName
FROM
	sys.masked_columns C
JOIN
	sys.tables AS T
ON
	C.object_id=T.object_id
WHERE
	is_masked=1
GO

Dzięki temu możemy monitorować, gdzie i na jakich zasadach założono funkcję maskującą. Wielu z wam może przyjść do głowy pytanie – czy nie ma możliwości obejścia tego mechanizmu np. poprzez wstawienie danych do tabeli tymczasowej? Myślę, że najlepszą odpowiedzią na to pytanie będzie sprawdzenie tego faktu. Stwórzmy sobie kolejną funkcję, która pobierze dane z tabeli jako testUser nie mający prawa do odczytu niezamaskowanych danych i wstawi do globalnej tabeli tymczasowej:

CREATE PROC dbo.CopyMaskedData
WITH EXECUTE AS 'TestUser'
AS
IF EXISTS (SELECT * FROM tempdb.sys.tables where name='##TempWithMaskedData')
BEGIN
	DROP TABLE ##TempWithMaskedData
END

SELECT 
	FirstName
	,LastName 
	,EmailAddress 
	,Phone
	,SecurityNumber
	,AccountNumber 
INTO
	##TempWithMaskedData
FROM 
	dbo.PersonalData
GO

Następnie po prostu odpytajmy tą globalną tabelę zwykłym zapytaniem adhoc jako nasz bieżący użytkownik (sysadmin) mający prawa do odczytu niezamaskowanych danych:

select 	FirstName
	,LastName 
	,EmailAddress 
	,Phone
	,SecurityNumber
	,AccountNumber 
from 
	##TempWithMaskedData

Jak widać dane w tabeli tymczasowej pozostały zamaskowane – dzięki temu dane pozostają bezpieczne nawet po ich przeniesieniu do innych struktur. Jednakże nie należy zawierzać bezpieczeństwa danych temu mechanizmowi ponieważ odpowiednio napisane zapytanie SELECT może odnaleźć nasze dane nawet jeśli są one zamaskowane. Oczywiście mechanizm Dynamic Data Masking nie jest pozbawiony ograniczeń np. mechanizmu nie można użyć gdy używamy technologii FILESTREAM lub też Always Encrypted, mimo to technologia ta rozszerza nasz wachlarz możliwości co zawsze powinniśmy przyjąć z otwartymi ramionami.