Аудит в SQL Server 2012. Что нового!?

21 марта 2013 года прошла вторая онлайн конференция "24 Hours of PASS. Russian Edition". Это крупнейшая русскоязычная конференция, посвященная SQL Server. Доклад, который читал я, был как раз про аудит. В рамках своего блога я решил осветить, что нового появилось в SQL Server 2012 касаемо аудита. Так же в конце этой статьи я выложу презентацию и скрипты из своего доклада, а чуть позже должна будет появиться запись.

Эту статью я хочу начать с перечисления всех инструментов, которые позволяют отслеживать события, происходящие на вашем сервере баз данных. У вас, как всегда, есть возможность использовать, как конкретную возможность для "слежки", так и комбинировать решения. Но в основном в этой заметке я буду повествовать про подсистему аудита (SQL Server Audit), которая появилась в SQL Server 2008 и дополнилась рядом интересных возможностей в SQL Server 2012.

Но обо всем по порядку…

Говоря о том, какие инструменты для аудита и контроля за изменениями данных предоставляет SQL Server, я рекомендую начать с просмотра доклада Дмитрия Костылева(аудит и контроль за изменением данных в ms sql server 2008).

Общий перечень инструментов, удобно изобразить в виде небольшой таблицы:

Инструмент аудита	В каких редакциях SQL Server доступно	Описание	Комментарий
SQL Trace SQL Profiler	Все, при этом приложение SQL Profiler не доступно в редакции Express	Чтобы создать трассировки на экземпляре компонента SQL Server Database Engine, Microsoft SQL Server предоставляет системные хранимые процедуры на языке Transact-SQL. Эти системные хранимые процедуры можно использовать для создания трассировок вручную в рамках пользовательских приложений вместо использования приложения SQL Server Profiler. Это позволяет писать пользовательские приложения, отвечающие конкретным нуждам предприятия.	Эти функции будут удалены в следующем выпуске SQL Server. Устаревшие функции не должны использоваться в новых приложениях. Вместо SQL Trace рекомендуется использовать расширеные события(XEvents)
C2 Audit	Все	Включение этого параметра заставляет сервер регистрировать как успешные, так и неуспешные попытки получения доступа к инструкциям и объектам. Эти сведения позволяют профилировать работу системы и отслеживать возможные нарушения политики безопасности (использует SQL Trace).	В будущей версии Microsoft SQL Server этот компонент будет удален. Избегайте использования этого компонента в новых разработках и запланируйте изменение существующих приложений, в которых он применяется. Стандарт безопасности С2 был заменен стандартом Common Criteria Certification
Common Criteria	Enterprise, Datacenter	Включает следующие элементы, необходимые для поддержки стандарта Common Criteria Защита остаточных данных (RIP) Возможность просматривать статистику имени входа Разрешение GRANT на столбец не переопределяет запрета DENY на таблицу	Помимо включения параметра common criteria compliance enabled, необходимо загрузить и выполнить скрипт, завершающий настройку SQL Server для соответствия стандарту Common Criteria уровня 4 (EAL4+). Загрузить этот скрипт можно с сайта Стандарт Common Criteria для Microsoft SQL Server.
Триггеры DDL/DML	Все	Триггер — это особая разновидность хранимой процедуры, выполняемая автоматически при возникновении события на сервере базы данных. Событиями DML являются процедуры INSERT, UPDATE или DELETE, применяемые к таблице или представлению. Триггеры DDL срабатывают в ответ на ряд событий языка описания данных (DDL). Эти события прежде всего соответствуют инструкциям Transact-SQL CREATE, ALTER, DROP и некоторым системным хранимым процедурам, которые выполняют схожие с DDL операции. Триггеры входа могут срабатывать в ответ на событие LOGON, возникающее при установке пользовательских сеансов.	Необходимо вручную писать свой аудит с помощью триггеров. Трудоемко поддерживать. Вредоносный программный код внутри триггеров может быть запущен с расширенными правами доступа.
Change Tracking	Все	Change Tracking - это простое решение, обеспечивающее эффективный механизм отслеживания изменений для приложений. Отпадает необходимость в самостоятельной реализации отслеживания изменений, например с помощью триггеров.	Факт изменения строки фиксируется синхронно в одной транзакции с самой операцией изменения. Информация об изменениях доступна немедленно. Не содержит промежуточных данных и информации о кол-ве произведенных изменений, только информация о факте изменения записей с определенного момента времени. Если приложению необходимы данные обо всех произведенных изменениях и все промежуточные значения, следует воспользоваться Change Data Capture
Change Data Capture	Enterprise, Datacenter	Система отслеживания измененных данных регистрирует операции вставки, обновления и удаления, которые применяются к таблице SQL Server. Тем самым обеспечивается доступ к подробностям этих изменений в легко обрабатываемом реляционном формате. Сведения о столбцах и метаданных, которые требуются для применения изменений к целевой среде, отслеживаются в измененных строках и хранятся в таблицах изменений, отражающих структуру столбцов исходных таблиц.	Работает асинхронно, поэтому существует небольшая задержка фиксации изменений. Внутри себя использует механизмы схожие с работой репликации. Все изменения фиксируются и считываются из журнала транзакций.
XEvents	Все	Расширенная подсистема событий SQL Server имеет чрезвычайно масштабируемую и легко настраиваемую архитектуру, которая позволяет пользователям собирать именно такое количество информации, которое необходимо для устранения нарушения в работе или выявления проблемы производительности.	Является основным инструментом фиксации изменений в последних версия SQL Server. Высокопроизводительное решение (в сравнении с SQL Trace).
SQL Audit	Начиная с SQL Server 2012, базовый аудит доступен во всех редакциях. Аудит БД доступен только в Enterprise и Datacenter	Аудит — это сочетание в едином пакете нескольких элементов для определенной группы действий сервера или базы данных. Аудит среды SQL Server позволяет проводить аудит сервера, который может включать в себя спецификации аудита сервера для событий на уровне сервера, а также спецификации аудита базы данных для событий на уровне базы данных. События аудита могут записываться в журналы событий или файлы аудита.	Подсистема аудита SQL Server использует расширенные события (XEvents) для создания аудита.

