Оператор 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

 

clip_image002image

рис.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

clip_image006 clip_image008

рис.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

 

image

рис.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

 

clip_image012 clip_image014

рис.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

1

SELECT

1

1

1

55

W7X86SQL08R2\Administrator

261

dbo

1

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

1

LOGIN FAILED

0

0

0

0

sa

0

 

0

LOGIN

W7X86SQL08R2

     

0

Login failed for user 'sa'. Reason: Password did not match that for the login provided. [CLIENT: <local machine>]

<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. По умолчанию, открывается текущий файл аудита.

 

image

рис.5

 

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

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

 

Comments (4)

  1. isvrus says:

    И Вам спасибо. Приятно видеть, что пригодилось.

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

  2. Anonymous says:

    Полезная статья! Большое спасибо за обзор этого оператора я как раз мучался…

  3. isvrus says:

    Аудит логинов – http://www.kodyaz.com/…/sql-server-login-auditing.aspx

    Блэк лист – превентивное действие, это уже не задача аудита. Отсеивать попытки нежелательных коннектов можно при помощи триггера на логон – blogs.msdn.com/…/p20090714_5f00_2.aspx

    blogs.msdn.com/…/ip.aspx

  4. Cometa says:

    а есть ли возможность с помощью аудита, собрать инфо по подключениям к бд и создать блэк лист, для определённых конектов?

Skip to main content