Оператор CREATE AUDIT

Article
01/16/2010

Ранее мы говорили о разных способах аудита событий входа/выхода пользователей на/с 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

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

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
TestAudit	2010/01/16 20:39:54	FILE	2	3	c:\Temp\	TestAudit_FC0517C7-88DA-46BD-B54E-EE50442BC7D1.sqlaudit	CONTINUE	1	1000

Скрипт 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

sequence_number

name

succeeded

permission_bitmask

is_column_permission

session_id

server_principal_name

server_principal_id

database_principal_name

database_principal_id

class_type_desc

server_instance_name

database_name

schema_name

object_name

object_id

statement

additional_information

file_name

audit_file_offset

SELECT

W7X86SQL08R2\Administrator

261

dbo

TABLE

W7X86SQL08R2

AdventureWorks2008R2

Person

PersonPhone

1653580929

SELECT [PhoneNumber] FROM [AdventureWorks2008R2].[Person].[PersonPhone] WHERE [BusinessEntityID]=@1

c:\Temp\TestAudit_FC0517C7-88DA-46BD-B54E-EE50442BC7D1_0_129081384184950000.sqlaudit

2048

W7X86SQL08R2

<action_info xmlns="http://schemas.microsoft.com/sqlserver/2008/sqlaudit_data"><pooled_connection>0</pooled_connection><error>0x00004818</error><state>8</state><address>local machine</address></action_info>

c:\Temp\TestAudit_FC0517C7-88DA-46BD-B54E-EE50442BC7D1_0_129081384184950000.sqlaudit

1024

Скрипт 6

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

рис.5

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

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

Comments

Anonymous
January 01, 2003
И Вам спасибо. Приятно видеть, что пригодилось. Мы стараемся отражать в блоге разные интересные моменты, интересные, в первую очередь, с точки зрения актуальности и практической пользы. Когда видишь, что это действительно так, это воодушевляет.
Anonymous
January 01, 2003
Полезная статья! Большое спасибо за обзор этого оператора я как раз мучался...
Anonymous
January 01, 2003
Аудит логинов - www.kodyaz.com/.../sql-server-login-auditing.aspx Блэк лист - превентивное действие, это уже не задача аудита. Отсеивать попытки нежелательных коннектов можно при помощи триггера на логон - blogs.msdn.com/.../p20090714_5f00_2.aspx blogs.msdn.com/.../ip.aspx
Anonymous
May 15, 2012
а есть ли возможность с помощью аудита, собрать инфо по подключениям к бд и создать блэк лист, для определённых конектов?

Partager via

Оператор CREATE AUDIT

Comments

Ressources supplémentaires