SQL Server Application Roles – mało popularna funkcjonalność bezpieczenstwa

Czasem istnieje potrzeba niestandardowego podejścia do tematu nadawania uprawnień w SQL Server. Większość ludzi utożsamia kwestie dostępów do zasobów jako sekwencję kroków składającą się uwierzytelnienia opartego o Windows bądź login i hasło SQL Server, a następnie autoryzacji dostępu do określonego zasobu. Otóż jak zapewne wywnioskowaliście z tytułu i niniejszego wstępu to nie jedyne co mamy do dyspozycji. Możemy również użyć tworu o nazwie Application Roles, który mimo, że jest zaimplementowany w naszej bazie danych od bardzo dawna to nie jest szeroko znany ani tym bardziej używany. Dlaczego tak mówię? Ponieważ mimo, iż widziałem dosyć dużo różnych implementacji baz danych opartych o SQL Server to nigdy nie było mi dane zobaczyć ról aplikacyjnych w akcji – być może moja próbka nie była wystarczająca do tego aby wnioskować na temat całej populacji – jednakże ta obserwacja dała mi pretekst do napisania tego artykułu więc czuje się usprawiedliwiony:)

Role aplikacyjne z koncepcyjnego punktu widzenia dają nam możliwość nadawania określonych uprawnień nie dla użytkownika, a dla aplikacji która się z bazą danych łączy. Przypuśćmy, że chcemy aby użytkownicy mieli dostęp do bazy danych ale tylko za pośrednictwem aplikacji, którą dla nich przygotowaliśmy – wtedy też możemy przygotować właśnie rolę aplikacyjną – jak to zrobić? Zapraszam do zapoznania się z niniejszym tekstem.

Po przeczytaniu powyższego wprowadzenia możecie po krótkim zastanowieniu znaleźć rozwiązanie przedstawionego problemu w postaci dedykowanego dla aplikacji konta SQL Server – jednakże to podejście ma swoje minusy. Przede wszystkim w takim przypadku musimy użyć uwierzytelnienia SQL Server, a po drugie tracimy możliwość identyfikacji tego kto się aktualnie zalogował. Te ograniczenia można eliminować korzystając właśnie z opisywanej funkcjonalności.

Role aplikacyjne z technicznego punktu widzenia niewiele różnią się od typowych ról bazodanowych. Pierwszym faktem jaki warto zapamiętać jest to, że role aplikacyjne są elementem zabezpieczeń baz danych, a nie instancji SQL Server. Aby się do niej dostać wystarczy rozwinąć węzeł Security -> Roles określonej bazy danych:

Czym zatem różni się ten typ roli od standardowej roli bazodanowej? Przekonamy się w dalszej części artykułu – na ten moment stwórzmy sobie przykładową rolę w bazie WideWorldImporters. W tym przykładzie użyjemy interfejsu graficznego klikając prawym przyciskiem myszy na Application Roles i z menu kontekstowego wybierając New Application Role – naszym oczom powinno ukazać się okno podobne do poniższego:

Na pierwszy rzut oka mamy kilka nowości w porównaniu do tradycyjnych ról. Przede wszystkim musimy podać hasło… zaraz zaraz – jak to możliwe, że rola ma hasło? Przecież to użytkownik ma hasło! To stwierdzenie jest prawdziwe ale tylko w tradycyjnym podejściu. Rola aplikacyjna posiada hasło ze względu na fakt, iż w kodzie aplikacji możemy włączyć kontekst bezpieczeństwa związany z tą właśnie rolą używając hasła. Dzięki temu można dla aplikacji przypisać określone prawa do zasobów- bez konieczności nadawania uprawnień dla każdego użytkownika, który z danej aplikacji korzysta. Podejście to ma jeszcze jedną unikalną zaletę – chodzi mianowicie o fakt, że w przypadku jakbyśmy dodali użytkownikowi dostęp do bazy to mógłby on dostać się do niej nie tylko przez naszą aplikację, a również przez każde inne narzędzie jak np. Excel. Takie zachowanie nie zawsze jest pożądane – rozwiązaniem tego problemu jest właśnie opisywane funkcjonalność. W przypadku ról aplikacyjnych niejako pomijamy użytkowników – co również możecie zauważyć w powyższym oknie dialogowym, gdzie nie ma możliwości przypisania żadnego użytkownika – nie jest to potrzebne – po poprawnym wywołaniu roli z poziomu aplikacji kontekst bezpieczeństwa automatycznie przełącza się na tą właśnie rolę.

Na ten moment stwórzmy sobie rolę TestAppRole i nadajmy jej uprawnienie do odczytu tabel ze schematu Application – możemy to zrobić używając wywołanego wcześniej okna dialogowego lub też używając poniższej składni:

USE [WideWorldImporters]
GO
CREATE APPLICATION ROLE [TestAppRole] WITH DEFAULT_SCHEMA = [dbo], PASSWORD = N'zaq1@WSX'
GO
use [WideWorldImporters]
GO
GRANT SELECT ON SCHEMA::[Application] TO [TestAppRole]
GO

