Оператор 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
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. По умолчанию, открывается текущий файл аудита.
рис.5
При задании файла с результатами аудита мы указывали только папку. В какой именно файл сейчас идет запись, можно посмотреть в sys.dm_server_audit_status. В каком именно файле лежит то или иное событие - колонка file_name в sys.fn_get_audit_file. Приостановить / остановить сбор событий аудита можно Скриптом 4, сказафф off вместо on. Даже если аудит остановлен, его журналы по-прежнему можно читать аналогично асинхронному файловому таргету в сессии сбора XEvents, т.е. Скрипт 6 работает.
Алексей Шуленин
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.aspxAnonymous
May 15, 2012
а есть ли возможность с помощью аудита, собрать инфо по подключениям к бд и создать блэк лист, для определённых конектов?