SQL Server 2016 – Row level security

Kontynuujemy serię poświęconą nowościom w SQL Server 2016. Naszym dzisiejszym bohaterem jest Row Level Security (RLS) czyli technologia pozwalająca zabezpieczać dane na poziomie wierszy – zapraszam do lektury!

Dane zawarte w bazach danych od zawsze wymagały pewnego rodzaju zabezpieczeń. Zabezpieczenia te na przestrzeni lat były coraz bardziej wyrafinowane, jednak dla wielu z nas nie było to wystarczające i musieliśmy tworzyć obejścia czy to na poziomie aplikacji czy też samej bazy danych.  Jedną z najbardziej pożądanych funkcji w świecie bazy danych SQL Server od zawsze był mechanizm zabezpieczenia danych na poziomie pojedynczego wiersza w tabeli. Jeżeli dobrze poszukacie to znajdziecie ogromną ilość próśb, narzekań i obejść związanych właśnie z tą funkcjonalnością. Zapewne również i  Wy staraliście się coś takiego zaimplementować czy to przy pomocy tradycyjnych widoków, procedur czy też na poziomie kodu aplikacji. Jednak nadszedł ten dzień i wraz z SQL Server 2016 mamy do dyspozycji tak długo wyczekiwane Row Level Security! Do tego technologia ta daje nam duże możliwości i jest bardzo prosta w implementacji .

W teorii technologia RLS ma działać w taki sposób aby umożliwiać użytkownikom w sposób transparentny odpytywać tabele – transparentność w tym przypadku polega na tym, iż użytkownicy mają zobaczyć , czy też modyfikować tylko to co jest dla nich przeznaczone. Oczywiście czym innym jest zabezpieczenie danych w ramach pojedynczej tabeli przed odczytem, a czym innym zabezpieczenie przed wstawieniem nieodpowiednich danych – na szczęście opisywana przeze mnie technologia potrafi obsłużyć obie te role o czym przekonamy się w dalszej części niniejszego artykułu.

Jak zostało przedstawione na powyższym obrazku mając dane w pojedynczej tabeli chcemy je rozgraniczyć pomiędzy dwóch użytkowników. Row Level Security pozwoliło na to aby użytkownik o loginie UserA mógł odczytać jedynie dane sprzedażowe dla Europy i Azji, a użytkownik UserB, dane sprzedażowe dla Stanów Zjednoczonych oraz Australii. Oczywiście użytkownicy sami nie przefiltrowali sobie danych – wcześniej została przygotowana odpowiednia funkcja, która mapuje ich login do odpowiedniego pola w bazie danych – jak to zostało zaimplementowane? Sprawdźmy na konkretnym przykładzie.

Tradycyjnie na samym początku stwórzmy sobie testową bazę danych, w której będziemy wykonywać nasze skrypty.

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

USE RowLevelSecurityDemo
GO

Następnie stwórzmy sobie tabelę z zamówieniami, do których użytkownicy będą mieli ograniczony dostęp.

USE RowLevelSecurityDemo
GO
CREATE TABLE dbo.Orders  
    (  
    OrderID int,  
    SalesRepresentative sysname,  
    ProductName varchar(10),  
    Quantity int,
    OrderDate datetime  
    );

Musimy również stworzyć samych użytkowników, którzy posłużą nam jako użytkownicy biznesowi o różnym poziomie dostępu do danych. Dwaj nowi użytkownicy będą przedstawicielami handlowymi (SalesEU, SalesUS), którzy mają dostęp do zamówień ze swoich regionów oraz menedżera, który powinien mieć dostęp do wszystkich rekordów.

CREATE USER Manager WITHOUT LOGIN;  
CREATE USER SalesEU WITHOUT LOGIN;  
CREATE USER SalesUS WITHOUT LOGIN;

Następnie wprowadzimy sobie do nowopowstałej tabeli rekordy obrazujące zależności pomiędzy zamówieniami, a użytkownikami. Dodatkowo nadamy uprawnienia do pobierania danych z tej tabeli dla wszystkich trzech kont.

INSERT dbo.Orders 
(
	OrderID,
	SalesRepresentative,
	ProductName,
	Quantity,
	OrderDate
)  
VALUES 
	(1, 'SalesEU', 'Chair', 5,'20160501'),   
	(2, 'SalesEU', 'Table', 2,'20160503'),   
	(3, 'SalesUS', 'Table', 4,'20160501'),  
	(4, 'SalesUS', 'Sofa', 2,'20160512'),   
	(5, 'SalesEU', 'Table', 5,'20160611'),   
	(6, 'SalesUS', 'Chair', 5,'20160515'),
	(7, 'SalesUS', 'Chair', 5,'20160515'),
	(8, 'SalesEU', 'Chair', 5,'20160515'),
	(9, 'SalesUS', 'Chair', 5,'20160515'),
	(10, 'SalesEU', 'Chair', 5,'20160515')

