SQL Server Audit を設定する: ログイン、権限変更、テーブルアクセス

コマンド

USE master;
CREATE SERVER AUDIT [Audit_DBA]
TO FILE (FILEPATH = N'D:\Audit\', MAXSIZE = 256 MB, MAX_ROLLOVER_FILES = 20, RESERVE_DISK_SPACE = OFF)
WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE);
ALTER SERVER AUDIT [Audit_DBA] WITH (STATE = ON);

-- Server-wide (covers all databases):
CREATE SERVER AUDIT SPECIFICATION [AuditSpec_Server]
FOR SERVER AUDIT [Audit_DBA]
    ADD (FAILED_LOGIN_GROUP),
    ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),
    ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),
    ADD (SERVER_PRINCIPAL_CHANGE_GROUP),
    ADD (DATABASE_PRINCIPAL_CHANGE_GROUP),
    ADD (SERVER_PERMISSION_CHANGE_GROUP),
    ADD (DATABASE_PERMISSION_CHANGE_GROUP),
    ADD (AUDIT_CHANGE_GROUP)
WITH (STATE = ON);

-- Database: access to a sensitive table:
USE [Musteri];
CREATE DATABASE AUDIT SPECIFICATION [AuditSpec_Musteri]
FOR SERVER AUDIT [Audit_DBA]
    ADD (SELECT, INSERT, UPDATE, DELETE ON OBJECT::[dbo].[Siparis] BY [public])
WITH (STATE = ON);

-- Read:
SELECT event_time, action_id, succeeded, server_principal_name, database_name,
       schema_name, object_name, LEFT(statement, 200) AS stmt, client_ip, application_name
FROM   sys.fn_get_audit_file(N'D:\Audit\Audit_DBA_*.sqlaudit', DEFAULT, DEFAULT)
WHERE  event_time >= DATEADD(HOUR, -24, SYSUTCDATETIME())
ORDER BY event_time DESC;

概要

ファイルに書き込むサーバー監査を作成する。サーバー全体でログイン失敗、ロールメンバーシップ、プリンシパルと権限の変更を記録し、データベースレベルで選択したテーブルへの SELECT/DML アクセスを記録し、T-SQL でレコードを読み取る。

使用する場面

監査 / コンプライアンス要件 (GDPR、PCI、SOX)、「誰がこのテーブルを読んだ / 変更したか」という問い、または権限昇格が疑われる場合に。

注意

高負荷テーブルでの SELECT 監査はレコードを非常に速く生成し、性能コストがかかるため、オブジェクトとプリンシパルで絞り込む。ON_FAILURE = SHUTDOWN は監査を書き込めない場合にサーバーを停止させるため、義務付けられている場合にのみ使用する。監査フォルダーは SQL Server サービスアカウントが書き込み可能で、DBA 以外が削除できないようにする必要がある。event_time は UTC。データベース監査仕様はデータベースとともにリストアされるが、サーバー監査の GUID が一致しない場合は孤立したままとなる。