Кибербезопасность

Аудит 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
Группа событийОписаниеВключено в MS SQL +/-
APPLICATION_ROLE_CHANGE_PASSWORD_GROUPЭто событие появляется при изменении пароля для роли приложения. Эквивалентно Audit App Role Change Password Event Class.+
AUDIT_CHANGE_GROUPЭто событие возникает при создании, изменении или удалении любого аудита. Это событие возникает при создании, изменении или удалении спецификации любого аудита. Аудит любых изменений в аудите производится в этом аудите. Эквивалентно Audit Change Audit Event Class.+
BACKUP_RESTORE_GROUPЭто событие вызывается командой резервного копирования или восстановления.Эквивалент класса событий Audit Backup и Restore.+
BROKER_LOGIN_GROUPЭто событие вызывается для составления отчета о сообщениях аудита, связанных с механизмом обеспечения безопасности транспорта компонента Service Broker.Эквивалентно Audit Broker Login Event Class.
DATABASE_CHANGE_GROUPЭто событие вызывается при создании, изменении или удалении базы данных. Это событие возникает при создании, изменении или удалении любой базы данных.Эквивалентно Audit Database Management Event Class.+
DATABASE_LOGOUT_GROUPЭто событие возникает при выходе пользователя автономной базы данных из базы данных.+
DATABASE_MIRRORING_LOGIN_GROUPЭто событие вызывается для составления отчета о сообщениях аудита, связанных с механизмом обеспечения безопасности транспорта зеркального отображения базы данных. Эквивалентно Audit Database Mirroring Login Event Class.
DATABASE_OBJECT_ACCESS_GROUPЭто событие вызывается каждый раз при обращении к типам сообщений, сборкам и контрактам. Это событие возникает при любом доступе к любой базе данных.Примечание. Это может привести к появлению большого количества записей аудита.
DATABASE_OBJECT_CHANGE_GROUPЭто событие вызывается в тот момент, когда для объекта базы данных (например, для схемы) выполняется инструкция CREATE, ALTER или DROP. Это событие возникает при создании, изменении или удалении любого объекта базы данных. Примечание. Это приводит к очень большому количеству записей аудита.+
DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUPЭто событие возникает при изменении владельца объекта в области базы данных.Это событие возникает при любом изменении владельца объекта в любой базе данных на сервере. Эквивалентно Audit Database Object Take Ownership Event Class.+
DATABASE_OBJECT_PERMISSION_CHANGE_GROUPЭто событие возникает в тот момент, когда для объекта базы данных (например, сборки или схемы) выполняется инструкция GRANT, REVOKE или DENY. Это событие возникает при любом изменении разрешения на объект для любой базы данных на сервере. Эквивалентно Audit Database Object GDR Event Class.+
DATABASE_OPERATION_GROUPЭто событие вызывается при выполнении различных операций в базе данных, например при создании контрольной точки или уведомлении о запросе подписки.Это событие возникает при любой операции с базой данных в любой базе данных.Эквивалентно Audit Database Operation Event Class.+
DATABASE_OWNERSHIP_CHANGE_GROUPЭто событие вызывается при смене владельца базы данных инструкцией ALTER AUTHORIZATION в момент проверки разрешений на эту операцию. Это событие возникает при любом изменении владельца базы данных в любой базе данных на сервере. Эквивалентно Audit Change Database Owner Event Class.+
DATABASE_PERMISSION_CHANGE_GROUPЭто событие возникает в случае, когда инструкции GRANT, REVOKE или DENY выдаются для разрешения на выполнение инструкции любым участником SQL Server(применяется только к событиям базы данных, например предоставлению разрешений на базу данных).+
DATABASE_PRINCIPAL_CHANGE_GROUPЭто событие создается при создании, изменении или удалении из базы данных участников, таких как пользователи. Эквивалентно Audit Database Principal Management Event Class. (Также эквивалентно классу событий подсистемы аудита «Add DB Principal», вызываемому устаревшими хранимыми процедурами sp_grantdbaccess, sp_revokedbaccess, sp_addPrincipal и sp_dropPrincipal).+
DATABASE_PRINCIPAL_IMPERSONATION_GROUPЭто событие возникает при использовании в области базы данных операции олицетворения, например EXECUTE AS <субъект> или SETPRINCIPAL. Это событие возникает при использовании олицетворения в любой базе данных. Эквивалентно Audit Database Principal Impersonation Event Class.+
DATABASE_ROLE_MEMBER_CHANGE_GROUPЭто событие вызывается каждый раз, когда имя входа добавляется в роль базы данных или удаляется из нее. Этот класс событий вызывается хранимыми процедурами sp_addrolemember, sp_changegroup и sp_droprolemember. Это событие появляется при изменении члена любой роли базы данных в любой базе данных.Эквивалентно Audit Add Member to DB Role, класс событий.+
DBCC_GROUPЭто событие появляется при вызове участником любой команды DBCC.Эквивалентно Audit DBCC Event Class.+
FAILED_DATABASE_AUTHENTICATION_GROUPУказывает, что попытка участника войти в автономную базу данных завершилась ошибкой. События этого класса вызываются новыми соединениями или соединениями, которые многократно используются в пуле соединений.Эквивалентно Audit Login Failed Event Class.
FULLTEXT_GROUPУказывает, что произошло полнотекстовое событие. Эквивалентно Audit Fulltext Event Class.
LOGIN_CHANGE_PASSWORD_GROUPЭто событие появляется при изменении пароля входа с помощью инструкции ALTER LOGIN или хранимой процедуры sp_password. Эквивалентно Audit Login Change Password Event Class.+
LOGOUT_GROUPУказывает, что участник отключился от SQL Server. События этого класса вызываются новыми соединениями или соединениями, которые многократно используются в пуле соединений. Эквивалентно Audit Logout Event Class.+
SCHEMA_OBJECT_ACCESS_GROUPЭто событие возникает при применении разрешения на объект в схеме.Эквивалентно Audit Schema Object Access Event Class.
SCHEMA_OBJECT_CHANGE_GROUPЭто событие вызывается в момент выполнения для схемы операции CREATE, ALTER или DROP. Эквивалентно Audit Schema Object Management Event Class.+
SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUPЭто событие возникает при проверке разрешений на смену владельца объекта схемы (таблицы, процедуры, функции и т. д.). Возникает, когда объекту назначается владелец при помощи инструкции ALTER AUTHORIZATION. Это событие возникает при любом изменении владельца схемы в любой базе данных на сервере.Эквивалентно Audit Schema Object Take Ownership Event Class.+
SCHEMA_OBJECT_PERMISSION_CHANGE_GROUPЭто событие возникает при выполнении инструкции GRANT, REVOKE или DENY для объекта схемы. Эквивалентно Audit Schema Object GDR Event Class.+
SERVER_OBJECT_CHANGE_GROUPЭто событие возникает при выполнении операций CREATE, ALTER или DROP с объектами сервера. Эквивалентно Audit Server Object Management Event Class.+
SERVER_OBJECT_OWNERSHIP_CHANGE_GROUPЭто событие возникает при изменении владельца объектов в области сервера.Эквивалентно Audit Server Object Take Ownership Event Class.+
SERVER_OBJECT_PERMISSION_CHANGE_GROUPЭто событие возникает в случае, когда инструкции GRANT, REVOKE или DENY выдаются для разрешений на объекты сервера любым участником SQL Server.Эквивалентно Audit Server Object GDR Event Class.+
SERVER_OPERATION_GROUPЭто событие возникает при использовании таких операций аудита безопасности, как изменение параметров, ресурсов, внешнего доступа или авторизации. Эквивалентно Audit Server Operation Event Class.
SERVER_PERMISSION_CHANGE_GROUPЭто событие возникает в случае, когда инструкции GRANT, REVOKE или DENY выдаются для разрешений в области действия сервера. Эквивалентно Audit Server Scope GDR Event Class.+
SERVER_PRINCIPAL_CHANGE_GROUPЭто событие возникает при создании, изменении и удалении участников на уровне сервера. Эквивалентно Audit Server Principal Management Event Class.+
SERVER_PRINCIPAL_IMPERSONATION_GROUPЭто событие возникает при использовании в области действия сервера олицетворения, например команды EXECUTE AS <имя_для_входа>. Эквивалентно Audit Server Principal Impersonation Event Class.+
SERVER_ROLE_MEMBER_CHANGE_GROUPЭто событие появляется при добавлении или удалении имени входа из предопределенной роли сервера. Это событие вызывается хранимыми процедурами sp_addsrvrolemember и sp_dropsrvrolemember. Эквивалентно Класс событий Audit Add Login to Server Role.+
SERVER_STATE_CHANGE_GROUPЭто событие возникает при изменении состояния службы SQL Server . Эквивалентно Audit Server Starts and Stops Event Class.+
SUCCESSFUL_DATABASE_AUTHENTICATION_GROUPУказывает, что участник успешно выполнил вход в автономную базу данных.+
SUCCESSFUL_LOGIN_GROUPУказывает, что участник успешно выполнил вход на SQL Server. События этого класса вызываются новыми соединениями или соединениями, которые многократно используются в пуле соединений. Эквивалентно Audit Login Event Class.+
TRACE_CHANGE_GROUPЭто событие вызывается для всех инструкций, выполняющих проверку на разрешение ALTER TRACE. Эквивалентно Audit Server Alter Trace Event Class.+
TRANSACTION_GROUPЭто событие вызывается для операций BEGIN TRANSACTION, ROLLBACK TRANSACTION и COMMIT TRANSACTION как при явных вызовах этих инструкций, так и при неявных операциях с транзакциями. Это событие также вызывается для операций UNDO при использовании отдельных инструкций, вызванных откатом транзакции.
USER_CHANGE_PASSWORD_GROUPЭто событие возникает при изменении пароля пользователя автономной базы данных с помощью инструкции ALTER USER.+
USER_DEFINED_AUDIT_GROUPЭта группа наблюдает за событиями, вызываемыми с помощью процедуры sp_audit_write (Transact-SQL). Как правило, триггеры или хранимые процедуры включают вызовы процедуры sp_audit_write для включения аудита важных событий.
Группы событий аудита MS SQL
Советуем почитать