GRANT SELECT ON dbo.Orders TO Manager;  
GRANT SELECT ON dbo.Orders TO SalesEU;  
GRANT SELECT ON dbo.Orders TO SalesUS; 
GO

W tym miejscu przejdziemy do implementacji Row Level Security. Pierwszym krokiem jest stworzenie funkcji tabelarycznej typu inline, która zwróci nam wiersz w przypadku gdy warunek w klauzuli where zostanie spełniony. Nie jest istotne to jaka wartość jest zwracana przez SELECT – jeżeli cokolwiek jest zwracane to SQL Server wie, że warunek jest spełniony i może zwrócić konkretny wiersz z tabeli. Nasza demonstracja opiera się na tym, że w ramach funkcji  sprawdzany jest  login przedstawiciela handlowego jako parametr  (można go utożsamić jako z kolumną SalesRepresentative w naszej tabeli), a następnie sprawdza czy podany parametr jest równy loginowi bieżącego użytkownika. W przypadku gdy login należy do menedżera zwracana jest wartość bez względu na wartość podanego parametru.

CREATE FUNCTION dbo.fn_secureOrders(@SalesRepresentative AS sysname)  
    RETURNS TABLE  
WITH SCHEMABINDING  
AS  
    RETURN SELECT 1 AS fn_securitypredicate_result   
WHERE @SalesRepresentative = USER_NAME() OR USER_NAME() = 'Manager'; 

W tym miejscu warto zwrócić uwagę, iż tworzona przez nas funkcja jest zapisana z opcją WITH SCHEMABINDING która zastrzega możliwość modyfikacji obiektów używanych w definicji tej funkcji. Bez tej klauzuli nie jest możliwa implementacja ROW LEVEL SECURITY, samo jej użycie ogranicza nas w niektórych aspektach. Często spotykanym podejściem jest przechowywanie uprawnień globalnie w ramach dedykowanej do tego celu bazie danych. W przypadku RLS takie podejście nie może być zastosowane gdyż SCHEMABIDNING działa tylko i wyłącznie w ramach pojedynczej bazy danych. W przypadku gdy posiadamy centralną bazę z uprawnieniami niestety będziemy musieli ją zreplikować do naszej bazy aplikacyjnej.

Następnym krokiem jaki musi wykonać jest przypisanie powyższej funkcji do naszej tabeli. Aby to zrobić należy stworzyć politykę zabezpieczeń (ang. Security Policy) i dodać predykat filtrujący(ang. Filter predicate), którym jest właśnie nasza funkcja. W tym miejscu widzimy, że jako parametr jest przekazywana konkretna kolumna z tabeli. Warto również zaznaczyć, iż w ramach Row Level Security mamy dwa rodzaje predykatów – wspomniany wcześniej predykat filtrujący, który odpowiada za to aby do użytkowników były zwracane odpowiednie dane. Drugim rodzajem predykatu jest predykat blokujący (ang. Block predicate) , o którym powiemy sobie niżej.

CREATE SECURITY POLICY SalesFilter  
ADD FILTER PREDICATE dbo.fn_secureOrders(SalesRepresentative)   
ON dbo.Orders 
WITH (STATE = ON);

Tworząc politykę należy pamiętać o przekazaniu klauzuli WITH (STATE=ON) aby nasze zabezpieczenia odrazu włączyć (analogicznie możemy je wyłączyć przekazując STATE=OFF). Mając już włączony opisywany mechanizm nie pozostało nam nic innego jak odpytać tabelę jako każdy z naszych trzech testowych użytkowników – zróbmy to!

Jak pierwszego użyjemy użytkownika SalesEU, który był wprost przypisany do 5 rekordów w naszej testowej tabeli.

EXECUTE AS USER = 'SalesEU';  
SELECT * FROM dbo.Orders;   
REVERT;

Jak widać mechanizm zadziałał w prawidłowy sposób i automatycznie dane zostały przefiltrowane. Analogiczna sytuacja będzie miała miejsce w przypadku wywołania zapytania jako użytkownik SalesUS – co natomiast gdy wywołamy klauzulę SELECT użytkownikiem należącym do menedżera? Teoretycznie takie zapytanie powinno zwrócić wszystkie dziesięć wierszy – sprawdźmy.

EXECUTE AS USER = 'Manager';  
SELECT * FROM dbo.Orders;   
REVERT;

Jak widać wszystko zadziałało zgodnie z przewidywaniami. Co się natomiast stanie gdy np. użytkownik SalesUS otrzyma uprawnienia do wstawiania danych i wstawi dane wraz z przypisaniem do użytkownika EU?

GRANT INSERT TO SalesUS
GO
EXECUTE AS USER = 'SalesUS';
INSERT INTO 
	dbo.orders