Teraz przełączmy nasz kontekst na tę rolę – możemy to zrobić używając specjalnej procedury składowanej o nazwie sp_setapprole, która za pierwszy parametr przyjmuje nazwę roli aplikacyjnej, a za drugi jej hasło:

sp_setapprole 'TestAppRole','zaq1@WSX'

Oczywiście taki zapis będzie przesyłał hasło przez sieć czystym tekstem – dlatego też warto zabezpieczyć połączenie sieciowe poprzez SSL. Istnieje pewien przełącznik wspomnianej procedury o nazwie ODBC Encrypt jednakże nie jest to najlepsze wyjście ze względu na to, że przełącznik ten nie jest prawdziwym szyfrowaniem, a jedynie obfuskacją i posiada liczne ograniczenia.

Przejdźmy dalej i sprawdźmy czy rzeczywiście mamy pożądany przez nas kontekst:

SELECT USER_NAME()
GO

Przełączając się na rolę pozostaniemy w tym kontekście bezpieczeństwa aż do końca sesji(chyba, że postanowimy inaczej o czym w dalszej części artykułu). W porządku, nadaliśmy uprawnienia i przełączyliśmy się na naszą rolę – sprawdźmy jej działanie i odpytajmy dowolną tabelę ze schematu Application:

SELECT * FROM Application.Cities
GO

Wszystko działa jak należy – odpytajmy zatem inną tabelę z innego schematu:

SELECT * FROM [Purchasing].[PurchaseOrderLines]
GO

Rezultat był dosyć łatwy do przewidzenia – co natomiast gdy chcemy pobrać dane z innej bazy danych? Jest to możliwe tylko wtedy gdy w określonej bazie danych user o nazwie guest został włączony i przypisane zostały mu określone prawa gdyż to właśnie on jest używany przez rolę aplikacyjną przy dostępie do innych baz. Innym ograniczeniem jeśli chodzi o omawiane role jest brak możliwości odwoływania się do ustawień i zasobów samej instancji – co wynika wprost z tego czym funkcjonalność Application Roles jest i tym, że działa ona na poziomie samej bazy danych.

W porządku, możemy przełączyć kontekst do danej roli – ale czy możemy ten kontekst wyłączyć lub przełączyć? Kiedyś odpowiedź była by negatywna gdyż aby to zrobić trzeba było się rozłączyć i połączyć na nowo – teraz jednak mamy taką możliwość. Aby móc tego dokonać musimy zdefiniować tak zwane ciasteczko (cookie), które następnie możemy użyć jako argument procedury sp_unsetapprole:

SELECT USER_NAME() AS OriginalContext
DECLARE @cookie varbinary(8000);

EXEC sp_setapprole 'TestAppRole', 'zaq1@WSX'  
    , @fCreateCookie = true, @cookie = @cookie OUTPUT;  
SELECT USER_NAME() AS AppRoleContext
EXEC sp_unsetapprole @cookie;

SELECT USER_NAME() AS RevertedContext

Rezultat naszych działań przedstawia się następująco:

Czyli kontekst został prawidłowo nadpisany rolą aplikacyjną – po czym został przywrócony do oryginalnego stanu.

Jeśli chodzi o kwestie utrzymaniowe – to zmiana uprawnień przypisanych do określonej roli działa analogicznie do tradycyjnych ról. Jeśli chcemy zmienić raz wprowadzone hasło możemy to zrobić przy pomocy procedury o nazwie sp_approlepassword, która za pierwszy parametr przyjmuje nazwę roli, a za drugi nowe hasło :

sp_approlepassword 'TestAppRole', 'zaq1@WSX2'

Rolę można również bardzo łatwo usunąć z poziomu GUI lub używając kolejnej procedury  sp_dropapprole:

sp_dropapprole 'TestAppRole'

Do dyspozycji mamy też tradycyjną składnię DROP APPLICATION ROLE:

DROP APPLICATION ROLE [TestAppRole]

W przypadku chęci usunięcia roli aplikacyjnej, która jest właścicielem np. schematu – nie będziemy mogli tego zrobić. Przed podjęciem jakichkolwiek działań musimy przenieść własność na innego prinicipal’a (np. użytkownika czy też rolę).

Role aplikacyjne w użyciu i implementacji są bardzo proste i w wielu przypadkach bardzo upraszczają kwestie bezpieczeństwa. Ze względu na prostotę tego mechanizmu warto znać jego możliwości i testować na własnym projekcie. Być może nie trzeba będzie tworzyć grupy do której będzie trzeba dodawać dziesiątki użytkowników. Z drugiej strony warto również pamiętać, że opieranie dostępu do bazy danych na jednym haśle nie jest być może najbezpieczniejszym rozwiązaniem i zdecydowanie wymaga użycia szyfrowanego połączenia między narzędziem klienckim i bazą danych. Ponadto hasło musi być odpowiednio zabezpieczone na poziomie aplikacji – moim zdaniem jednak wszystko ma swoje wady i zalety, a jedynie odpowiednie zastosowanie może uwypuklić jedne bądź drugie.