Po przejściowych problemach ze startem w PBM rozwiązanych “With a Little Help from My Friends” - co można prześledzić tutaj - wypłynąłem na szerokie wody możliwości tworzenia polis w MSSQL 2008.
Na początek kilka blogów, które warto subskrybować przez swój czytnik RSS:
Co to jest polisa? Według encyklopedii to “tradycyjny dokument, którym ubezpieczyciel potwierdza zawarcie umowy ubezpieczenia”. W MSSQL polisa (policy) jest również zawarciem “umowy” od której zawierający oczekuje, że zapewni mu ona zdrowie (health state) jego serwerów SQL, w szeregu różnych aspektów (facets). Gdzie znajduje się zarządzanie polisami w MSSQL 2008? W zakładce Management->Policy Management. W zakładce Facets można zobaczyć predefiniowaną listę aspektów, co do których można budować warunki zapewnienia wymaganego przez wprowadzającego stanu, który uważa się za satysfakcjonujący do spełnienia oczekiwanych przez niego warunków bezpieczeństwa (tak jak go rozumie). Klikając podwójnie na wybrany aspekt można zobaczyć listę warunków, które można w danym aspekcie brać pod uwagę. Aby ułatwić start rozpoczynającemu budowanie własnych polis użytkownikowi istnieje możliwość importowania predefiniowanych polis. Można to zrobić wybierając prawym klawiszem opcję Policies->Import Policy…
Klikając na klawisz … obok pola Files to import uzyskujemy
i na początek DatabaseEngine
Warto wybrać jakąkolwiek polisę, zaimportować ją i obejrzeć jak wyglądają warunki (zakładka Conditions) oraz jak jest zbudowana sama polisa wybierając dla nich opcję Properties.
Polisy to świetne narzędzie dla DBA do sprawdzania warunków bezpieczeństwa stawianych środowisku produkcyjnemu. Ale nie mniej istotne może być jej zastosowanie do tworzenia środowiska zespołów deweloperskich. Budując odpowiednie polisy można wymusić stosowanie konwencji tworzenia w projekcie nazw tabel, procedur, widoków etc. W tym przypadku najlepiej sprawdza się wartościowanie polisy w trybie On change – prevent. Powoduje ona odpalenie trigger’a w celu niedopuszczenia do naruszenia zasad polisy. W ten sposób można również wymusić określanie założonej schema przy tworzeniu procedur składowanych. W tym celu uruchamiamy opcję Conditions->New Condition…
Równocześnie życzymy sobie aby polisa obowiązywała w bazie TEST:
Następnie tworzymy nową polisę uruchamiając opcję Policies->New Policy…
Mamy polisę, która wymusza stosowanie określonej schema dla nowo tworzonych procedur składowanych w bazie TEST. Ustawiony został tryb ewaluacji na On change – prevent, ma ona włączone Enabled więc spodziewamy się, że nie uda się nikomu stworzyć procedury, która łamię tę polisę. Sprawdzamy to:
…i zdziwienie. Dlaczego polisa nie zadziałała? Dlatego, że warunek na nazwę bazy danych pochodzi od aspektu Database, który nie przywiduje obsługi wartościowania On Change. Takie wartościowanie zapewnia aspekt Database Options ale nie może on być zastosowany do Against Targets. Taka łyżka dziegciu w beczce miodu…
Rozwiązaniem może zastosowanie in Every Database jako Against Targets do wartościowania polisy.
i wówczas:
Ale jest to rozwiązanie “brute force”. Czy można to zrobić lepiej? Odpowiedź jest negatywna (jeżeli chodzi o zablokowanie możliwości stworzenia nazwy procedury składowanej niezgodnej ze zdefiniowaną dla niej polisą w określonej bazie danych). Ale zawsze można wykonać wartościowanie On demand przyjmując, że polisa ma obejmować tylko jedną bazę tak jak założyliśmy na początku. Jest to zabezpieczenie post factum ale zawsze jest to coś niż nic.
Ale zdecydowanie najciekawszą właściwością jest możliwość bufowania własnych warunków w oparciu o zapytania będące podstawą wartościowania polis. Najciekawszym zastosowaniem może być zbudowanie swego własnego warsztatu polis do liczników wydajności dostarczanych przez widoki DMV. Dla przykładu zbudujemy warunek, który będzie postawą dla polisy, która będzie wartościowała czy średni czas wykonania procedury składowanej jest większy od założonej wartości (chore) lub mniejszy (zdrowe).
Przy budowania wartościowania w oparciu o własne zapytania należy pamiętać, że wartościowanie może się odbywać w kontekście aspektu dla którego warunek został zbudowany oraz aby to miało sens należy wykorzystać parametry:
- @@ObjectName – nazwę obiektu branego pod uwagę przy wartościowaniu w kontekście danego aspektu,
- @@SchemaName – schema obiektu branego pod uwagę przy wartościowaniu w kontekście danego aspektu.
Zbudujemy funkcję wartościującą w oparciu o wykorzystanie wbudowanej PBM funkcji ExecuteSql. Zainteresowanych szczegółami wywołania funkcji ExecuteSql, którą wykorzystam w przykładzie odsyłam tutaj.
Wartościowane będzie miało dwa warunki:
- Pierwszy warunek będzie wyliczał średni czas wykonania procedury składowanej i jeżeli przekroczy ona pewien próg będzie zwracał 0 i w przeciwnym wypadku 1, czyli:
ExecuteSql('Numeric', 'select case when sum(total_elapsed_time) / sum(execution_count) > 100 then 0
else 1 end avg_time
from sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(sql_handle) st
inner join sys.all_objects o on st.objectid = o.object_id
where o.name = @@ObjectName
group by o.name') |
- Drugi warunek będzie sprawdzał istnienie przypadku gdy procedura składowana nie była wywoływana wcale, w związku z czym nie nie posiada zarejestrowanych liczników:
| IsNull(ExecuteSql('Numeric', 'select case when sum(total_elapsed_time) / sum(execution_count) > 100 then 0
else 1 end avg_time
from sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(sql_handle) st
inner join sys.all_objects o on st.objectid = o.object_id
where o.name = @@ObjectName
group by o.name'), 0) |
Teraz należy wbudować to w warunki dla polisy:
A następnie należy zbudować polisę w oparciu o ten warunek:
Pozostało teraz wykonać Evaluate dla tej polisy i analizować podane “na tacy” wyniki, które polisa uznała za chore.
Czego mi brak? Możliwości parametryzowania warunków (poza wymienionymi dwoma). Co mi sie nie podoba? Wprowadzenie kolejnego, jeszcze innego sposobu przekazywania parametrów. Poza ODBC, OLEDB, T-SQL, SSIS teraz do funkcji PBM trzeba podawać w sposób, który kojarzy się z globalnymi zmiennymi T-SQL. Ale bilans jest mimo wszystko na plus.