SQL Server 2005 Service Pack 2 przedstawia – Logon Trigger
Dodatek Service Pack 2 do systemu Microsoft SQL Server 2005 daje administratorom baz danych nowe możliwości przechwytywania zdarzeń logowania serwera i odpowiedniego reagowania na takie zdarzenia. Taką funkcjonalność oferuje nowa klasa obiektów zwana logon trigger.
Zawartość strony
 | Wstęp |
| Tworzenie i działanie logon triggera |
| Zastosowania |
| Podsumowanie |
Wstęp
W systemie SQL Server 2005 administrator otrzymuje dwa potężne narzędzia do nadzorowania zdarzeń w systemie i bazach danych. Triggery DDL (DDL -ang. Data Definition Language) to nowy rodzaj triggerów (słowo „trigger” tłumaczone jest często na język polski jako „wyzwalacz”, jednak autorzy artykułu pozostają przy nazwie pochodzącej z języka angielskiego), które są uruchamiane jako reakcja na wykonanie składni DDL (takich jak CREATE, ALTER czy DROP) na poziomie serwera lub określonej bazy danych.
Event notification (po polsku – „powiadomienie o zdarzeniu”) to z kolei obiekt bazy danych, którego funkcjonowanie polega na wysyłaniu informacji o zdarzeniach mających miejsce na serwerze lub w bazie danych do odpowiedniego serwisu, obsługiwanego przez usługę Service Broker.
Oba wymienione mechanizmy mają swoje zalety. W przypadku triggerów DDL są to między innymi: prostota tworzenia i możliwość wycofywania operacji DDL. W przypadku event notification zaś są to: możliwość przechwycenia bardzo wielu rodzajów zdarzeń, asynchroniczne przetwarzanie zdarzeń dzięki wykorzystaniu mechanizmów Service Brokera oraz możliwość przetwarzania zdarzeń na zdalnym serwerze.
Jednak oprócz zalet wspomniane mechanizmy mają swoje wady, zwłaszcza w kwestii obsługi zdarzeń logowania. Trigger DDL nie reaguje na ten typ zdarzenia, zaś event notification nie może w prosty sposób takiego zdarzenia wycofać. To skłoniło firmę Microsoft do stworzenia - w ramach dodatku Service Pack 2 do systemu SQL Server 2005 - mechanizmu łączącego zalety obu wspomnianych mechanizmów. Nowa klasa obiektów nazywa się logon trigger i umożliwia przechwycenie zdarzenia logowania na serwerze i wycofanie tego zdarzenia w klasyczny, jak dla triggerów, sposób.
Tworzenie i działanie logon triggera
Składnia T-SQL służąca do tworzenia logon triggera jest następująca:
CREATE TRIGGER trigger_name
ON ALL SERVER
[ WITH <logon_trigger_option> [ ,...n ] ]
{ FOR | AFTER } LOGON
AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME <method specifier> [ ; ] }
<logon_trigger_option> ::=
[ ENCRYPTION ]
[ EXECUTE AS Clause ]
<method_specifier> ::=
assembly_name.class_name.method_name
W nazwach logon triggerów podawanych zaraz na początku składni polecenia CREATE TRIGGER nie podaje się schematów (należy operować jednoczłonową nazwą obiektu).
Trigger tego typu zawsze tworzony jest na poziomie serwera (ON ALL SERVER). Posiada opcjonalne ustawienia takie same, jak procedura składowana:
| • | ENCRYPT – opcja służąca do szyfrowania definicji triggera |
| • | EXECUTE AS – opcja pozwalająca na zmianę kontekstu wykonywania triggera na wybrany login |
| Informacja: |
Opcja EXECUTE AS jest przydatna w logon triggerach, ponieważ logujący się użytkownik niekoniecznie musi mieć uprawnienia do wykonywania wszystkich operacji wykonywanych przez trigger. Jeśli trigger wykonałby się w kontekście logina o niewystarczających uprawnieniach, zwrócony zostałby błąd i trigger zakończyłby swoje działanie, co na ogół jest niepożądanym efektem, a w przypadku logon triggerów powoduje, że użytkownik nie może zalogować się do serwera. W logon triggerach, podobnie jak we wszystkich triggerach działających na poziomie serwera, opcja EXECUTE AS akceptuje loginy, a nie akceptuje userów baz danych. |
Treść triggera może być napisana w języku Transact-SQL lub może być podmapowana do zawartości odpowiedniej metody, z wnętrza assembly stworzonego w technologii .NET.
Logon trigger jest reaktywny, czyli działa podobnie jak triggery DML typu AFTER - jest uruchamiany po wystąpieniu zdarzenia AUDIT_LOGIN. Użycie wewnątrz kodu triggera składni ROLLBACK powoduje, że akcja logowania do serwera zostaje wycofana, a użytkownik otrzymuje komunikat jak poniżej (rysunek 1 obrazuje komunikat mówiący o wycofaniu zdarzenia logowania z poziomu środowiska Management Studio przez trigger).
Rys.1. Komunikat będący wynikiem działania logon triggera.
Drugim ewentualnym powodem tego, że sesja połączenia użytkownika z serwerem nie zostaje nawiązana, jest wystąpienie w triggerze błędu na poziomie (ang. severity) 20 lub wyższym (są to błędy krytyczne zrywające połączenie z serwerem). Trzecim powodem, o którym już wspomnieliśmy, może być nieposiadanie przez logującego się niezbędnych uprawnień, do wykonania kodu z wnętrza triggera.
W logon triggerach, podobnie jak w triggerach DDL, administrator może korzystać z funkcji systemowej EVENTDATA. Dzięki tej funkcji można przechwycić wszelkie informacje na temat zdarzenia, które było bezpośrednią przyczyną uruchomienia triggera. Wynikiem zwracanym przez funkcję są informacje o zdarzeniu w formacie XML. Dla logon triggerów ów XML ma następującą postać:
<EVENT_INSTANCE> <EventType>LOGON</EventType> <PostTime>2007-04-16T07:19:31.893</PostTime> <SPID>53</SPID> <ServerName>0704-002</ServerName> <LoginName>SOFTLAB\test</LoginName> <LoginType>Windows (NT) Login</LoginType> <SID>tu pojawia się SID</SID> <ClientHost><local machine></ClientHost> <IsPooled>0</IsPooled> </EVENT_INSTANCE>
Jak widać, funkcja zwraca wiele cennych informacji – administrator może monitorować kto, skąd i kiedy logował się do serwera. Dzięki użyciu funkcji języka XQuery lub używając funkcji OPENXML można wybrać dowolne detale ze zwróconego tekstu w formacie XML.
Zastosowania w logon triggerach znajdują także funkcje systemowe, takie jak:
| • | ORIGINAL_LOGIN – zwraca nazwę logina, który nawiązał połączenie i otworzył bieżącą sesję, |
| • | SUSER_SNAME (lub SYSTEM_USER) – zwraca nazwę logina, w kontekście którego aktualnie wykonywane są operacje na sesji (login ten nie musi być loginem zwracanym przez funkcję ORIGINAL_LOGIN, ponieważ kontekst można zmieniać, używając składni EXECUTE AS LOGIN), |
| • | HOST_NAME – domyślnie zwraca nazwę maszyny, z której nawiązywane jest połączenie z serwerem, ale należy pamiętać o tym, że niektóre aplikacje klienckie (jak na przykład SQLCMD) pozwalają na dowolne sterowanie tym identyfikatorem sesji, |
| • | APP_NAME – zwraca nazwę aplikacji, jakiej użytkownik używa do połączenia się z serwerem, |
| • | GETDATE (lub CURRENT_TIMESTAMP) – zwraca aktualną datę i czas. |
Metadane logon triggerów
Informacje o istniejących na serwerze logon triggerach można uzyskać używając widoków systemowych:
| • | sys.server_triggers – lista triggerów DDL działających na poziomie serwera oraz logon triggerów, |
| • | sys.server_events – zawiera jeden wiersz dla każdego zdarzenia obsługiwanego na poziomie serwera przez trigger DDL, logon trigger lub event notification, |
| • | sys.server_trigger_events – zawiera informacje o kolejności wykonywania triggerów. |
Przykładowe zapytanie zwracające listę logon triggerów oraz ich właściwości:
SELECT t.name AS [Nazwa triggera], t.is_disabled AS [Czy wylaczony], te.is_first AS [Czy wykonywany jako pierwszy], te.is_last AS [Czy wykonywany jako ostatni] FROM sys.server_triggers AS t INNER JOIN sys.server_events AS e ON t.[object_id] = e.[object_id] INNER JOIN sys.server_trigger_events AS te ON t.[object_id] = te.[object_id] WHERE e.type_desc = N'LOGON'
Kolejność wykonywania logon triggerów
Administrator serwera może zdefiniować wiele logon triggerów na jednej instancji systemu SQL Server 2005. W takim przypadku kluczowa może być kolejność, w jakiej triggery są wykonywane. Do określania kolejności wykonywania triggerów służy systemowa procedura składowana sp_settriggerorder. Procedura ta pozwala określić, który trigger będzie wykonywany jako pierwszy, a który jako ostatni. Nie pozwala natomiast konfigurować w jawny sposób kolejności uruchamiania dowolnej liczby logon triggerów.
Składnia uruchamiania tej procedury wygląda następująco:
EXEC sp_settriggerorder @triggername = N'logonLimitToThreeSessions',
@order = 'first',
@stmttype = 'LOGON',
@namespace = 'SERVER'
Powyższy kod ustawia logon trigger o nazwie logonLimitToThreeSessions jako pierwszy logon trigger do wykonania podczas logowania użytkownika do serwera.
Procedura sp_settriggerorder przyjmuje cztery parametry:
| • | @triggername – nazwa istniejącego logon triggera, |
| • | @order – który w kolejności będzie uruchamiany wybrany logon trigger (parametr akceptuje tylko trzy możliwe wartości: first – pierwszy, last – ostatni, none – nieokreślona kolejność), |
| • | @stmttype – tu należy podać słowo LOGON dla logon triggerów, a w ogólnym przypadku rodzaj zdarzenia, na które reaguje wybrany trigger, |
| • | @namespace – zakres działania triggera (dla logon triggerów – SERVER). |
Do wykonywania opisywanej procedury systemowej na logon triggerze niezbędne jest uprawnienie CONTROL SERVER.
| Informacja: |
Procedura systemowa sp_settriggerorder służy do określania kolejności wykonywania każdego triggera reaktywnego, czyli można za jej pomocą ustawiać kolejność wykonania triggerów DML typu „po”, triggerów DDL i logon triggerów. Powyżej został przedstawiony jedynie sposób użycia dla logon triggerów. |
Modyfikowanie, wyłączanie i usuwanie logon triggerów
Logon trigger może zostać zmodyfikowany przy użyciu polecenia ALTER TRIGGER. Polecenie to pozwala na podanie nowej definicji logon triggera.
Logon triggery można również wyłączać (bez ich usuwania z serwera), czyli spowodować, że trigger nie będzie uruchamiany. Służy do tego polecenie DISABLE TRIGGER, którego przykładowa składnia wygląda następująco:
DISABLE TRIGGER logonGetEventdata ON ALL SERVER
Powyższy kod powoduje wyłączenie triggera o nazwie logonGetEventdata.
Wyłączony trigger można włączyć przy użyciu polecenia ENABLE TRIGGER.
ENABLE TRIGGER logonGetEventdata ON ALL SERVER
Powyższy kod powoduje włączenie triggera o nazwie logonGetEventdata.
W obu wymienionych poleceniach zamiast nazwy konkretnego logon triggera można użyć słowa ALL, by wyłączyć / włączyć wszystkie logon triggery (oraz triggery DDL działające na zakresie serwera).
Do usuwania logon triggerów (jak również wszystkich innych triggerów) służy polecenie DROP TRIGGER. Przykładowa składnia tego polecenia dla logon triggera wygląda następująco (klauzula ON ALL SERVER jest obligatoryjna):
DROP TRIGGER logonGetEventdata ON ALL SERVER
Powyższy kod powoduje usunięcie triggera o nazwie logonGetEventdata.
Do wykonywania poleceń ALTER TRIGGER, DISABLE TRIGGER, ENABLE TRIGGER i DROP TRIGGER niezbędne jest posiadanie uprawnienia CONTROL SERVER.
| Informacja: |
W skrajnych sytuacjach, na przykład gdy na serwerze zostanie stworzony logon trigger uniemożliwiający logowanie użytkowników, użyj DAC (ang. Dedicated Administrator Connection) do wyłączenia lub usunięcia logon triggera. Połączenie typu DAC nie powoduje wykonania logon triggerów. |
Zastosowania
Poniżej prezentujemy przykładowe zastosowania logon triggerów.
Trigger TR_LOGON_LogEvents służy do audytu zdarzeń logowania. Dane dotyczące zdarzeń logowania zapisywane są w tabeli dbo.LogonEventLog w bazie danych LogDB. Na potrzeby wykonywania operacji zapisu tworzymy także login (ExecuteServerTriggersLogin – służy do zmiany kontekstu wykonywania logon triggera) i użytkownika (ExecuteServerTriggersUser – służy do zapisywania danych w tabeli audytującej logowanie). Do tabeli audytującej zapisywane są następujące dane: informacje zwracane przez funkcję EVENTDATA (login, data logowania, ew. adres IP), nazwa identyfikatora sesji / stacji, z jakiej loguje się użytkownik (wykorzystano funkcję HOST_NAME) oraz nazwa aplikacji, jakiej użytkownik używa do łączenia się z instancją SQL Server (wykorzystano funkcję APP_NAME).
Trigger TR_LOGON_AllowRegisteredOnly uniemożliwia logowanie użytkownikom, których login nie jest zarejestrowany w tabeli dbo.LoginProperties w bazie LogDB. Do przechwycenia nazwy logina wykorzystaliśmy funkcję ORIGINAL_LOGIN.
Trigger TR_LOGON_OnlyOneHost służy do uniemożliwienia wybranym loginom logowania do serwera z wielu maszyn jednocześnie. Realizacja tego celu jest następująca:
| • | w tabeli dbo.LoginProperties każdy rekord przechowuje jeden zarejestrowany login, |
| • | każdemu zarejestrowanemu loginowi przyporządkowana jest odpowiednia wartość w kolumnie OnlyOneHost (1 – login może logować się tylko z jednej maszyny w danym momencie, 0 – login może logować się z dowolnie wielu maszyn jednocześnie), |
| • | do wykrycia maszyn, z jakich loguje się użytkownik posłużyliśmy się funkcją HOST_NAME oraz widokiem dynamicznym sys.dm_exec_sessions, który zwraca informacje o bieżących sesjach, |
| • | do wykrycia w widoku sys.dm_exec_sessions rekordów odwołujących się do użytkownika, który próbuje się logować, wykorzystaliśmy funkcję systemową @@SPID. |
USE master
GO
-- Tworzymy login kontekstowy dla logon triggerów
IF NOT EXISTS ( SELECT *
FROM sys.server_principals
WHERE Name = 'ExecuteServerTriggersLogin' )
CREATE LOGIN ExecuteServerTriggersLogin WITH PASSWORD =
'%#$QJHQBK986Q2JHjQHKj6qw4k k33je3wJ4KQ#HE$L!J5HQWjkhlkjh QEJJ5QH'
GO
-- Będzie potrzebne przy odwołaniach do sys.dm_exec_sessions
GRANT VIEW SERVER STATE TO ExecuteServerTriggersLogin
GO
IF NOT EXISTS ( SELECT *
FROM sys.databases
WHERE name = 'LogDB' )
CREATE DATABASE LogDB
GO
USE LogDB
GO
-- Tworzymy użytkownika dla naszego konstekstowego logina
IF NOT EXISTS ( SELECT *
FROM sys.database_principals
WHERE name = 'ExecuteServerTriggersUser' )
CREATE USER ExecuteServerTriggersUser FOR LOGIN ExecuteServerTriggersLogin
GO
-- Tworzymy tabelę audytującą logowanie
IF OBJECT_ID('dbo.LogonEventLog') IS NULL
CREATE TABLE dbo.LogonEventLog
(
LogId INT IDENTITY(1, 1),
LogonData XML,
HostName SYSNAME
CONSTRAINT DF_LogonEventLog_HostName DEFAULT ( HOST_NAME() ),
AppName SYSNAME
CONSTRAINT DF_LogonEventLog_AppName DEFAULT ( APP_NAME() ),
CONSTRAINT PK_LogonEventLog PRIMARY KEY ( LogId )
)
GO
GRANT INSERT ON dbo.LogonEventLog TO ExecuteServerTriggersUser
GO
-- Tworzymy tabelę przechowującą właściwości dodatkowe loginów
IF OBJECT_ID('dbo.LogonEventLog') IS NULL
CREATE TABLE dbo.LoginProperties
(
LoginName SYSNAME NOT NULL,
FirstName NVARCHAR(100) NOT NULL,
LastName NVARCHAR(100) NOT NULL,
OncePerHost BIT NOT NULL
CONSTRAINT DF_LoginProperties_OnlyOneHost DEFAULT ( 1 ),
CONSTRAINT PK_LoginProperties PRIMARY KEY ( LoginName )
)
GO
-- Zarejestruj użytkownika sa
IF NOT EXISTS ( SELECT 1
FROM dbo.LoginProperties
WHERE LoginName = 'sa' )
INSERT INTO dbo.LoginProperties
(
LoginName,
FirstName,
LastName,
OnlyOneHost
)
VALUES (
N'sa',
N'System',
N'Administrator',
0
)
GO
-- Zarejestruj bieżącego użytkownika
IF NOT EXISTS ( SELECT 1
FROM dbo.LoginProperties
WHERE LoginName = SUSER_SNAME() )
INSERT INTO dbo.LoginProperties
(
LoginName,
FirstName,
LastName,
OnlyOneHost
)
VALUES (
SUSER_SNAME(),
N'CURRENT',
N'USER',
0
)
GO
GRANT SELECT ON dbo.LoginProperties TO ExecuteServerTriggersUser
GO
-- Zapisuj wszystkie próby zalogowania do LogDB.dbo.LogonEventLog
IF EXISTS ( SELECT *
FROM sys.server_triggers
WHERE NAME = 'TR_LOGON_LogEvents' )
DROP TRIGGER TR_LOGON_LogEvents ON ALL SERVER
GO
CREATE TRIGGER TR_LOGON_LogEvents ON ALL SERVER
WITH EXECUTE AS 'ExecuteServerTriggersLogin'
FOR LOGON
AS
BEGIN
INSERT LogDB.dbo.LogonEventLog ( LogonData )
SELECT EVENTDATA()
END
GO
-- Wpuszczaj tylko zarejestrowanych użytowników
IF EXISTS ( SELECT *
FROM sys.server_triggers
WHERE NAME = 'TR_LOGON_AllowRegisteredOnly' )
DROP TRIGGER TR_LOGON_AllowRegisteredOnly ON ALL SERVER
GO
/* Trigger pozwala na logowanie tylko użytkownikom zarejestrowanym */
/* UWAGA! Bez DAC tylko użytkownik sa zdoła się zalogować do systemu */
CREATE TRIGGER TR_LOGON_AllowRegisteredOnly ON ALL SERVER
WITH EXECUTE AS 'ExecuteServerTriggersLogin'
FOR LOGON
AS
BEGIN
IF NOT EXISTS ( SELECT *
FROM LogDB.dbo.LoginProperties p
WHERE p.LoginName = ORIGINAL_LOGIN() )
BEGIN
-- Tego komunikatu nie zobaczy użytkownik - będzie tylko w Event Logu
RAISERROR ( 'Logować mogą się tylko zarejestrowani użytkownicy i administratorzy systemu',
20, 1 ) WITH LOG
END
END
GO
-- Wpuszczaj użytkownika nieadministracyjnego tylko z jednej stacji
IF EXISTS ( SELECT *
FROM sys.server_triggers
WHERE NAME = 'TR_LOGON_OnlyOneHost' )
DROP TRIGGER TR_LOGON_OnlyOneHost ON ALL SERVER
GO
-- Trigger zabrania logowania zalogowanemu już u¿ytkownikowi,
-- któremu nie dajemy praw logowania z wielu stacji jednoczeœnie
CREATE TRIGGER TR_LOGON_OnlyOneHost ON ALL SERVER
WITH EXECUTE AS 'ExecuteServerTriggersLogin'
FOR LOGON
AS
BEGIN
DECLARE @FullLoginName VARCHAR(200),
@OncePerHost BIT
SELECT @FullLoginName = QUOTENAME(p.FirstName + ' ' + p.Lastname)
+ ' ' + QUOTENAME(p.LoginName),
@OncePerHost = p.OncePerHost
FROM LogDB.dbo.LoginProperties p
WHERE p.LoginName = ORIGINAL_LOGIN()
-- Niezarejestrowani użytkownicy nie podlegają naszej regule,
-- czyli mogą się logować bez problemu z wielu stacji
SELECT @OncePerHost = ISNULL(@OncePerHost, 0)
IF @OncePerHost = 1
AND EXISTS ( SELECT 1
FROM sys.dm_exec_sessions s
WHERE s.session_id <> @@SPID
AND s.host_name = HOST_NAME() )
BEGIN
RAISERROR ( 'Próba zalogowania użytkownika %s z więcej niż jednej stacji',
20, 1, @FullLoginName ) WITH LOG
END
END
GO
Podsumowanie
Logon trigger oferuje administratorom systemu SQL Server 2005 nowe możliwości audytu i kontroli procesów związanych z logowaniem do serwera. Stanowi doskonałe uzupełnienie mechanizmów istniejących przed pojawieniem się dodatku Service Pack 2. Z całą pewnością chcielibyśmy oglądać więcej tego typu dodatkowych funkcjonalności w dodatkach do systemu.
 | Paweł Potasiński (Microsoft Certified Trainer, Asseco Business Solutions S.A.) |
 | Marek Adamczuk (Microsoft Certified Database Administrator, SoftLab) |