Аудит в SQL Server. Триггер на логон.

  • Article

Следующая серия постов основывается на докладе, который я делал на Russian SQL Server User Group в Москве в сентябре 2007 г. Кстати, недавно (в мае) схожую тему затрагивал Дмитрий Костылев (https://blogs.gotdotnet.ru/personal/DeColores/PermaLink.aspx?guid=400a42fa-9333-476a-b2d5-e9a810954a0b). Я не могу сослаться на оригинальную сентябрьскую встречу на https://sql.ineta.ru, потому что, похоже, Вадим Осовитный затеял там очередную реконструкцию и пункт "Встречи" из меню исчез. Зато появился блог Вадима. Без сомнения, мне очень интересны его мысли по поводу Silverlight, погоды в Лондоне и жизни вообще. Единственно, непонятно, какое отношение это имеет к Russian SQL Server User Group.

 

Ладно. Не везет мне с сайтом – повезет где-нибудь еще. Данный пост будет полезен консерваторам, сидящим на SQL Server 2005, а также пользователям SQL Server 2008 не Enterprise (и не Developer) Edition. Короче, где оператора CREATE AUDIT еще или уже не было, а аудит тем не менее провести очень хочется. Вообще, аудит на латыни означает слушание и применяется в мировой практике для обозначения проверки, ревизии. Ровно в том же смысле будем его понимать и мы, только наша бухгалтерия завязана на SQL Server. Т.е. кто зашел на SQL Server, когда, откуда, с какой целью (что делал, чем интересовался, до каких данных хотел долезть и насколько у него это получилось) и т.д. Традиционно для этих целей используются SQL Trace (Profiler), триггеры на DDL / DML, а также читалки лога (покупные или самописные).

Краткая напоминалка по DDL-триггерам. В SQL Server бывают только after. Задаются на двух уровнях: сервер или БД. На каждом уровне фиксирован список событий. События можно посмотреть здесь - https://msdn.microsoft.com/ru-ru/library/bb522542.aspx. Всего имеется 13 штук серверных событий

image001_thumb1

рис.1

и 77 штук событий уровня БД:

 

image002_thumb6

рис.2

Пример DDL-триггера:

use tempdb

if exists(select 1 from sys.triggers where parent_class_desc = 'DATABASE' and name = 'tr1') drop trigger tr1 on database

go

create trigger tr1 on database for CREATE_TABLE, ALTER_TABLE, DROP_TABLE as

select EventData()

raiserror('Все операции над таблицами временно прекращены. Администрация.', 16, 1)

rollback

go

Скрипт 1

Пробуем:

 

create table t(id int)

 

и получаем ошибку:

 

image003_thumb3

рис.3

Смотрим список таблиц:

select * from sys.tables where type = 'U'

и видим, что таблица действительно не была создана, т.е. транзакция откатилась.

DDL-триггеры можно создавать не только на одно или несколько событий, но и на группы. А также на отдельные события и группы, на несколько групп и т.д. Группы – это предопределенные объединения родственных событий. См. https://msdn.microsoft.com/ru-ru/library/bb510452.aspx.

image006_thumb6

рис.4

Вместо таблиц inserted / deleted информация об окружении в момент срабатывания триггера передается в ф-ции EventData(), которая возвращает XML вида:

<EVENT_INSTANCE>

  <EventType>CREATE_TABLE</EventType>

  <PostTime>2007-09-14T18:59:28.357</PostTime>

  <SPID>52</SPID>

  <ServerName> VISTA</ServerName>

  <LoginName> VISTA\Leshik</LoginName>

  <UserName>dbo</UserName>

  <DatabaseName>tempdb</DatabaseName>

  <SchemaName>dbo</SchemaName>

  <ObjectName>t</ObjectName>

  <ObjectType>TABLE</ObjectType>

  <TSQLCommand>

    <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />

    <CommandText>create table t(id int)</CommandText>

  </TSQLCommand>

</EVENT_INSTANCE>

Скрипт 2

Таким образом, при помощи DDL-триггеров и функции EventData() можно получать информацию обо всех перечисленных на рис.1, 2 действиях, а также о том, кто, когда и как их совершил. Остается вопрос с аудитом логона, потому что формально логон не является DDL-операцией - в T-SQL нет команды типа CREATE SESSION. Поэтому в 2005 SP2 был добавлен якобы еще один тип триггера (LOGON), который в BOL в CREATE TRIGGER прописан отдельно от DML- и DDL-триггеров - https://msdn.microsoft.com/ru-ru/library/ms189799.aspx. Если не заостряться на формальностях, это обычный DDL-триггер уровня сервера. Когда он заканчивается commitом, SQL Server разрешает соединение, когда rollbackом – отсылает. Смотрим:

use tempdb

if exists(select 1 from sys.server_triggers where name = 'tr1') drop trigger tr1 on all server

go

create trigger tr1 on all server for logon as

if original_login() = 'sa' commit -- sa пускаем всегда

declare @MaxConnects int

set @MaxConnects = 1

if (select count(1) from sys.dm_exec_sessions where is_user_process = 1 and original_login_name = original_login()) <= @MaxConnects

 commit

else

 rollback

go

Скрипт 3

Триггер на логон срабатывает после фазы аутентификации, но до установления пользовательской сессии, поэтому писать к rollback разные raiserror или print с сообщением, почему он не прошел, говорить ай-яй-яй, низя, показывать язык и пр. бессмысленно – юзер все равно ничего этого не увидит.

Вот обещаный аудит логона:

if exists(select 1 from sys.tables where name = ' ЖурналЛогонов') drop table ЖурналЛогонов

create table ЖурналЛогонов

(id int identity, когда datetime, куда sysname, кто sysname, тип_события sysname, спид int, клиентская_тачка sysname null, прикладуха sysname null)

if exists(select 1 from sys.server_triggers where name = 'tr1') drop trigger tr1 on all server

go

create trigger tr1 on all server for logon as

declare @eventdata xml

set @eventdata = EventData()

insert tempdb.dbo. ЖурналЛогонов (когда, куда, кто, тип_события, спид, клиентская_тачка, прикладуха)

values (

      @eventdata.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime'),

      @eventdata.value('(/EVENT_INSTANCE/ServerName)[1]', 'sysname'),

      @eventdata.value('(/EVENT_INSTANCE/LoginName)[1]', 'sysname'),

      @eventdata.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname'),

      @eventdata.value('(/EVENT_INSTANCE/SPID)[1]', 'int'),

      @eventdata.value('(/EVENT_INSTANCE/ClientHost)[1]', 'sysname'),

       App_Name()

      )

commit

Скрипт 4

Выполняем скрипт 4 и тестируем созданный триггер, организовав новую сессию:

image007

рис.5

image009_thumb2

рис.6

Видим, что попытки логона теперь фиксируются в указанной таблице:

 

image011

рис.7

Вопрос: как модифицировать предыдущий скрипт, если мы хотим клиента не пущать, а его умысел залогиниться всеж-таки зафиксировать? Элементарно: надо вместо commit поставить rollback:

truncate table tempdb.dbo.ЖурналЛогонов

 

if exists(select 1 from sys.server_triggers where name = 'tr1') drop trigger tr1 on all server

go

create trigger tr1 on all server for logon as

declare @eventdata xml

set @eventdata = EventData()

rollback --Откатываем = не пущаем

begin tran --транзакцию вставки в журнал фиксируем

insert tempdb.dbo.ЖурналЛогонов (когда, куда, кто, тип_события, спид, клиентская_тачка, прикладуха)

values (

      @eventdata.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime'),

      @eventdata.value('(/EVENT_INSTANCE/ServerName)[1]', 'sysname'),

      @eventdata.value('(/EVENT_INSTANCE/LoginName)[1]', 'sysname'),

      @eventdata.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname'),

      @eventdata.value('(/EVENT_INSTANCE/SPID)[1]', 'int'),

      @eventdata.value('(/EVENT_INSTANCE/ClientHost)[1]', 'sysname'),

       App_Name()

      )

commit

Скрипт 5

 

При попытке логона клиент получит отлуп:

 

image013_thumb3

рис.8

Меж тем его попытка будет отражена в таблице:

image015_thumb2

рис.9

Вопрос: что будет, если я сейчас выйду из текущей сессии, а триггер останется висеть и посылать всех на? Или, скажем, я допустил ошибку, когда его писал. Имеется в виду не синтаксическая, а времени выполнения. Например, укажем в INSERT несуществующую таблицу. При возникновении run-time error выполнение триггера прекращается и делается неявный откат вместо ошибочного оператора. То есть вместо INSERT получим ошибку и ROLLBACK. Это тоже означает все в сад. Предположим, посмотреть текст ошибки можно SQLном ErrorLoge. Кто забыл, где находится директория с журналами, может обратиться к https://blogs.msdn.com/alexejs/archive/2009/06/25/9803218.aspx.

image017_thumb2

рис.10

Теперь, когда ошибка понятна, возникает вопрос, как ее исправить, если мы уже вышли из редактора, потому что триггер никому не даст залогиниться на сервер.

Два возможных пути для этого имеются. Первый путь - перестартовать SQL Server в минимальной конфигурации. Например, из одного командного окна делаем:

net stop mssqlserver

“C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr.exe” –c –f

А из другого:

sqlcmd

drop trigger tr1 on all server.

Если забыли, как он, треклятый, называется, воспользуйтесь select * from sys.server_triggers.

Второй путь – это DAC (выделенное административное соединение) - https://msdn.microsoft.com/ru-ru/library/ms178068.aspx. SSMS сначала будет пытаться приконнектить Object Explorer (если он был открыт), который не поддерживает DAC. Забейте на него, и соединяйте окно запросов. Из окна запросов нужно прикончить глюкавый триггер, после чего на сервер станет возможно логиниться обычным порядком.