Policy Based Management – czyli jak zarządzać obiektami SQL Server

Zdarza się, iż w bazach danych gdzie tworzone są różnorakie obiekty potrzeba wprowadzić pewną nomenklaturę np. chcemy aby nasze procedury zawsze były tworzone z odpowiednim przedrostkiem, obiekty musiały być zawarte w określonym schemacie, a określona właściwość samej bazy danych miała ustawioną taką,a nie inną wartość. Oczywiście każdy z nas może wierzyć na słowo i umówić się z deweloperami czy też innymi użytkownikami naszej bazy aby stosowali się do ustalonych wcześniej reguł. Nauczeni doświadczeniem powinniśmy wiedzieć, że nikt w IT nie powinien być ufny i wierzyć słownym zapewnieniom – jaki zatem mamy inne wyjście? W SQL Server mamy do dyspozycji świetne narzędzie jakim jest Policy Based Management, które pozwala nam zdefiniować pewne reguły wymuszające określone zachowanie na naszym serwerze. W ramach niniejszego wpisu postaram się Wam przedstawić jak w łatwy i szybki sposób konfigurować wspomniany mechanizm.

Na samym początku tradycyjnie stwórzmy sobie bazę danych na której będziemy działać – w tym przypadku nie musimy przejmować się plikami, logiem itp. – zostawmy to wszystko z domyślnymi wartościami:

CREATE DATABASE PBM_DEMO
GO

Policy Based Management po połączeniu się z instancją z poziomu Management Studio znajduje się w węźle Management:

Jak łatwo zauważyć cały mechanizm składa się z trzech komponentów:

• Conditions – zestaw warunków odnoszących się do określonych obiektów (facets)
• Facets – wszystkie obiekty SQL Server do których mogą odnosić się warunki
• Policies – obiekt, który może wywołać określone Conditions według określonego harmonogramu lub na żądanie. Polityka może też działać prewencyjnie tzn. być wywoływana w momencie np. tworzenia obiektu aby sprawdzić czy jest on zgodny ze zdefiniowanymi warunkami.

Jak zawsze najprościej będzie zrozumieć to na przykładzie – stwórzmy sobie zatem przykładową politykę. Aby tego dokonać należy kliknąć prawym przyciskiem myszy na folder Policies i tam wybrać z menu kontekstowego New Policy. Po wykonaniu tej czynności naszym oczom powinno ukazać się okno podobne do poniższego:

Pierwszym krokiem jest nadanie nazwy – nasza polityka będzie miała za zadanie wymusić to aby nazwy każdej z procedur zaczynały się od przedrostka sp_ oraz aby żadna z nich nie znajdowała się w schemacie dbo. Tak więc nazwijmy obiekt “StoredProcedureWithPrefixAndNotInSchema” – zdaję sobie sprawę, że to dosyć długa nazwa ale dzięki niej wiemy dokładnie co znajduje się wewnątrz. Kolejnym krokiem jest rozwinięcie pola Condition – w tym miejscu powinniśmy widzieć dostępne warunki, które możemy zawrzeć w polityce. Na ten moment wybierzmy z menu opcję New Condition. Naszym oczom powinno ukazać się okno o nazwie Create New Condition, gdzie musimy zdefiniować nazwę oraz w sekcji Facet wybrać ten element do którego chcemy się odnosić w warunku ( w naszym przykładzie będzie to Stored Procedure).

Do każdego obiektu (Facet) mamy przypisane określone właściwości. Ich ilość jest zdeterminowana tym jaki obiekt wybraliśmy. Procedury składowane mają wiele właściwości z czego nas interesuje jej nazwa czyli @Name oraz schemat w którym się znajduje czyli @Schema. Po wybraniu właściwości możemy przypisać operator taki jak =,!=,<,<=,>,>=, LIKE, NOT LIKE, IN, NOT IN. Operatory również będą nam wyświetlane w zależności od tego jaki typ ma właściwość, którą wybraliśmy. Ze względu na fakt, iż chcemy aby nasze procedury zaczynały się określonym przedrostkiem to wybierzmy @Name, operator LIKE oraz w wartości wpiszmy ‘sp_%’ ponieważ według tego wzorca chcemy aby nasze procedury były nazywane.

Sama wartość, tak jak pozostałe elementy warunku, jest zdeterminowana właściwością jaką wybraliśmy. Jeśli właściwość może przyjąć dowolny ciąg znaków to w polu Value będziemy mogli wpisać co tylko chcemy, jeśli natomiast właściwość może przyjąć wartość z predefiniowanej listy to będziemy ograniczeni do elementów tej właśnie listy. Wartości warunków mogą być również wynikiem działania prostych wyrażeń dostępnych w tym mechanizmie:

