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 TraceSQL 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
|
Помимо включения параметра 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)
Автор — Alexey Knyazev
*** *** *** *** *** *** *** *** *** *** *** ***
Полезные Скрипты
Ссылка на наш канал YouTube
Обсудить тему можно в комментариях или вконтакте.
Не забывайте следить за рубрикой «Грядущие события и мероприятия»