Что нового появилось в SQL Audit с выходом SQL Server 2012:.

Теперь разберем все эти улучшения более подробно.

Аудит на уровне сервера поддерживается во всех выпусках SQL Server.
Аудит на уровне базы банных доступен только в выпусках Datacenter, Enterprise Edition, Developer Edition и Evaluation Edition.

Аудит может быть сохранён в один из трёх источников:

• В файл (File)
• В журнал приложений Windows (Windows Security Log)
• В журнал безопасности Windows (Windows Application Log)

Любой прошедший проверку пользователь может осуществлять чтение и запись в журнале событий приложений. Для работы с журналом событий приложений необходимо меньше разрешений, чем для работы с журналом событий безопасности Windows; журнал событий приложений менее защищен, чем журнал событий безопасности Windows.

Дополнительные сведения см. в разделе Возможности, поддерживаемые различными выпусками SQL Server 2012.

Теперь создадим аудит. Это можно сделать, как запросом на T-SQL, так и с помощью SSMS. Для этого необходимо перейти во вкладку Security -> Audits



Теперь правой кнопкой мыши выбираем New Audit...



Давайте рассмотрим более подробно параметры, которые мы можем здесь заполнить.

• Audit name - название аудита
• Queue delay - Определяет задержку в миллисекундах, после которой продолжается выполнение действий аудита. Значение 0 соответствует синхронной доставке. Минимальное значение задаваемой задержки запроса составляет 1000 (1 секунда), и это значение используется по умолчанию.
• On Audit Log Failure - Указывает, будет ли экземпляр, выполняющий запись в целевой объект, вызывать ошибку (Fail operation), продолжать работу (Continue) или останавливать SQL Server (Shut down server), если целевой объект не может выполнить запись в журнал аудита. Значение по умолчанию — CONTINUE.
• Audit destination - Определяет расположение целевого объекта аудита. Возможными параметрами являются двоичный файл, журнал приложений Windows или журнал безопасности Windows.
• File path - Путь к журналу аудита. Имя файла формируется на основе имени аудита и его идентификатора GUID.
• Maximum rollover files - Указывает максимальное количество файлов, хранимых в файловой системе помимо текущего. Значением MAX_ROLLOVER_FILES должно быть целое число или UNLIMITED. Значение по умолчанию — UNLIMITED.
• Maximum files - Задает максимальное число файлов аудита, которые могут быть созданы. При достижении предела переключение на первый файл не производится. При достижении предела MAX_FILES любое действие, которое вызывает создание дополнительных событий аудита, завершится ошибкой.
• Reserve disk space - Этот параметр заранее размещает на диске файл в соответствии со значением MAXSIZE. Применяется, только если MAXSIZE не имеет значения UNLIMITED. Значение по умолчанию — OFF.

