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)
Подписаться