データベース監査の背後にある考え方は、データベーステーブルに誰がいつアクセスしたか、テーブルにどのような変更が加えられたかを把握することです。これは、エンタープライズレベルのアプリケーションの標準的な最小要件とみなされているだけでなく、銀行業務やサイバーセキュリティなどの多くの分野の法的要件でもあります。データベース監査証跡は、不正アクセス、問題のある設定変更など、アプリケーションのあらゆる種類の問題を調査するために不可欠です。
今日のブログでは、rentalテーブルを監査するために、MySQL Sakilaサンプルデータベース にログを追加します。データベースはDVDレンタルストアのビジネスプロセスを表すため、これはキーテーブルです。
監査証跡データを保存するテーブルの作成
理想的には、監査対象のテーブルごとに監査テーブルを用意するのが最善です。rentalテーブルの監査証跡テーブルを作成するDDLステートメントは次のとおりです：
create table rental_audit_log( id int NOT NULL AUTO_INCREMENT, rental_id int NOT NULL, old_values varchar(255), new_values varchar(255), done_by varchar(255) NOT NULL, done_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id) );
また、Navicatでは、DDLステートメントを記述せずに、テーブルデザイナーを使用して全てのフィールドと属性を指定できます。
監査ログトリガーの作成
rental_audit_logテーブルにレコードを挿入するためには、rentalテーブルで実行されるDMLステートメントの種類（INSERT、UPDATE、DELETE）ごとに1つずつ、3つのデータベーストリガーを作成する必要があります。
AFTER INSERTトリガー
rentalテーブルのINSERTステートメントは、rental_insert_audit_triggerによって阻止されます。挿入操作の後にこれを起動し、全ての新しいデータをJSON_OBJECTとして提供します。Navicatでは、これらの詳細は全てテーブルデザイナーのトリガータブで指定できます：
新しい行をrentalテーブルに追加すると、rental_audit_logにも新しいレコードが表示されます：
AFTER UPDATEトリガー
rentalテーブルに対するUPDATEステートメントは、次のrental_update_audit_triggerによってキャプチャされます：
これで、レンタルレコードが更新される度に、rental_update_audit_triggerが実行され、変更されたレコードの古い状態と新しい状態の両方をキャプチャするためのrental_audit_log行が作成されます。この場合、ユーザー robgがrental_dateを“2005-05-25 17:17:04”から“2005-05-31 19:47:04”に変更したことがわかります。
AFTER DELETEトリガー
レンタルテーブルのDELETEステートメントを追跡するためには、次のrental_delete_audit_triggerを作成します：
この場合、新しいレコード状態がないため、old_values列のみが設定されます。したがって、生成されたrental_audit_log行の空のnew_values列は次のようになります：
ここでは、ユーザー fsmithが2023-03-22 08:46:07にrentalテーブルからレコード 1114を削除したことがわかります。
トリガーを使用した監査証跡ログに関する最終的な考え方
今日のブログでは、rentalテーブルを監査するために、MySQL Sakilaサンプルデータベースにログを追加しました。ログを追加したテーブルには、最も一般的な監査フィールドがいくつか含まれていました。組織によっては、DML操作タイプなどの他のタイプが含まれる場合もありますが、変更されたフィールドのみが含まれる場合もあります。それは組織にとって最も効果的なものです。
