Аудит MS SQL сервера

Время прочтения: 10 мин.

В целях обеспечения контроля доступа к информации, а также действий и операций, выполняемых пользователями на экземпляре СУБД MS SQL корпорация Microsoft предлагает штатный инструмент для аудита MS SQL, который предполагает детальное журналирование следующих событий на экземпляре MS SQL:

1. Детализация и журналирование событий над объектами экземпляра СУБД MS SQL:

— создание;

— изменение;

— удаление.

• Детализация и журналирование событий над объектами БД экземпляра СУБД  MS SQL:

— создание;

— изменение;

— удаление.

•  Детализация и журналирование событий предоставления прав и привилегий к объектам экземпляра СУБД MS SQL;
•  Детализация и журналирование событий предоставление прав и привилегий к БД экземпляра СУБД MS SQL.

В этой статье будут рассмотрены тонкости настройки инструмента для аудита MS SQL от Microsoft и его использования (получения информации для анализа событий).

ДАНО:

Установленный и настроенный экземпляр СУБД MS SQL

ЗАДАЧА:

 — Обеспечить журналирование событий на экземпляре СУБД MS SQL;

— Обеспечить доступ к данным аудита для анализа;

— Обеспечить хранение журналов аудита в течении 0.5 года.

РЕШЕНИЕ:

Для начала необходимо ознакомиться с событиями аудита, которые могут регистрироваться инструментом от Microsoft. Список событий представлен в таблице 1. Знаком «+» я отметил события, которые нужны были мне, в момент настройки, но читатель может отметить интересующие его пункты и добавить их в скрипт создания спецификации аудита (но об этом позже).

Так же хочу отметить возможность настройки фильтра событий аудита, который предполагает исключение классов событий и видов событий из аудита.

Например, пользователь в дереве объектов раскрывает «Базы Данных», далее переходит в интересующую его БД и затем в дерево таблиц.

После описанных выше действий, просматривает структуру таблицы, или др. объекты Эти события нам не интересны, хотя попадают в группу DATABASE_OPERATION_GROUP.

Или, например, в события аудита попадает информация о качестве сетевого соединения пользовательской сессии из группы событий SUCCESSFUL_LOGIN_GROUP, что для меня так же не было интересно, так как это задача мониторинга работоспособности и доступности SQL Server. Кроме того, журналирование и хранение этих событий будет утилизировать вычислительные ресурсы CPU, RAM и HDD.  

Итак, продолжим:

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

Листинг кода создания спецификации для аудита сервера:

USE [master]
GO
ALTER SERVER AUDIT SPECIFICATION [SQL_SRV_AUDIT_SPECIFICATION]
ADD (SCHEMA_OBJECT_ACCESS_GROUP),
ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),
ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),
ADD (BACKUP_RESTORE_GROUP),
ADD (AUDIT_CHANGE_GROUP),
ADD (DATABASE_LOGOUT_GROUP),
ADD (SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP),
ADD (DBCC_GROUP),
ADD (DATABASE_PERMISSION_CHANGE_GROUP),
ADD (DATABASE_OBJECT_PERMISSION_CHANGE_GROUP),
ADD (SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP),
ADD (SERVER_OBJECT_PERMISSION_CHANGE_GROUP),
ADD (SERVER_PERMISSION_CHANGE_GROUP),
ADD (DATABASE_PRINCIPAL_IMPERSONATION_GROUP),
ADD (SERVER_PRINCIPAL_IMPERSONATION_GROUP),
ADD (SUCCESSFUL_LOGIN_GROUP),
ADD (LOGOUT_GROUP),
ADD (DATABASE_CHANGE_GROUP),
ADD (DATABASE_OBJECT_CHANGE_GROUP),
ADD (DATABASE_PRINCIPAL_CHANGE_GROUP),
ADD (SCHEMA_OBJECT_CHANGE_GROUP),
ADD (SERVER_OBJECT_CHANGE_GROUP),
ADD (SERVER_PRINCIPAL_CHANGE_GROUP),
ADD (DATABASE_OPERATION_GROUP),
ADD (APPLICATION_ROLE_CHANGE_PASSWORD_GROUP),
ADD (LOGIN_CHANGE_PASSWORD_GROUP),
ADD (SERVER_STATE_CHANGE_GROUP),
ADD (DATABASE_OWNERSHIP_CHANGE_GROUP),
ADD (DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP),
ADD (SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP),
ADD (SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP),
ADD (TRACE_CHANGE_GROUP),
ADD (USER_CHANGE_PASSWORD_GROUP)
WITH (STATE = ON)
GO 

Далее создадим сам аудит