Po zdefiniowaniu @Name dodajmy nową linię warunku i tam ustawmy @Schema !=’dbo’. Linie pomiędzy sobą mogą być łączone warunkiem AND lub OR przy czym podobnie jak w wielu językach programowania AND ma wyższość nad OR i działa tak jakbyśmy użyli nawiasu tj. warunekA AND warunekB OR warunekC działa analogicznie do zapisu (warunekA AND warunekB) OR warunekC. Ostatecznie okno definiowania warunku powinno przypominać poniższy zrzut ekranu:

Zatwierdzając klawiszem OK stworzyliśmy nasz pierwszy warunek i powróciliśmy do definiowania polityki. Póki co okno tworzenia polityki wygląda następująco:

Wprawne okno zauważy, że stworzony przez nas warunek będzie odnosił się do wszystkich procedur w każdej bazie danych, a tego raczej nie chcemy. Naszym celem jest sprawdzenie procedury tylko w bazie PBM_Demo – dlatego też kliknijmy w słowo Every obok Database i tam z listy rozwijanej wybierzmy po raz kolejny New Condition. Naszym oczom po raz kolejny wyświetlone zostanie okno definiowania warunku – tym razem jednak musimy wybrać bazę, która nas interesuje. Aby tego dokonać wystarczy wybrać Fact o nazwie Database i właściwość @Name po czym skonfigurować ją tak jak można zobaczyć na poniższym zrzucie:

Po zdefiniowaniu warunku i zatwierdzeniu zobaczymy, że cała polityka będzie odnosić się tylko i wyłącznie do wybranej przez nas bazy. Kolejnym krokiem jest wybór tego w jaki sposób nasza polityka będzie wywoływana.

Może ona działać w czterech trybach:

• On demand – obiekty będą sprawdzone przez politykę na żądanie
• On schedule – obi będą sprawdzone według harmonogramu
• On change: log only – w momencie zmiany obiektu zostanie zalogowana informacja o tym czy dana zmiana jest zgodna z polityką czy też nie
• On change: prevent – w momencie zmiany obiektu jego właściwości zostaną sprawdzone pod kątem polityki- jeśli są one z nią niezgodne to zwrócony zostanie błąd i obiekt nie zostanie stworzony/zmodyfikowany

Kolejność widoczna na powyższej liście nie jest przypadkowa i wskazuje na to w ilu scenariuszach takie podejście jest możliwe do wykorzystania. Dla przykładu On demand możemy wykorzystać w każdym scenariuszu, który wymyślimy. On schedule może wykorzystywać wszystkie obiekty Facet wchodzące w skład silnika baz danych (ale już nie te związane np. z usługą Analysis Services).  On change prevent i On Change: log only z kolei nie wspiera polityk, które nie powodują wywołania żadnego zdarzenia lub w żaden transakcyjny sposób nie mogą być cofnięte np. wzrost rozmiaru pliku bazy danych. Używając widoku znajdującego się w bazie systemowej msdb (bo na niej właśnie opiera się opisywany mechanizm) o nazwie syspolicy_management_facets możemy zdeterminować w jakim trybie mogą działać poszczególne Facet:

use msdb
go

select * from dbo.syspolicy_management_facets

Interesującym dla nas polem jest execution_mode który jest niczym innym jak maską bitową wskazującą w jakim trybie dany Facet może być wykonany:

• 4 On Schedule
• 2 On Change: Log Only
• 1 On Change: On Prevent

Czyli jeśli dana polityka wykorzystuje Facet z daną liczbą w powyższym widoku to może być uruchamiana według następujących zasad:

• 0 On Demand
• 4 On Demand i On Schedule
• 6 On Demand, On Schedule i On Change:Log Only
• 7 On Demand, On Schedule, On Change: Log Only i On Change: Prevent

W naszej demonstracji interesuje nas tryb On Change: Prevent gdyż chcemy jawnie zabronić tworzenia procedur w inny sposób niż byśmy tego chcieli.

Ostatnim elementem wartym wspomnienia jest zakładka Description gdzie jak nazwa wskazuje możemy opisac naszą politykę ale nie tylko. Możemy tutaj również dodać stworzony przez nas obiekt do jednej z predefiniowanych kategorii lub stworzyć nową kategorię. Dzięki temu podejściu przy większej ilości polityk możliwe jest zachowanie odpowiedniego porządku. Ponadto w tym miejscu możemy podać Text to display oraz Address czyli tekst oraz adres URL jaki zostanie wyświetlony w sekcji Additional help komunikatu zwróconego w przypadku naruszenia polityki. Te dodatkowe informacje mogą być przydatne gdy chcemy użytkownikom dać odnośnik do zapisu w dokumentacji itp.

