Оператор CREATE AUDIT

Ранее мы говорили о разных способах аудита событий входа/выхода пользователей на/с SQL Server (триггер на логон, уведомления о событиях) и отслеживания сделанных ими изменений (Change Data Capture, Change Tracking). Понятие аудита достаточно близко соприкасается с задачами отслеживания изменений и зачастую использует схожие инструменты, однако, в целом, понимается шире. Во-первых, например, операция чтения не меняет данных, однако с точки зрения аудита, возможно, представляет интерес, кто, когда, откуда, к каким данным доступился. Во-вторых, под системным аудитом также понимается отслеживание изменений не только данных, но и различных параметров настройки системы и изменение ее поведения в ответ на это. В принципе, это решается на уровне XEvents и ETW. Расширенные события мы рассматривали недавно применительно к выявлению устаревшей функциональности после перехода на SQL Server 2008 и давно, когда нужно было отслеживать ситуацию возникновения чекпойнта. Аудит в SQL Server основан на расширенных событиях и потому процесс создания аудита выглядит очень похоже. Мы уже пользовались возможностями аудита, когда при прохождении Сценария №2 теста Works with SQL Server 2008 необходимо было удостовериться, что подключаемые в процессе работы приложения учетные записи приложения не входят в серверную роль sysadmin. Разнообразим спецификацию аудита. Пусть мы хотим фиксировать неудачные попытки захода на сервер плюс некоторую пользовательскую активность внутри базы. Создадим аудит:

use master

if exists (select 1 from sys.server_audits where name = 'TestAudit') begin

 alter server audit TestAudit with (state = off);

 drop server audit TestAudit

end

