АУДИТ MS SQL СЕРВЕРА
В целях обеспечения контроля доступа к информации, а также действий и операций, выполняемых пользователями на экземпляре СУБД MS SQL корпорация Microsoft предлагает штатный инструмент для аудита MS SQL, который предполагает детальное журналирование следующих событий на экземпляре MS SQL:
- Детализация и журналирование событий над объектами экземпляра СУБД 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