Po stworzeniu polityki w eksploratorze obiektów kliknijmy na nią prawym przyciskiem myszy i z menu kontekstowego wybierzmy Enable aby ją uruchomić. Następnie przejdźmy do okna zapytań i spróbujmy stworzyć dowolną procedurę w schemacie dbo:

CREATE PROC dbo.TestowaProcedura
AS
SELECT @@VERSION AS WersjaSQL

Efektem naszych działań poniższy komunikat błędu:

Policy 'StoredProcedureWithPrefixAndNotInSchema' has been violated by 
'SQLSERVER:\SQL\WIN-N5EEPKU6QVV\DEFAULT\Databases\PBM_DEMO\StoredProcedures\dbo.TestowaProcedura'.
This transaction will be rolled back.
Policy condition: '@Name LIKE 'sp_%' AND @Schema != 'dbo''
Policy description: ''
Additional help: '' : ''
Statement: '

CREATE PROC dbo.TestowaProcedura
AS
SELECT @@VERSION AS WersjaSQL

'.
Msg 3609, Level 16, State 1, Procedure sp_syspolicy_dispatch_event, Line 65 [Batch Start Line 2]
The transaction ended in the trigger. The batch has been aborted.

Błąd ten jawnie wskazał nam nazwę polityki oraz warunek w niej zawarty który został naruszony. Jeśli chcemy aby komunikat dokładnie wskazywał co zostało naruszone w ramach warunku to zamiast łączonego wyrażenia możemy stworzyć dwie osobne polityki z pojedynczymi warunkami. Niestety (chociaż w może to być dobra wiadomość – zależy od punktu widzenia) mechanizm ten nie działa tak jak SQL Server Agent i nie możemy dodawać wielu warunków do pojedynczej polityki. Spróbujmy zatem stworzyć nowy schemat i stworzyć procedurę zgodnie z wytycznymi:

USE PBM_DEMO
GO

CREATE SCHEMA TestowySchemat
GO

CREATE PROC TestowySchemat.sp_TestowaProcedura
AS
SELECT @@VERSION AS WersjaSQL

W rezultacie wszystko wykonało się poprawnie:

Command(s) completed successfully.

Za Policy Based Management stoi nic innego jak wyzwalacze DDL i należy mieć na uwadze fakt, że każda pojedyncza polisa może mieć wpływ na wydajność tworzenia obiektów itp. Tutaj też warto wytłumaczyć dlaczego nie możemy niektórych obiektów monitorować automatycznie On Change – po prostu nie jest możliwe przechwycenie triggerem takiego wydarzenia. Ponadto warto pamiętać, że czasem niektóre polisy mogą mięć powiązania itp. dlatego też trzeba dla nich włączyć opcję zagnieżdżania triggerów:

EXEC sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE ;  
GO  
EXEC sp_configure 'nested triggers', 1 ;  
GO  
RECONFIGURE;  
GO

Oczywiście wad tego podejścia nie trzeba tłumaczyć – jednakże włączenie tego ustawienia jest konieczne aby mieć pewność, że Policy Based Management działa w sposób taki jakbyśmy tego chcieli.

Przechodząc dalej stwórzmy sobie politykę, która sprawdzi czy opisywana przeze mnie niedawno na blogu opcja xp_cmdshell(link tutaj) jest włączona czy też nie. W tym przypadku konfiguracja sprowadza się do ustawienia właściwości @XPCmdShellEnabled na False – właściwość ta znajduje się w Facet o nazwie Surface Area Configuration:

Różnicą w porównaniu do tego co robiliśmy wcześniej jest ustawienie Evaluate Mode na  On change: log only.

Zmieńmy rzeczoną właściwość i sprawdźmy w logu czy mamy tam informację związaną z polityką. Najpierw wykonajmy polecenie, które polityki nie narusza by zaraz potem postąpić wbrew polityce:

EXEC sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE ;  
GO  
EXEC sp_configure 'xp_cmdshell', 0;  
GO  
RECONFIGURE;  
GO 

EXEC sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE ;  
GO  
EXEC sp_configure 'xp_cmdshell', 1;  
GO  
RECONFIGURE;  
GO

