SQL Server で監査証跡 (Audit Trail)

概要

• データベースのマスタテーブルの値について、いつ・だれが・どこで変更したのかを自動で記録する方法のメモ
  • 証跡用のテーブルを用意
  • 対象テーブルのトリガーを用意

対象

• SQL Server (対象バージョンは未調査、2022 で動作確認)

---

証跡テーブル

• 変更内容を記録するためのテーブルを下記のように設計

name	type	default	descr.
Id	bigint		PK
SchemaName	varchar(128)		対象のスキーマ名
TableName	varchar(128)		対象のテーブル名
RecordId	int		対象のレコードＩＤ　※設計によって型を変更すること！
Action	char(3)		処理区分 (UPD / DEL / INS)
Content	nvarchar(max)		変更内容 (JSON)
RecordedAt	datetime	GETDATE()	記録日時
RecordedBy	nvarchar(128)	APP_NAME()	記録アプリ
RecordedOn	nvarchar(128)	HOST_NAME()	記録端末

log.AuditTrail テーブル作成クエリ

-- CREATE SCHEMA log

CREATE TABLE [log].[AuditTrail]
(
  [Id]          bigint identity NOT NULL
, [SchemaName]  varchar(128)    NOT NULL
, [TableName]   varchar(128)    NOT NULL
, [RecordId]    int             NOT NULL
, [Action]      char(3)         NOT NULL
, [Content]     nvarchar(max)   NOT NULL
, [RecordedAt]  datetime        NOT NULL  CONSTRAINT [DF_AuditTrail_RecordedAt] default GETDATE()
, [RecordedBy]  nvarchar(128)   NOT NULL  CONSTRAINT [DF_AuditTrail_RecordedBy] default APP_NAME()
, [RecordedOn]  nvarchar(128)   NOT NULL  CONSTRAINT [DF_AuditTrail_RecordedOn] default HOST_NAME()
, CONSTRAINT [PK_AuditTrail] PRIMARY KEY ([Id])
)

CREATE INDEX [IX_AuditTrail_Identifier] ON [log].[AuditTrail] ([SchemaName], [TableName], [RecordId])
CREATE INDEX [IX_AuditTrail_RecordedAt] ON [log].[AuditTrail] ([RecordedAt])

• RecordedAt,RecordedBy,RecordedOnは、デフォルト制約によって自動入力
  • GETDATE
  • APP_NAME
  • HOST_NAME

---

対象テーブル

• 任意のテーブル

mst.Sample テーブル作成クエリ

-- CREATE SCHEMA mst

CREATE TABLE [mst].[Sample]
(
  [Id]          int identity    NOT NULL  -- Trail.RecordId と同じ型
, [Name]        nvarchar(128)   NOT NULL
, CONSTRAINT [PK_Sample] PRIMARY KEY ([Id])
, CONSTRAINT [AK_Sample_Name] UNIQUE ([Name])
)

mst.Sample トリガー作成クエリ

CREATE TRIGGER [TG_OnChanged] ON [mst].[Sample] AFTER UPDATE, INSERT, DELETE AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @SchemaName varchar(128) = 'mst';
  DECLARE @TableName  varchar(128) = 'Sample';

  IF EXISTS (SELECT 1 FROM inserted) AND EXISTS (SELECT 1 FROM deleted)
  BEGIN
    -- UPDATE
    INSERT INTO [log].[AuditTrail] ([SchemaName], [TableName], [RecordId], [Action], [Content])
    SELECT @SchemaName, @TableName, i.[Id], 'UPD', (SELECT i.* FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) FROM inserted i;
  END
  ELSE IF EXISTS (SELECT 1 FROM inserted)
  BEGIN
    -- INSERT
    INSERT INTO [log].[AuditTrail] ([SchemaName], [TableName], [RecordId], [Action], [Content])
    SELECT @SchemaName, @TableName, i.[Id], 'INS', (SELECT i.* FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) FROM inserted i;
  END
  ELSE IF EXISTS (SELECT 1 FROM deleted)
  BEGIN
    -- DELETE
    INSERT INTO [log].[AuditTrail] ([SchemaName], [TableName], [RecordId], [Action], [Content])
    SELECT @SchemaName, @TableName, d.[Id], 'DEL', (SELECT d.* FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) FROM deleted d;
  END
END

• 変更時のトリガーで証跡テーブルにレコードを登録する
  • 変数 (@SchemaName,@TableName) の値を指定することで、他のテーブルにも適用可能

レコード内容の例

• レコード内容はFOR JSON句を使ってJSON文字列に変換できる

{"Id":1,"Name":"test"}