Красным выделены новые параметры аудита, которые появились в SQL Server 2012. Особое внимание стоит уделить параметру “ON_FAILURE = FAIL_OPERATION” - Действия с базой данных завершаются ошибкой, если они вызывают события аудита. Действия, которые не вызывают события аудита, можно продолжать выполнять. Аудит продолжает попытки регистрации событий и будет возобновлен, если причина сбоя будет устранена. Используйте этот параметр, если обеспечение полного аудита более важно, чем полный доступ к данным.



Создание Аудита с помощью запроса будет выглядеть примерно так:

use master;
go
create server audit [24PASSAuditDemo]
to file (	 filepath = N'c:\temp'
	       , maxsize = 0 mb
	       , max_rollover_files = 2147483647
	       , reserve_disk_space = off
        )
with (	queue_delay = 1000
	    , on_failure = continue
     );
go

После создания, аудит нужно включить:

use master;
go
alter server audit [24PASSAuditDemo] with ( state = on );

Теперь можно создать Спецификацию аудита сервера, которая фиксирует события уровня сервера.



либо Спецификацию аудита базы данных, которая включает действия аудита уровня базы данных.

Список событий, на которые срабатывает аудит сервера и БД можно прочитать по ссылке: http://msdn.microsoft.com/ru-ru/library/cc280663.aspx.

Для демонстрации нового функционала: фиксация T-SQL стека выполнения и пользовательских событий, создадим базу данных DemoDB_24PASS. И в рамках новой БД создадим одну таблицу dbo.Speaker24PASS за которой и будем следить.

use master;
go

if db_id( 'DemoDB_24PASS' ) is not null
drop database DemoDB_24PASS;
go

create database DemoDB_24PASS;
go

use DemoDB_24PASS;
go

create table dbo.Speaker24PASS( id int identity
                              , Speaker nvarchar(255)
                              , City nvarchar(64)
                              );
go
insert into dbo.Speaker24PASS
values ( N'Коршиков Андрей', N'Краснодар' ), ( N'Коннова Татьяна', N'Сидней' ), ( N'Павлюк Елена', N'Тампа' )
     , ( N'Колесник Анатолий', N'Харьков' ), ( N'Михалев Сергей', N'Санкт-Петербург' ), ( N'Хомяков Константин', N'Киев' )
     , ( N'Панов Кирилл', N'Екатеринбург' ), ( N'Гилёв Вячеслав', N'Москва' ), ( 'Халяко Алексей', N'Мюнхен' )
     , ( N'Косяков Иван', N'Москва' ), ( N'Нейгебауэр Нико', N'Лиссабон' ), ( 'Короткевич Дмитрий', N'Тампа' )
     , ( N'Лемешко Максим', N'Ставрополь' ), ( N'Князев Алексей', N'Екатеринбург' ), ( 'Пилюгин Дмитрий', N'Москва' )
     , ( N'Голубец Игорь', N'Кёльн' ), ( N'Олонцев Сергей', N'Москва' ), ( 'Косинский Константин', N'Рэдмонд' )
     , ( N'Резник Денис', N'Киев' ), ( N'Комаров Михаил', N'Москва' ), ( 'Костылев Дмитрий', N'Москва' )
     , ( N'Матеев Михаил', N'София' ), ( N'Кривошеев Евгений', N'Рэдмонд' );
go

select * from dbo.Speaker24PASS;
go

Теперь добавим интерфейсную процедуру для доступа к таблице

create procedure dbo.Get_Speaker
@id int
as
select Speaker, City 
  from dbo.Speaker24PASS
  where id = @id;
go

exec dbo.Get_Speaker @id = 10;
go

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

--Create New Login/User
if exists ( select * from sys.server_principals
              where name = 'TestLogin'
          )
