MSSQL ServerでのTRUNCATE・DELETE操作の監査：実行者と実行時刻を特定する方法

本記事では、MSSQL Server上でテーブルからデータが切り捨て（TRUNCATE）られたり削除（DELETE）されたりした際に、その操作を実行したユーザーや実行時刻を特定するための手順を解説します。

例えば、以下のような疑問に答えることができます。

• テーブルはいつ切り捨てられ、いつデータが削除されたのか？
• 誰がテーブルを切り捨て、データを削除したのか？

情報収集が必要な理由

この調査を行う目的は、データが意図的に削除されたのか、それとも誤操作によるものなのかを判別し、該当者を特定したうえで再発防止策を講じることです。実際、こうした情報を求めるお客様からの問い合わせは少なからず寄せられています。

また、データ消去操作の正確な時刻がわかれば、ログバックアップの復元時にSTOP AT句を使用することで、該当时点の直前までデータを容易に復旧させることができます。

問題の概要

ここでは、次のようなシナリオを想定します。

2020年1月5日の午後5時〜7時の間に、「truncate test」DB内の「dump_truncate」テーブルが切り捨てられ、「dump_delete」テーブルからデータが削除されました。解明すべき主な疑問点は以下の通りです。

• 何が起きたのかを正確に把握したい
• 誰が「dump_delete」テーブルからデータを削除したのか？
• 「dump_delete」テーブルから何行削除されたのか？
• 誰が「dump_truncate」テーブルを切り捨てたのか？
• これらの操作はいつ実行されたのか？

なお、サーバーの現在のバックアップスケジュールは以下の通りとします。

• 毎週日曜日にフルバックアップを実施
• 毎日午後1時に差分バックアップを実施
• 15分ごとにログバックアップを作成

前提条件

• データベースが完全復旧モデル（FULL RECOVERY）に設定されていること
• フルバックアップ、差分バックアップ、ログバックアップがすべて利用可能であること

全体のアプローチ

1. どのログバックアップにDELETEおよびTRUNCATE操作が含まれているかを特定する
2. ログバックアップをもとに、TRUNCATEおよびDELETE操作の詳細を特定する

ステップ1：DELETE・TRUNCATE操作を含むログバックアップの特定

注意： 本手順は、本番DBに影響を与えないよう、DBのコピーを作成したうえで実施しています。

1. 1月2日（日曜日）のフルバックアップをSTANDBYモードで復元する
2. 1月5日の差分バックアップをSTANDBYモードで復元する
3. ログバックアップをSTANDBYモードで順次復元し、各復元後にテーブルの行数を確認して、どのログバックアップにTRUNCATE・DELETE操作のログが含まれているかを絞り込む

今回のケースでは、午後6時のログバックアップを復元した時点で「dump_truncate」テーブルが空になり、「dump_delete」テーブルのデータも消失していました。つまり、午後5時45分〜午後6時の間に「dump_truncate」テーブルが切り捨てられ、同時に「dump_delete」テーブルのデータも消去されたことがわかります。

ステップ2：ログバックアップからTRUNCATE・DELETE操作の詳細を特定

2-1. トランザクションIDの収集

まず、午後5時45分〜午後6時の間に発生したすべてのTRUNCATE・DELETE操作のトランザクションIDを収集します。fn_dblog関数に対象時間帯を指定してクエリを実行すると、午後5時50分にDELETEとTRUNCATEの2つの操作が実行され、いずれもログイン「RP_DEV」によって実行されたことが判明しました。

SELECT [Current LSN], [Operation], [Transaction Name],
       [Transaction ID], [Begin Time], [Login Name]
FROM fn_dblog(NULL, NULL)
WHERE [Begin Time] BETWEEN '2020/01/05 17:45:00' AND '2020/01/05 18:00:00';

2-2. DELETE操作の詳細を特定する

I) DELETE操作のオブジェクトIDとパーティションIDを特定する

トランザクションIDをキーにログレコードを照会すると、以下の情報を読み取ることができます。

• Description列・Transaction Name列： DELETE操作が実行されたことを示している
• Begin Time： DELETE操作は2022/01/05 17:50:22:493に開始された
• Login_Name： 操作を実行したのはRP_DEVである
• Lock Information： 「HoBt」というプレフィックスで始まるロック行が1行の削除に対応し、合計7行が削除されている
• Object ID： データが削除されたテーブルのオブジェクトID
• Partition ID： データが削除されたオブジェクトのパーティションID

II) オブジェクトIDとパーティションIDから対象テーブルを特定する

システムカタログビューに対象のIDを照会することで、テーブル名を特定できます。

SELECT o.name AS table_name, p.partition_id
FROM sys.objects o
JOIN sys.partitions p ON o.object_id = p.object_id
WHERE o.object_id = <特定したObject ID>;

以上の結果から、「dump_delete」テーブルのデータがRP_DEVユーザーによって午後5時50分に、トランザクションID「0000:00016a96」として削除され、合計7行が削除されたことが確定します。

2-3. TRUNCATE操作の詳細を特定する

I) TRUNCATE操作のオブジェクトIDとパーティションIDを特定する

同じくトランザクションIDをキーにログを照会します。TRUNCATE操作の出力は、DELETE操作とはいくつか異なる点がありますので注意が必要です。

• Partition ID列： この列には正しいパーティションIDが表示されません。正しい値はDescription列で確認できます。今回のパーティションIDは72057594043564032と72057594043629568です。
• Lock Description： SCH_M_OBJECTの行には常に正しいObject IDが表示されます。今回のオブジェクトIDは885578193です。

II) オブジェクトIDとパーティションIDから対象テーブルを特定する

先ほどと同様にシステムカタログビューへ照会します。

これにより、「dump_truncate」テーブルがRP_DEVユーザーによって午後5時50分に、トランザクションID「0000:00016a95」として切り捨てられたことが確定します。

まとめ

監査機能が有効化されていない環境でも、誰がTRUNCATE・DELETE操作を実行したのかを把握できれば、同様のインシデントの再発防止につながります。

• DELETE操作の場合、ビジネス側は削除された行数を正確に把握できる
• 復元ポイントとなる正確な時刻がわかれば、データ復旧が格段に容易になる
• ApexSQL LogやApexSQL Recoverといったサードパーティ製ツールを活用すれば、より柔軟なデータ復旧も可能になる

次世代データプラットフォームへの移行をご検討の際は、ぜひ弊社の専門家にご相談ください。コメントやご質問がある場合は、フィードバックタブからお気軽にお寄せください。