В выражении WHERE перечисленыклассы событий, исключаемые мной из АУДИТА. Подробнее о классах событий можно прочитать на MSDN.

Листинг кода для создания аудита сервера, с использованием фильтра:

USE [master]
GO

CREATE SERVER AUDIT [SQL_SRV_AUDIT]
TO FILE 
(	FILEPATH = N'C:\AUDIT_SQL\'
	,MAXSIZE = 10240 MB
	,MAX_ROLLOVER_FILES = 100
	,RESERVE_DISK_SPACE = OFF
)
WITH
(	QUEUE_DELAY = 1000
	,ON_FAILURE = CONTINUE
	,AUDIT_GUID = '4fc594e2-5f73-4190-a589-40f25e6bb0dc'
)
WHERE ([class_type]<>(22604) 
AND [class_type]<>(21059) 
AND [class_type]<>(19283) 
AND [class_type]<>(21843) 
AND [class_type]<>(16964) 
AND [class_type]<>(8257))
ALTER SERVER AUDIT [SQL_SRV_AUDIT] WITH (STATE = ON)
GO

Для просмотра событий, попадающих в аудит, можно воспользоваться командой

select  top 100 * from fn_get_audit_file ('C:\AUDIT_SQL\*', null, null)

Для теста выполним ряд операций на нашем MS SQL сервере:

• Создадим, изменим, удалим какой-нибудь объект на экземпляре СУБД, например, БД;
• Создадим БД, создадим в ней таблицу, наполним ее несколькими тестовыми строками, выполним команду SELECT из этой таблицы.

Теперь посмотрим, что записалось в журнале аудита с помощью команды:

select  * from fn_get_audit_file ('C:\AUDIT_SQL\*', null, null) 

Для тонкой настройки под конкретные задачи можно поэкспериментировать с набором DATABASE_OPERATION_GROUP и фильтром классов событий.

Чтение данных из файлов на диске, при необходимости анализа данных аудита ресурсоемко, поэтому я создал БД AUDIT_SQL с одной единственной таблицей и наполнил ее данными из текстовых файлов аудита. Создал ежедневное задание, которое собирает новые данные из текстовых файлов и дописывает их в БД AUDIT_SQL.

В результате мы получили ежедневно наполняемую реляционную БД, данные в которой хранятся нужный период времени (например, 0.5 и очищается она по заданию). Доступ к БД можно предоставить аналитикам.

В заключении хотел бы добавить, что необходимо внимательно изучить список групп событий аудита. Некоторые из них генерируют большое кол-во записей событий, которые требуют очистки средствами фильтра аудита.

Отмечу, что для включения в фильтр исключений по видам событий class_type и action_ID я использовал функции (листинг кода ниже) получения ID этих событий, т.к. в журнал аудита попадает алиас события, а в фильтре нужно указывать ID события.

---------------------------------------------------------
--Функция просмотра class_type
---------------------------------------------------------
create function dbo.GetInt_class_type ( @class_type varchar(2)) returns int
begin
declare @x int
SET @x = convert(int, convert(varbinary(1), upper(substring(@class_type, 1, 1))))
if LEN(@class_type)>=2
SET @x = convert(int, convert(varbinary(1), upper(substring(@class_type, 2, 1)))) * power(2,8) + @x
else
SET @x = convert(int, convert(varbinary(1), ' ')) * power(2,8) + @x
return @x
end
go
---------------------------------------------------------
-- пример использования функции
Select dbo.GetInt_class_type ('A') as Int_class_type


---------------------------------------------------------
--Функция просмотра action_ID
create function dbo.GetInt_action_id ( @action_id varchar(4)) returns int
begin
declare @x int
SET @x = convert(int, convert(varbinary(1), upper(substring(@action_id, 1, 1))))
if LEN(@action_id)>=2
SET @x = convert(int, convert(varbinary(1), upper(substring(@action_id, 2, 1)))) * power(2,8) + @x
else
SET @x = convert(int, convert(varbinary(1), ' ')) * power(2,8) + @x
if LEN(@action_id)>=3
SET @x = convert(int, convert(varbinary(1), upper(substring(@action_id, 3, 1)))) * power(2,16) + @x
else
SET @x = convert(int, convert(varbinary(1), ' ')) * power(2,16) + @x
if LEN(@action_id)>=4
SET @x = convert(int, convert(varbinary(1), upper(substring(@action_id, 4, 1)))) * power(2,24) + @x
else
SET @x = convert(int, convert(varbinary(1), ' ')) * power(2,24) + @x
return @x
end
go
--------------------------------------------------------------------
-- пример использования функции
select dbo.GetInt_action_id('in') as int_action_id