drop login TestLogin;
go
create login TestLogin with password = '12345', check_policy = off;
go
create user TestUser for login TestLogin;
go

grant execute on dbo.Get_Speaker to TestUser;
go
deny select on dbo.Speaker24PASS to TestUser;
go

Теперь с помощью SSMS создадим аудит на событие SELECT из нашей таблицы. Databases -> DemoDB_24PASS -> Security -> Database Audit Specifications



Правой кнопкой мыши New Database Audit Specification...



Скрипт на T-SQL:

create database audit specification [DatabaseAuditSpecification-24PASS]
for server audit [24PASSAuditDemo]
add ( select on object::[dbo].[Speaker24PASS] by [public] );

Так же, как и Аудит, спецификацию необходимо активировать:

alter database audit specification [DatabaseAuditSpecification-24PASS]
with ( state = on );

Теперь подключимся к нашему серверу БД с новой УЗ TestLogin и выполним два запроса: чтение данных из таблицы прямым запросом и с помощью интерфейсной процедуры:

use DemoDB_24PASS;
go

select Speaker, City 
  from dbo.Speaker24PASS
  where id = 10;
go

exec dbo.Get_Speaker @id = 10;
go

А теперь обратимся к логу аудита: Security -> Audits -> 24PASSAuditDemo и правой кнопкой мыши View Audit Logs



В журнале зафиксировано две попытки чтения данных из таблицы, одно удачное (доступ через процедуру), а другое завершилось ошибкой. Теперь прокрутим таблицу вправо и посмотрим, что записано в столбцах Statement и Additional Information для доступа к таблице через процедуру:



Чтение журнала аудита можно осуществлять и с помощью запроса:

select * 
from fn_get_audit_file( 'c:\temp\*_5e860b00-a2ed-46be-8ebb-128090b7a1d1*', null, null ) 
order by event_time desc
        ,sequence_number

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



sp_audit_write - Добавляет определяемое пользователем событие аудита в группу USER_DEFINED_AUDIT_GROUP. Если группа USER_DEFINED_AUDIT_GROUP не включена, sp_audit_write игнорируется.

Нам необходимо добавить USER_DEFINED_AUDIT_GROUP в нашу спецификацию, но чтобы внести изменения в спецификацию её сперва нужно остановить.

alter database audit specification [DatabaseAuditSpecification-24PASS]
with ( state = off );
go
alter database audit specification [DatabaseAuditSpecification-24PASS]
add ( user_defined_audit_group );
go
alter database audit specification [DatabaseAuditSpecification-24PASS]
with ( state = on );
go

Теперь добавим пару пользовательских событий, при этом идентификатор события может быть произвольным числом (например, номер из вашего внутреннего справочника ошибок/сообщений), а текст может быть, как константой, так и переменной текстового типа:

use DemoDB_24PASS;
go

exec sp_audit_write 1234, 1, N'Hello World';
go

declare @str nvarchar(128) = convert( varchar, getdate(), 104 );
exec sp_audit_write 911, 1, @str;
go

Теперь обратимся к нашему логу аудита:

select action_id, succeeded, statement, user_defined_event_id, user_defined_information
from fn_get_audit_file( 'c:\temp\*_5e860b00-a2ed-46be-8ebb-128090b7a1d1*', null, null ) 
where action_id = 'UDAU'
order by event_time desc
        ,sequence_number

Теперь поговорим о фильтрованных событиях аудита.



Для демонстрации изменим наш аудит и добавим в качестве условия фильтр по полю user_defined_event_id.

use master;
go

alter server audit [24PASSAuditDemo] with ( state = off );
go
alter server audit [24PASSAuditDemo]
      where user_defined_event_id = 911;
go
alter server audit [24PASSAuditDemo] with ( state = on );
go

Но добавить условие можно и через SSMS. В настройках Аудита появилась новая вкладка Filter



Теперь из всех событий в журнал будут попадать только пользовательские события с значением user_defined_event_id = 911.

Ну и в завершении пару слайдов по поводу производительности Аудита:






Полная версия презентации: 24HoursofPASS_a.knyazev.pptx (948,49 kb)
Скрипты из демонстрации: Audit_Demo.zip (9,10 kb)