В его параметрах прописывается, куда сбрасываются результаты, с какой периодичностью и что делать при сбое. Поддерживается вывод пойманных событий в файл, в Windows Application или Security log (см. http://technet.microsoft.com/ru-ru/library/cc280448.aspx).

create server audit TestAudit

    to file (filepath = 'c:\Temp', maxsize = 2MB, max_rollover_files = 3, reserve_disk_space = off)

    with (queue_delay = 1000, on_failure = continue)

queue_delay = 0 означает синхронный аудит, т.е. событие должно быть записано в таргет немедленно после того, как оно было зафиксировано. Синхронный аудит может вызвать замедления в производительности. Во всех остальных случаях аудит происходит асинхронно. Минимальное значение queue_delay = 1000 (мс), а максимальное - 2147483647 мс (в BOL опечатка - не секунды, а миллисекунды).

Посмотреть шапку аудита в SSMS можно так: Server -> Security -> Audits -> Properties

рис.1

После того, как прописана шапка аудита, необходимо указать, какие события он будет ловить на уровне сервера и(или) какие на уровне БД. В данном случае на уровне сервера будем ловить неудачные попытки логона:

if exists (select 1 from sys.server_audit_specifications where name = 'Srv_Spec') begin

 alter server audit specification Srv_Spec with (state = off)

 drop server audit specification Srv_Spec

end

create server audit specification Srv_Spec for server audit TestAudit

    add (FAILED_LOGIN_GROUP)

    with (state = on)

Скрипт 2

Посмотреть серверную спецификацию аудита в SSMS можно так: Server -> Security -> Server Audit Specifications -> Properties

рис.2

Список аудируемых событий и групп событий можно посмотреть в BOL или в DMV sys.dm_audit_actions.

select distinct name, class_desc, parent_class_desc, containing_group_name from sys.dm_audit_actions order by name

рис.3

Шапка аудита и спецификация уровня сервера задаются в контексте БД master. Спецификацию уровня базы данных задаем, перейдя в эту базу.

use AdventureWorks2008R2

if exists (select * from sys.database_audit_specifications where name = 'DB_Spec') begin

 alter database audit specification DB_Spec with (state = off)

 drop database audit specification DB_Spec

end

go

create database audit specification DB_Spec for server audit TestAudit

    add (SELECT on OBJECT::Person.PersonPhone by public),

    add (SELECT, INSERT, UPDATE, DELETE on OBJECT::Person.Password by public),

    add (EXECUTE on dbo.uspGetEmployeeManagers by dbo)

    with (state = on)

Скрипт 3

Давайте разберем первую строчку add. Здесь SELECT - один из actions, к-е мы видели в колонке name в sys.dm_audit_actions; после ON следует сущность из колонки class_desc, относящаяся к этому действию, а после :: имя конкретного экземпляра этой сущности, в данном случае таблица PersonPhone схемы Person; после by - пользователь или роль. Всего на уровне базы собираемся ловить чтение таблицы Person.PersonPhone, любой DML над таблицей Person.Password и событие вызова процедуры dbo.uspGetEmployeeManagers.

Посмотреть спецификацию аудита уровня БД в SSMS можно так: БД -> Security -> Database Audit Specifications -> Properties

рис.4

Включаем созданный аудит:

use master

alter server audit TestAudit with (state = on)

Скрипт 4

Производим аудируемые события. Имитируем неудачный логон и читаем из таблицы Person.PersonPhone:

select PhoneNumber from AdventureWorks2008R2.Person.PersonPhone where BusinessEntityID = 100

Посмотрим, куда в данный момент пишутся результаты аудита

select name, modify_date, type_desc, max_file_size, max_rollover_files, log_file_path, log_file_name, on_failure_desc, is_state_enabled, queue_delay from sys.server_file_audits

Скрипт 5

Ф-ции fn_get_audit_file можно сказать читать из конкретного файла аудита или из всех в данной папке. Как и в случае XEvents, ее 2-й параметр - это имя файла, с которого начать производить выборку, а 3-й - смещение. Время также показывается GMT. В данном случае для увеличения читаемости табл.ф-ция fn_get_audit_file джойнится с DMV sys.dm_audit_class_type_map, чтобы получить осмысленное описание типа объекта, засветившегося в аудируемой операции (TABLE вместо U, LOGIN вместо LX и т.д.) и с DMV sys.dm_audit_actions, чтобы получить осмысленное описание результата действия над этим объектом (SELECT вместо SL, LOGIN FAILED вместо LGIF и т.д.) Сам логин можно видеть в колонке server_principal_id, соответствующего ему юзера базы - в колонке database_principal_id. При событии LOGIN FAILED до конкретной базы дело, понятно, не доходит, поэтому юзер будет никакой.

declare @s nvarchar(1000)

select @s = log_file_path from sys.server_file_audits where name = 'TestAudit'

select f.event_time, f.sequence_number, a.name, f.succeeded, f.permission_bitmask, f.is_column_permission,

       f.session_id, f.server_principal_name, f.server_principal_id, f.database_principal_name, f.database_principal_id,

       c.class_type_desc, f.server_instance_name, f.database_name, f.schema_name, f.object_name, f.object_id,

       f.statement, f.additional_information, f.file_name, f.audit_file_offset

from sys.fn_get_audit_file(@s + '*', default, default) f

join sys.dm_audit_class_type_map c on f.class_type = c.class_type

join sys.dm_audit_actions a on f.action_id = a.action_id and c.securable_class_desc = a.class_desc

where f.action_id <> 'AUSC'

order by event_time desc, f.sequence_number asc

Скрипт 6

Журнал с результатами аудита можно просматривать из SSMS: см.рис.1 -> View Audit Logs. По умолчанию, открывается текущий файл аудита.

рис.5

При задании файла с результатами аудита мы указывали только папку. В какой именно файл сейчас идет запись, можно посмотреть в sys.dm_server_audit_status. В каком именно файле лежит то или иное событие - колонка file_name в sys.fn_get_audit_file. Приостановить / остановить сбор событий аудита можно Скриптом 4, сказафф off вместо on. Даже если аудит остановлен, его журналы по-прежнему можно читать аналогично асинхронному файловому таргету в сессии сбора XEvents, т.е. Скрипт 6 работает.

Алексей Шуленин