Аудит в SQL Server. Уведомления о событиях.

Artikel
07/19/2009

Продолжение. Начало см. - Аудит в SQL Server. Триггер на логон.

Есть триггер на логон, на логофф триггера нету. Вопрос: как аудировать события выхода из системы? Здесь на помощь приходит еще один механизм SQL Server, появившийся в версии 2005, который наряду с DDL-триггерами можно вполне приспособить под задачи аудита – Event Notifications. Он основывается на функциональности сервис-брокера и по принципу своего действия напоминает триггер – как только происходит какое-либо системное событие, в очередь брокера падает сообщение.

Список системных событий является надмножеством того, что мы видели в случае DDL-триггеров. Т.е. это Аудит в SQL Server. Триггер на логон.\Рис.1, 2 плюс так называемые Trace Events – мы привыкли их видеть в профайлере. Полный список событий, поддерживаемых в Event Notifications можно посмотреть в DMV sys.event_notification_event_types. Их там всего 364. У кого в поле Type стоит < 1000, те события DDL, а у кого больше, те профайлерные. Соответственно, 179 и 185. Это в 2008-м. В 2005-м, по-моему, было меньше. Как и в случае DDL события трассировки объединяются в группы, чтобы подписываться крупным планом. Группы обозначены в поле parent_type. Например, группа TRC_SECURITY_AUDIT включает события AUDIT_LOGIN, AUDIT_LOGOUT и AUDIT_LOGIN_FAILED. Это профайлерные события, знакомые нам по свойствам трассы

рис.1

Теперь понятно, как аудировать логофф. Надо подписаться на уведомление о событиях AUDIT_LOGOUT. Делается это так. Включаем брокер в текущей базе:

use tempdb

if (select is_broker_enabled from sys.databases where name = 'tempdb') = 0 alter database tempdb set enable_broker

Скрипт 1

Создаем очередь. У меня не получается придумать ей наукообразного определения в стиле куст есть совокупность веток, произрастающих из единой точки. Очередь – это просто очередь. В нее будут падать сообщения.

if exists(select 1 from sys.service_queues where name = 'myEventQueue') drop queue myEventQueue

create queue myEventQueue

Скрипт 2

В терминологии брокера сервис есть приложение, имеющее сношение с другими сервисами посредством этой очереди (в данном случае myEventQueue), т.е. отправляя в и принимая из нее сообщения. Сообщения подразумеваются в формате XML. Понимаемые участниками диалога схемы XML-документов входят в определение контракта, или протокола беседы. В данном случае мы не придумывали свой формат, а указали один из системных предопределенных контрактов (PostEventNotification).

if exists(select 1 from sys.services where name = 'myNotifications') drop service myNotifications

create service myNotifications on queue myEventQueue