Obie operacje zostały wykonane poprawnie i nie dostaliśmy żadnego komunikatu o tym, że w międzyczasie nasze polecenia zostały sprawdzone względem polityki. W poszukiwaniu tych informacji sprawdźmy zatem dziennik znajdujący się Management -> SQL Server Logs:

Po otwarciu dziennika zobaczymy, że informacje o sprawdzeniu względem polityki została zawarta zarówno dla polecenia, które go naruszyło jak i dla tego które było z nią zgodne:

Wykorzystując to podejście możemy monitorować użycie tak niebezpiecznego narzędzia jak xp_cmdshell zapisując przy tym informacje w dzienniku. Oczywiście możecie powiedzieć, że użycie sp_configure wymaga wysokich uprawnień i jak ktoś chce go użyć to i tak informacja trafia do loga. Zgadzam się z tym stwierdzeniem jednakże jeżeli ktoś ma uprawnienia sysadmin to nie ma żadnej możliwości powstrzymania go przed czymkolwiek. Dlatego też tego typu uprawnień nie rozdaje się każdemu. Niestety nie jesteśmy w stanie “zabronić” czy chociażby dać znać osobie włączającej to ustawienie, że jej działanie jest niezgodne z polityką. Innymi słowy nie możemy włączyć trybu działania dla tej polityki On Change: Prevent. Skąd to wiem? Informacja ta zawarta jest we wspomnianym wcześniej widoku:

select * from dbo.syspolicy_management_facets
where name='ISurfaceAreaFacet'

Każde naruszenie polityki trafia do dziennika z określonym numerem błędu i tak:

• 34050 – Naruszenie polityki On change:prevent uruchomione automatycznie
• 34051 – Naruszenie polityki On change: prevent uruchomione ręcznie
•  34052 – Naruszenie polityki On schedule
• 34053 – Naruszenie polityki On change: log only

Mając te informacje nic nie stoi na przeszkodzie abyśmy wykorzystując SQL Server Agent stworzyli alerty informujące nas o zaistnieniu naruszenia polityki. Połączenie wykorzystania SQL Server Agent i Policy Based Management jest w moim odczuciu bardzo przydatne gdyż nie zawsze mamy możliwość ręcznego uruchamiania polityk i szczegółowego monitorowania dziennika transakcji,

Istotnym elementem tego opisywanego mechanizmu jest to, że pod warstwą graficzną wywoływane są odpowiednie procedury z bazy msdb. Same wyrażenia wchodzące w skład Policy Based Management są zapisywane w języku XML i wszelkie zmiany w tych właśnie obiektów są zapisywane jako XML np. poniżej kod, który definiuje powyższy warunek sprawdzenia czy xp_cmdshell jest włączony:

Declare @condition_id int
EXEC msdb.dbo.sp_syspolicy_add_condition @name=N'XpCmdShellEnabled', @description=N'', 
@facet=N'ISurfaceAreaFacet', @expression=N'<Operator>
  <TypeClass>Bool</TypeClass>
  <OpType>EQ</OpType>
  <Count>2</Count>
  <Attribute>
    <TypeClass>Bool</TypeClass>
    <Name>XPCmdShellEnabled</Name>
  </Attribute>
  <Function>
    <TypeClass>Bool</TypeClass>
    <FunctionType>False</FunctionType>
    <ReturnType>Bool</ReturnType>
    <Count>0</Count>
  </Function>
</Operator>', @is_name_condition=0, @obj_name=N'', @condition_id=@condition_id OUTPUT
Select @condition_id

GO

Dosyć ciekawe jednakże cieszy fakt, iż mimo wszystko mamy GUI, a nie musimy obrabiać XML…

Podsumowując mechanizm Policy Based Management jest bardzo fajnym narzędziem, które mimo wszystko należy stosować z umiarem. W środowiskach deweloperskich czy testowym można stworzyć polityki sprawdzające konwencję nazewniczą poszczególnych obiektów. Następnie można stworzyć harmonogram, który będzie sprawdzał codziennie to czy deweloperzy trzymają się tych zasad i dzięki temu mieć kontrolę nad tworzonymi obiektami. Z drugiej strony należy pamiętać, że pod spodem mamy wyzwalacze, które prawie zawsze wiążą się z problemami. Tak jak już wspomniałem polityki mogą wiązać się ze spadkiem wydajności, ponadto czasem możemy nierozwaznymi politykami zakłócić działanie niektórych mechanizmów – dlatego też właściwie pod każdym artykułem powinienem napisać, że nie ma mechanizmu w IT, który będzie myślał za nas i nie inaczej jest w przypadku PBM.