VALUES
	(11, 'SalesEU', 'Table', 1,'20160516')
REVERT;

Dane te oczywiście zostały wstawione – może się tutaj pojawić pytanie jak to możliwe? Jest to jak najbardziej prawidłowa sytuacja gdyż jak już wcześniej wspomniałem nasze zabezpieczenia zawierały predykat filtrujący blokujący jedynie odczyt danych. Na szczęście mamy również do dyspozycji wspomniany wcześniej  predykat blokujący uniemożliwiający wstawianie danych w analogiczny sposób do przedstawionego powyżej. Definicja tego rodzaju predykatu różni się od poprzednika jedynie innym słowem kluczowym tj. zamiast FILTER używamy BLOCK:

ALTER SECURITY POLICY SalesFilter  
ADD BLOCK PREDICATE dbo.fn_secureOrders(SalesRepresentative)   
ON dbo.Orders

Aby dodać predykat do tabeli musimy pamiętać, że tabela może mieć tylko jedną politykę bezpieczeństwa, która może zawierać więcej niż jeden predykat – tak więc musimy użyć składni ALTER SECURITY POLICY… ADD.

Po zaimplementowaniu powyższego ograniczenia wprowadzenie nieswoich danych do tabeli nie jest możliwe i zakończy się błędem:

Jeżeli chodzi o operacje DELETE i UPDATE to mechanizm zabezpieczeń działa analogicznie do SELECT gdyż te operacje opierają się właśnie na wyszukiwaniu istotnych wierszy i dopiero w dalszej części wykonują aktualizację lub też usunięcie rekordu. Jeśli potrzebujemy dostosowanej logiki, która będzie różniła się od standardowego “to co mogę wyszukać mogę też zaktualizować i usunąć” możemy przygotować specjalne predykaty blokujące dla tych operacji. Dla DELETE będzie to predykat z dopiskiem AFTER DELETE np:

ALTER SECURITY POLICY SalesFilter 
ADD BLOCK PREDICATE dbo.fn_secureOrders(SalesRepresentative) 
ON dbo.Orders
AFTER DELETE

Oczywiście możemy podpiąć inną funkcję niż ta której używaliśmy w innych predykatach. Jeśli chodzi o operację UPDATE to wiąże się ona z dwoma operacjami tj. z wyszukaniem wierszy do aktualizacji oraz z wstawieniem nowej wartości. Dlatego też tworząc predykat dla tej operacji mamy dwie możliwości tj. BEFORE UPDATE oraz AFTER UPDATE. Szczególnie użyteczny jest AFTER UPDATE, który pozwala np. na uniemożliwienie użytkownikowi przypisania swoich wierszy komuś innemu.

Opisywany mechanizm posiada również sporo wad i ograniczeń. Jednym z nich jest wspomniane wcześniej ograniczenie związane z opcją SCHEMABINDING. Inna istotną wadą czy też ograniczeniem jest również fakt, iż nie możemy objąć tym rodzajem zabezpieczenia widoków zmaterializowanych. Ponadto jak można się domyślać włączony RLS powoduje spadek wydajności każdego zapytania ze względu na fakt, iż za każdym razem musi zostać wywołana funkcja filtrująca. Można się spodziewać, iż spadek wydajności będzie tym bardziej dotkliwy, im bardziej skomplikowany będzie warunek filtrujący – tak więc najlepiej implementować jak najprostsze warunki. Ważnym odnotowania faktem jest również to, że domyślnie administratorzy czy też dbo nie mają dostępu do danych objętych mechanizmem RLS – tak więc trzeba jawnie dostęp dla tych użytkowników obsłużyć aby mogli oni wprost działać na danych lub też zadbać o właściwe dostępy do uruchamiania kodu jako inny użytkownik.

Oczywiście powyżej przedstawiłem jedynie podstawowe zastosowanie ROW LEVEL SECURITY. W ramach funkcji filtrującej możemy również użyć całej gamy zależności opartych np. na SESSION_CONTEXT i rolach aplikacji czy chociażby funkcji IS_MEMBER itp. Podobnie jak wcześniej opisywane funkcjonalności tj. Temporal Tables oraz Dynamic Data Masking opisywany mechanizm jest transparentny dla aplikacji i jego zaimplementowanie nie wymaga zmian w kodzie – wystarczy zaimplementować politykę do już istniejącej tabeli i mechanizm jest gotowy do użycia. Mimo wielu ograniczeń technologia ta dla wielu deweloperów jedną z najważniejszych nowości w SQL Server 2016. Również dla mnie technologia ta jest ważna gdyż już nie będę musiał tworzyć widoków filtrujących dla moich raportów, a jedynie zaimplementuje RLS. Mam nadzieję, że również Wam ten mechanizm uprości codzienną pracę.