([https://schemas.microsoft.com/SQL/Notifications/PostEventNotification])

Скрипт 3

Собственно, все. Предварительная работа на этом закончена. Создаем уведомление на событие AUDIT_LOGOUT. При возникновении события уведомление будет отправлено сервису myNotifications, определенному в текущей БД (current database), то есть окажется в очереди myEventQueue, заданной при определении этого сервиса (Скрипт 3).

if exists(select 1 from sys.server_event_notifications where name = 'myEvent') drop event notification myEvent on server

CREATE EVENT NOTIFICATION myEvent

ON server

FOR AUDIT_LOGOUT

TO SERVICE 'myNotifications', 'current database'

Скрипт 4

Уведомление о логофф создается на уровне сервера. Определенные на уровне сервера уведомления показывает DMV sys.server_event_notifications, на уровне базы - sys.event_notifications.

Проверяем, что очередь пуста:

select * from myEventQueue

Скрипт 5

Если у вас запущен Reporting Service, лучше приостановить его на время эксперимента, иначе он быстро заполнит очередь своими сообщениями, т.к. то и дело входит и выходит. Чтобы очистить очередь, прекратите беседу:

declare @h uniqueidentifier;

select @h = c.conversation_handle from sys.conversation_endpoints c join sys.services s on c.service_id = s.service_id where name = 'myNotifications';

end conversation @h with cleanup;

Скрипт 6

Правда, как теперь запустить ее вновь, я не знаю J. Как написано в умной книжке под названием BOL (https://msdn.microsoft.com/en-us/library/ms177521.aspx), в этом случае SQL Server immediately removes all messages from the service queue and the transmission queue, without notifying the remote service. То есть можно, конечно, написать в лоб begin dialog conversation @h from service [https://schemas.microsoft.com/SQL/Notifications/EventNotificationService] to service 'myNotifications', 'current database' on contract [https://schemas.microsoft.com/SQL/Notifications/PostEventNotification], но поскольку сервис нотификации об ивентах не получил корректного сообщения об окончании предыдущей беседы, он думает, что та по-прежнему числится за ним и в новую ничего говорить не хочет. Она так и висит бесконечно долго в состоянии STARTED_OUTBOUND вместо CONVERSING. А кинуть ему в старую беседу сообщение типа https://schemas.microsoft.com/SQL/ServiceBroker/EndDialog нельзя, потому что она уже тю-тю. Эта непростая житейская коллизия подпортила мне немало крови. Для разрешения ситуации я воспользовался советом одного мужика из Трансильвании, который работал в составе команды, писавшей брокер, а потом свалил в независимые консультанты. Все оказалось просто: The conversations used by the event notifications infrastructure should not be ended. They will be ended by initiator when the event notification object is dropped (DROP EVENT NOTIFICATION). You procedure should react to the EndDialog message by ending it's side. If you end the conversations prematurely from the procedure side you are in effect disabling the event notification (it will have to be dropped and created again) - https://social.msdn.microsoft.com/forums/en-US/sqlservicebroker/thread/b7a405c1-f94e-41fc-b93b-7ffb08d6dedf/. Короче, Скрипт 4 по-новой.

Делаем по-быстрому коннект/дисконнект в соседней закладке запроса и смотрим, что появилось в очереди:

рис.2

Читаем из очереди последнее уведомление о событии:

select top 1 cast(message_body as xml) from myEventQueue order by queuing_order desc

<EVENT_INSTANCE>

<EventType>AUDIT_LOGOUT</EventType>

<NTUserName>Administrator</NTUserName>

<NTDomainName>Vistax86SQL2008</NTDomainName>

<HostName>VISTAX86SQL2008</HostName>

<ApplicationName>Microsoft SQL Server Management Studio - Transact-SQL IntelliSense</ApplicationName>

<LoginName>Vistax86SQL2008\Administrator</LoginName>

<ServerName>VISTAX86SQL2008</ServerName>

<DatabaseName>master</DatabaseName>

<LoginSid>AQUAAAAAAAUVAAAAXK9AlSunRrU26ZM09AEAAA==</LoginSid>

<SessionLoginName>Vistax86SQL2008\Administrator</SessionLoginName>

</EVENT_INSTANCE>

Скрипт 7

и видим, что действительно событие выхода логина .\Administrator с SQL Server зафиксировано. Для передачи информации о событии используется ровно такой же XML, как и возвращаемый функцией EventData() в DDL-триггере.

Все хорошо, единственно плохо, что нужно всякий раз прочитывать очередь в поисках сообщения о логофф. Это как если бы телефон не подавал никаких сигналов по прибытию входящего звонка. Приходилось бы каждый миг снимать трубку и слушать, нет ли кого часом на том конце провода. Проще говоря, требуется обработчик события поступления сообщения в заданную очередь. Совершенно случайно такой рояльчег имеется у нас в кустах, в смысле, в Т-SQL, и называется процедурой очереди. Поскольку очередь можно подвести под абстракцию таблицы, то и процедуру очереди я бы на месте Козьмы Пруткова смело уподобил триггеру на insert в эту таблицу.

if objectproperty(object_id('dbo.QueueProcessing'), 'IsProcedure') = 1 drop proc dbo.QueueProcessing

create proc dbo.QueueProcessing as begin

declare @msg_body as xml, @msg_type as nvarchar(256)

while 1 = 1 begin

waitfor (

receive top(1) @msg_type = message_type_name, @msg_body = message_body

from myEventQueue

)

insert LogoutLog values (@msg_body.value('*[1]/EndTime[1]', 'datetime2'), @msg_body.value('*[1]/SessionLoginName[1]', 'sysname'),

@msg_body.value('*[1]/HostName[1]', 'sysname'), @msg_body.value('*[1]/ApplicationName[1]', 'sysname'),

@msg_body.value('*[1]/DatabaseName[1]', 'sysname'))

end

Скрипт 8

Эта процедура будет вызываться всякий раз, когда в очередь myEventQueue валится сообщение. Она будет вытаскивать последнее сообщение из очереди. Структуру очереди можно посмотреть на рис.2. RECEIVE отличается от SELECT тем, что не только прочитывает, но и удаляет прочитанное сообщение из очереди. Остается распарсить message_body (Скрипт 7), вытащив из него интересующие элементы и сложив их в таблицу LogoutLog, которая будет у нас чем-то вроде журнала событий выхода:

if exists(select 1 from sys.tables where name = 'LogoutLog') drop table LogoutLog

create table LogoutLog(когда datetime2, кто sysname, откуда sysname, приблуда sysname, база sysname)

Скрипт 9

Слегка видоизменяем создание очереди (Скрипт 2), привязав к ней процедуру обработки (Скрипт 8). max_queue_readers означает максимальное кол-во копий процедуры, которые будут стартованы для разгреба очереди:

if exists(select 1 from sys.services where name = 'myNotifications') drop service myNotifications

if exists(select 1 from sys.service_queues where name = 'myEventQueue') drop queue myEventQueue

create queue myEventQueue with activation (

status = on,

procedure_name = dbo.QueueProcessing,

max_queue_readers = 2,

execute as self

)

Скрипт 10

Остается озаботиться насчет сервиса по контракту [https://schemas.microsoft.com/SQL/Notifications/PostEventNotification], привязанного к созданной очереди, и уведомления о событиях LOGOUT, привязанного к созданному сервису. Это Скрипты 3 и 4, они выполняются без изменений. После этого проворно делаем connect/disconnect и читаем таблицу LogoutLog:

рис.3

Подобным образом можно отслеживать не только факт логоффа, но и остальные 363 системных события и групп событий, перечисленных в sys.event_notification_event_types. От DDL-триггеров основанный на событиях аудит отличается своей асинхронной природой. Например, подобно предыдущему посту мы могли бы сделать аудит логона, отслеживая в очереди событие AUDIT_LOGON. Чем бы этот механизм отличался от предыдущего поста? Тем, что если в триггере фейс-контроль не прошел, он не пустит на сервер ни под каким видом, а в случае основанного на событиях аудита пройти удастся всегда и лишь потом будут разбираться. Разумеется, в процедуру обработки очереди можно засунуть выкид с сервера непонравившегося логона, но это произойдет асинхронно уже после факта захода, когда он, вообще говоря, уже может совершить какую-нибудь гадость. Для логоффа это менее критично, т.к. при аудите обычно просто регистрируется, что такой-то во столько-то вышел из системы. На практике я не встречал задач, когда при этом требовалось бы предпринять какие-либо проактивные действия. Трудно представить ситуацию, когда, допустим, человек делает SqlConnection.Close(), а она ему - куда, блин? Стоять, блин! А ну назад! Он просто плюнет и уйдет, оставив соединение болтаться. Скорее всего, в таких сценариях при логоффе требуется прибрать за ним какие-нибудь ресурсы. Это можно делать асинхронно рассмотренным здесь способом.

Freigeben über

Аудит в SQL Server. Уведомления о событиях.

Zusätzliche Ressourcen