投稿日：2025/01/23

はじめに

　前回はデータを暗号化してセキュリティを高めることができる「Always Encrypeted」について記事にしました。
　【SQL Server】Always Encrypetedを使ってみよう①
　【SQL Server】Always Encrypetedを使ってみよう②
　しかし、実際の操作の内容を把握しなければ不正を見分けられない可能性もありますし、原因の特定も難しくなります。
　そのような場合に必要な情報となるのが、不正な操作を見付ける「監査ログ」です。
　例えばデータベースへのログイン情報や、特定のテーブルへのアクセス履歴などの記録が挙げられます。

　今回はServerの監査ログの機能である、SQL Server Auditをご紹介します。

SQL Server Auditの概要

　SQL Server Auditは、サーバーレベルとデータベースレベルの両方で設定が可能な機能です。
　サーバーレベルの監査では、ログイン試行、データベースのバックアップ、復元操作など、サーバー全体のアクティビティを監視します。
　一方、データベースレベルの監査では、特定のデータベース内でのINSERT、UPDATE、DELETE操作など、より詳細なアクションを追跡できます。

　監査ログは、ファイル、Windowsイベントログ、またはAzureストレージに保存でき、監査ポリシーに基づいて特定のアクションを記録します。これにより、誰が、いつ、どのデータにアクセスしたかを把握することが可能です。

SQL Server Auditの全体像

　SQL Server上で、「SERVER AUDIT」と「～ AUDIT SPECIFICATION」を作成して監査ログを取得することができます。
　SERVER AUDITは監査ログを格納先情報や、データサイズ、ファイル数などを定義したオブジェクトです。
　AUDIT SPECIFICATIONは、サーバーもしくはデータベース単位で何を監査するか定義するオブジェクトであり、監査した結果は紐づけたSERVER AUDITで定義した情報を基に記録します。

　それでは、実機検証をしてみましょう。

監査オブジェクトの準備

　まずは master にて、SERVER AUDITを作成します。

　今回は2つのSERVER AUDITを作成して情報を収集する想定で検証を進めます。もちろん1つのSERVER AUDITに複数の監査情報を纏めることも可能です。

USE master
-- ログインの監査用
CREATE SERVER AUDIT LoginAudit
TO FILE
(FILEPATH = 'C:\AuditLogs\Login\',
MAXSIZE = 10 MB,
MAX_ROLLOVER_FILES = 5,
RESERVE_DISK_SPACE = OFF);
ALTER SERVER AUDIT LoginAudit WITH (STATE = ON);
-- データ変更の監査用
CREATE SERVER AUDIT DataChangeAudit
TO FILE
(FILEPATH = 'C:\AuditLogs\DataChange\',
MAXSIZE = 10 MB,
MAX_ROLLOVER_FILES = 5,
RESERVE_DISK_SPACE = OFF)
WHERE object_name = 'Employees'; <-- ★
ALTER SERVER AUDIT DataChangeAudit WITH (STATE = ON);

　★の部分にもあるように、監査オブジェクトにはWHEREを使用して対象を絞り込むことも可能です。

　上記クエリで定義した「DataChangeAudit」では、Employeesのオブジェクトに絞って監査する設定としています。

　サーバー監査は master、データベース監査は対象となるユーザーデータベースで作成します。
　今回は「ログインの失敗と成功」と「TESTデータベース内のHRスキーマのオブジェクトへのデータ操作」を監査します。

USE master
-- サーバー監査
CREATE SERVER AUDIT SPECIFICATION LoginAuditSpec
FOR SERVER AUDIT LoginAudit
ADD (FAILED_LOGIN_GROUP), -- ログイン失敗の監査
ADD (SUCCESSFUL_LOGIN_GROUP)         -- ログイン成功の監査
WITH (STATE = ON);
USE test
-- データベース監査
CREATE DATABASE AUDIT SPECIFICATION DataChangeAuditSpec
FOR SERVER AUDIT DataChangeAudit
ADD (INSERT, UPDATE, DELETE ON SCHEMA::HR BY public)
WITH (STATE = ON);

　最後に作成したオブジェクトを確認します。

USE test
SELECT * FROM sys.server_audits;
SELECT * FROM sys.server_audit_specifications;
SELECT * FROM sys.database_audit_specifications;

公式Doc

監査状況の確認

　はじめにLoginAuditの確認をします。

　ログインの成功として現在ログインしているユーザーで再接続し、ログインの失敗として存在しないユーザーでログインを試みます。

　上記実行後、以下のSQLで監査情報を確認してみましょう。

SELECT * FROM sys.fn_get_audit_file('C:\AuditLogs\Login\*', DEFAULT, DEFAULT);

to-nakagawaはWindows認証でログインしたユーザーであり、testは存在しないユーザーとなります。


　最後にDataChangeAuditの確認を行います。検証に使用するテーブルは下記2つのテーブルです。
　監査の定義は「TESTデータベース内のHRスキーマのオブジェクトへのデータ操作」なので、dboスキーマへのデータ操作は記録されない想定となります。

SELECT * FROM [dbo].[Employees] ORDER BY 1;
SELECT * FROM [HR].[Employees] ORDER BY 1;

　それでは以下のDMLを使って各テーブルのデータを操作します。

### dboスキーマのEmployeesへのデータ操作
UPDATE [dbo].[Employees]
SET    [EmployeeName] = N'Employee B999'
WHERE  [EmployeeID] = 7;
DELETE [dbo].[Employees] WHERE  [EmployeeID] = 7;

INSERT INTO [dbo].[Employees] VALUES (7, N'Employee B999', 3);

### HRスキーマのEmployeesへのデータ操作
UPDATE [HR].[Employees]
SET    [LastName] = N'Nakagawa'
WHERE  [EmployeeID] = 1;

DELETE [HR].[Employees] WHERE  [EmployeeID] = 1;

INSERT INTO [HR].[Employees] VALUES (1, N'795-73-9838', N'Catherine',N'Nakagawa',31692.00);

　監査ログを確認すると、HRスキーマのみのデータ操作が記録されていますね。

SELECT * FROM sys.fn_get_audit_file('C:\AuditLogs\DataChange\*', DEFAULT, DEFAULT);

まとめ

　今回はサーバー上に監査ログを格納する設定としておりますが、Azure上のSQL ServerであればAzure Blobに格納することも可能です。

　SQL Server Auditの設定自体は容易ですが、システムに求められる監査ログの保管期間と料金などを比較しながら設定する必要がありそうです。