- 設計
監査ログの設計項目一覧|操作ログのテーブル設計例とDDL
監査ログとは、「誰が・いつ・どこから・何に・何をして・どうなったか」をあとから説明できるように残す、追記専用の記録です。 障害調査のためのアプリログとは目的が違い、消えたり書き換わったりしないことと、利用者の操作を1件ずつたどれることが求められます。
この記事では、証跡を残すためのテーブルに話を絞り、項目一覧、PostgreSQL と MySQL の CREATE TABLE、変更履歴の取り方3方式の比較、追記専用にする権限、保持期間の決め方を順に示します。SQL はすべて実際に流し、出力をそのまま載せています。題材は架空の「社内の備品予約システム」です。
確認環境:Ubuntu 24.04 / PostgreSQL 16.15 / MySQL 8.0.46(確認日 2026-10-05)。出力中のスクリプト名は audit.sql に置き換えています。
この記事の結論
- 残す項目は7つの問いで決める:誰が・いつ・どこから・何に・何をした・変更前後・結果。
- テーブルは1枚の追記専用テーブルから始める。変更前後は JSON 型(PostgreSQL は jsonb)に入れると、対象テーブルが増えても列を足さずに済む。
- 変更履歴の取り方は3方式。操作の意味や拒否された操作まで残すなら「アプリで書く」、SQL の直接実行も漏らさないなら「トリガーで書く」、ある時点の姿を再現したいなら「履歴テーブル」。
- アプリ用のユーザーには INSERT と SELECT だけを与える。UPDATE・DELETE・TRUNCATE が権限エラーで止まることを確かめておく。
- 保持期間は月単位のパーティションで管理すると、期限が来た月をまとめて外せる。
監査ログの項目一覧の早見表
| 問い | 列名 | 型(PostgreSQL / MySQL) | 必須 | 記入例 | 補足 |
|---|---|---|---|---|---|
| 連番 | audit_id | bigint IDENTITY / BIGINT AUTO_INCREMENT | ○ | 1024 | 並び順と件数確認に使う |
| いつ | occurred_at | timestamptz / DATETIME(6) | ○ | 2026-10-05 10:30:12.345678+09 | DB サーバーの時刻で入れる |
| 誰が | actor_id | varchar(64) | ○ | u1024 | 表示名ではなく変わらない ID |
| 誰が(種別) | actor_type | varchar(16) | ○ | user / admin / system / batch | 人か、バッチかを分ける |
| どこから | source_ip | inet / VARCHAR(45) | 192.0.2.15 | IPv6 も入る長さにする | |
| どこから | user_agent | varchar(255) | Mozilla/5.0 … | 端末の手がかり | |
| どの要求で | request_id | varchar(64) | req-7f3a | アプリログと突き合わせる鍵 | |
| 何をした | action | varchar(32) | ○ | equipment.update | 「対象.動詞」で名前をそろえる |
| 何に | target_type | varchar(64) | ○ | equipment | テーブル名や機能名 |
| 何に | target_id | varchar(64) | 1 | 主キーを文字列で入れる | |
| 変更前 | before_data | jsonb / JSON | {"status":"available"} | 登録のときは NULL | |
| 変更後 | after_data | jsonb / JSON | {"status":"repair"} | 削除のときは NULL | |
| 結果 | result | varchar(16) | ○ | success / denied / error | 拒否された操作も残す |
| 結果の理由 | reason | varchar(255) | 権限なし | 拒否・エラーのときに書く |
表計算ソフトに貼れるタブ区切り版です。
問い 列名 PostgreSQLの型 MySQLの型 必須 記入例
連番 audit_id bigint IDENTITY BIGINT UNSIGNED AUTO_INCREMENT ○ 1024
いつ occurred_at timestamptz DATETIME(6) ○ 2026-10-05 10:30:12.345678+09
誰が actor_id varchar(64) VARCHAR(64) ○ u1024
誰が(種別) actor_type varchar(16) VARCHAR(16) ○ user
どこから source_ip inet VARCHAR(45) 192.0.2.15
どこから user_agent varchar(255) VARCHAR(255) Mozilla/5.0
どの要求で request_id varchar(64) VARCHAR(64) req-7f3a
何をした action varchar(32) VARCHAR(32) ○ equipment.update
何に target_type varchar(64) VARCHAR(64) ○ equipment
何に target_id varchar(64) VARCHAR(64) 1
変更前 before_data jsonb JSON {"status":"available"}
変更後 after_data jsonb JSON {"status":"repair"}
結果 result varchar(16) VARCHAR(16) ○ success
結果の理由 reason varchar(255) VARCHAR(255) 権限なし
本記事の内容は、ご自由にお使いください。
ご利用の際は、出典として本ページへのリンクを記載いただけますようお願いします。
(記載例)出典:株式会社RJC「監査ログの設計項目一覧|操作ログのテーブル設計例とDDL」
監査ログ・アプリログ・アクセスログの違い
3つは「誰が読むか」と「消えてよいか」で分かれます。
| 観点 | 監査ログ(操作ログ) | アプリログ | アクセスログ |
|---|---|---|---|
| 目的 | 操作の証跡を残し、あとから説明する | 不具合の原因を調べる | 通信の量と応答を把握する |
| 主な読み手 | 管理者、監査の担当者、問い合わせ対応 | 開発者、運用担当 | 運用担当 |
| 1件の単位 | 利用者の操作1回 | プログラムの出来事1つ | HTTP リクエスト1回 |
| 中身 | 誰が・何に・何をした・変更前後・結果 | レベル、メッセージ、スタックトレース | URL、ステータス、応答時間 |
| 置き場所 | データベースのテーブル | ファイルやログ基盤 | Web サーバーやロードバランサー |
| 書き換え・削除 | 不可(追記専用) | ローテーションで消える | ローテーションで消える |
| 欠けたときの扱い | 操作そのものを成立させない設計もある | 一部欠けても調査は続けられる | 一部欠けても傾向は分かる |
どこに書くかを決める基準は次のとおりです。
| 条件 | 書く先 |
|---|---|
| 利用者や管理者に「この操作は誰がしたか」を答える必要がある | 監査ログ |
| データの値が変わった、権限が変わった、ログインした | 監査ログ |
| 例外が起きた、外部 API の応答が遅い | アプリログ |
| どの URL に何回アクセスがあったかを知りたい | アクセスログ |
| 参照しただけの操作 | 個人情報や機密の閲覧なら監査ログ、それ以外はアクセスログ |
3つを request_id でつなげておくと、監査ログの1行から同じ要求のアプリログへたどれます。
記録する操作を決める
項目より先に「どの操作を残すか」を一覧にします。action の名前は「対象.動詞」でそろえると、あとで絞り込みやすくなります。
| 分類 | action の例 | 変更前後 | 補足 |
|---|---|---|---|
| 認証 | login / login.denied / logout | なし | 拒否されたログインも残す |
| データの登録・更新・削除 | equipment.create / equipment.update / equipment.delete | あり | 変更前後を JSON で残す |
| 権限・設定の変更 | role.grant / role.revoke / setting.update | あり | 管理者の操作は優先して残す |
| 一括操作・出力 | reservation.export / equipment.import | 件数だけ | 件数と条件を after_data に入れる |
| 機密の閲覧 | member.view | なし | 閲覧した対象の ID を残す |
| バッチ・システムの処理 | reservation.expire | あり | actor_type を batch にする |
パスワード、トークン、API キーのような秘密の値は before_data / after_data に入れません。値そのものではなく「変更した」という事実だけを残します。
操作ログテーブルの CREATE TABLE(PostgreSQL 16)
月単位で外せるよう、発生日時でパーティションに分けています。パーティションキーを主キーに含める必要があるため、主キーは (audit_id, occurred_at) です。
CREATE TABLE audit_log (
audit_id bigint GENERATED ALWAYS AS IDENTITY,
occurred_at timestamptz NOT NULL DEFAULT now(),
actor_id varchar(64) NOT NULL,
actor_type varchar(16) NOT NULL DEFAULT 'user'
CHECK (actor_type IN ('user','admin','system','batch')),
source_ip inet,
user_agent varchar(255),
request_id varchar(64),
action varchar(32) NOT NULL,
target_type varchar(64) NOT NULL,
target_id varchar(64),
before_data jsonb,
after_data jsonb,
result varchar(16) NOT NULL DEFAULT 'success'
CHECK (result IN ('success','denied','error')),
reason varchar(255),
PRIMARY KEY (audit_id, occurred_at)
) PARTITION BY RANGE (occurred_at);
CREATE TABLE audit_log_2026_09 PARTITION OF audit_log
FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');
CREATE TABLE audit_log_2026_10 PARTITION OF audit_log
FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');
CREATE INDEX audit_log_actor_idx ON audit_log (actor_id, occurred_at);
CREATE INDEX audit_log_target_idx ON audit_log (target_type, target_id, occurred_at);
索引は「この人は何をしたか」(actor_id)と「このデータは誰が触ったか」(target_type, target_id)の2つの調べ方に合わせています。
以降の例で使う業務テーブルです。
CREATE TABLE equipment (
equipment_id integer PRIMARY KEY,
name varchar(100) NOT NULL,
status varchar(16) NOT NULL,
location varchar(50) NOT NULL,
updated_at timestamptz NOT NULL DEFAULT now()
);
INSERT INTO equipment VALUES
(1,'プロジェクター A','available','3F 倉庫', '2026-10-01 09:00+09'),
(2,'ノートPC 12', 'available','5F 棚 2', '2026-10-01 09:00+09');
操作ログテーブルの CREATE TABLE(MySQL 8.0)
MySQL では IP アドレスを VARCHAR(45)、変更前後を JSON 型で持ちます。パーティションは RANGE COLUMNS で発生日時を区切ります。
CREATE TABLE audit_log (
audit_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
occurred_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
actor_id VARCHAR(64) NOT NULL,
actor_type VARCHAR(16) NOT NULL DEFAULT 'user',
source_ip VARCHAR(45),
user_agent VARCHAR(255),
request_id VARCHAR(64),
action VARCHAR(32) NOT NULL,
target_type VARCHAR(64) NOT NULL,
target_id VARCHAR(64),
before_data JSON,
after_data JSON,
result VARCHAR(16) NOT NULL DEFAULT 'success',
reason VARCHAR(255),
PRIMARY KEY (audit_id, occurred_at),
KEY audit_log_actor_idx (actor_id, occurred_at),
KEY audit_log_target_idx (target_type, target_id, occurred_at),
CONSTRAINT audit_log_actor_type_chk
CHECK (actor_type IN ('user','admin','system','batch')),
CONSTRAINT audit_log_result_chk
CHECK (result IN ('success','denied','error'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE COLUMNS (occurred_at) (
PARTITION p2026_09 VALUES LESS THAN ('2026-10-01'),
PARTITION p2026_10 VALUES LESS THAN ('2026-11-01')
);
| 違い | PostgreSQL 16 | MySQL 8.0 |
|---|---|---|
| 日時 | timestamptz(タイムゾーンつき) | DATETIME(6)(タイムゾーンなし。UTC で入れると決める) |
| IP アドレス | inet 型 | VARCHAR(45) |
| 変更前後 | jsonb | JSON |
| パーティション | 月ごとに子テーブルを CREATE | 定義の中に PARTITION を並べる |
| 値の取り出し | before_data->>'status' | before_data->>'$.status' |
変更履歴の取り方3方式を比べる
「誰が・いつ・何を変えたか」を残す方法は、大きく3つあります。同じ備品テーブルで順に試します。
方式1:アプリで書く
業務データの更新と監査ログの INSERT を、同じトランザクションの中でアプリから実行します。下の SQL は、更新前の行と更新後の行を1文で取り出して監査ログに入れる形です。
BEGIN;
WITH old AS (
SELECT to_jsonb(e) AS j FROM equipment e WHERE equipment_id = 1 FOR UPDATE
), upd AS (
UPDATE equipment SET status = 'repair', updated_at = now()
WHERE equipment_id = 1
RETURNING to_jsonb(equipment.*) AS j
)
INSERT INTO audit_log
(actor_id, source_ip, request_id, action, target_type, target_id,
before_data, after_data)
SELECT 'u1024', '192.0.2.15', 'req-7f3a', 'equipment.update', 'equipment', '1',
old.j, upd.j
FROM old, upd;
COMMIT;
SELECT audit_id, actor_id, source_ip, action, target_id,
before_data->>'status' AS before_status,
after_data->>'status' AS after_status, result
FROM audit_log ORDER BY audit_id;
audit_id | actor_id | source_ip | action | target_id | before_status | after_status | result
----------+----------+------------+------------------+-----------+---------------+--------------+---------
1 | u1024 | 192.0.2.15 | equipment.update | 1 | available | repair | success
(1 row)
アプリはログイン中の利用者、接続元、要求 ID を知っているので、7つの問いをすべて埋められます。「修理に出した」のような操作の意味を action に書けるのもこの方式の強みです。
方式2:トリガーで書く
テーブルに行単位のトリガーを付け、変更のたびにデータベースが監査ログを書きます。利用者の ID はデータベースからは見えないので、アプリがトランザクションの最初に SET LOCAL で渡します。
CREATE FUNCTION audit_row_change() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
INSERT INTO audit_log
(actor_id, actor_type, source_ip, request_id, action,
target_type, target_id, before_data, after_data)
VALUES (
coalesce(nullif(current_setting('app.actor_id', true), ''), session_user),
CASE WHEN nullif(current_setting('app.actor_id', true), '') IS NULL
THEN 'system' ELSE 'user' END,
nullif(current_setting('app.source_ip', true), '')::inet,
nullif(current_setting('app.request_id', true), ''),
TG_TABLE_NAME || '.' || lower(TG_OP),
TG_TABLE_NAME,
CASE WHEN TG_OP = 'DELETE' THEN OLD.equipment_id ELSE NEW.equipment_id END::text,
CASE WHEN TG_OP = 'INSERT' THEN NULL ELSE to_jsonb(OLD) END,
CASE WHEN TG_OP = 'DELETE' THEN NULL ELSE to_jsonb(NEW) END
);
RETURN NULL;
END $$;
CREATE TRIGGER equipment_audit
AFTER INSERT OR UPDATE OR DELETE ON equipment
FOR EACH ROW EXECUTE FUNCTION audit_row_change();
-- アプリからの更新(利用者の情報を渡す)
BEGIN;
SET LOCAL app.actor_id = 'u2048';
SET LOCAL app.source_ip = '192.0.2.31';
SET LOCAL app.request_id = 'req-91bc';
UPDATE equipment SET location = '4F 会議室' WHERE equipment_id = 2;
COMMIT;
-- アプリを通さず、SQL を直接実行した変更
DELETE FROM equipment WHERE equipment_id = 2;
SELECT audit_id, actor_id, actor_type, action, target_id,
before_data->>'location' AS before_loc,
after_data->>'location' AS after_loc
FROM audit_log ORDER BY audit_id;
audit_id | actor_id | actor_type | action | target_id | before_loc | after_loc
----------+----------+------------+------------------+-----------+------------+-----------
1 | u1024 | user | equipment.update | 1 | 3F 倉庫 | 3F 倉庫
2 | u2048 | user | equipment.update | 2 | 5F 棚 2 | 4F 会議室
3 | postgres | system | equipment.delete | 2 | 4F 会議室 |
(3 rows)
3行目が要点です。アプリを通さない直接の DELETE も記録され、actor_id にはデータベースの接続ユーザー名が入りました。 方式1では残らない操作です。
MySQL 8.0 でも同じ考え方で書けます。利用者の情報はユーザー定義変数で渡します。
CREATE TRIGGER equipment_audit_upd
AFTER UPDATE ON equipment FOR EACH ROW
INSERT INTO audit_log
(actor_id, source_ip, request_id, action, target_type, target_id,
before_data, after_data)
VALUES
(COALESCE(@actor_id, USER()), @source_ip, @request_id,
'equipment.update', 'equipment', NEW.equipment_id,
JSON_OBJECT('name', OLD.name, 'status', OLD.status, 'location', OLD.location),
JSON_OBJECT('name', NEW.name, 'status', NEW.status, 'location', NEW.location));
SET @actor_id = 'u1024', @source_ip = '192.0.2.15', @request_id = 'req-7f3a';
UPDATE equipment SET status = 'repair' WHERE equipment_id = 1;
SELECT audit_id, actor_id, source_ip, action,
before_data->>'$.status' AS before_status,
after_data->>'$.status' AS after_status, result
FROM audit_log;
+----------+----------+------------+------------------+---------------+--------------+---------+
| audit_id | actor_id | source_ip | action | before_status | after_status | result |
+----------+----------+------------+------------------+---------------+--------------+---------+
| 1 | u1024 | 192.0.2.15 | equipment.update | available | repair | success |
+----------+----------+------------+------------------+---------------+--------------+---------+
MySQL のトリガーは INSERT・UPDATE・DELETE ごとに別々に作ります。行全体を JSON にする関数が無いため、列を JSON_OBJECT に並べます。
方式3:履歴テーブルを持つ
業務テーブルと同じ列に「いつからいつまで有効だったか」を足したテーブルを持ち、変更のたびに「今の行を閉じて、新しい行を足す」方式です。
CREATE TABLE equipment_history (
equipment_id integer NOT NULL,
name varchar(100) NOT NULL,
status varchar(16) NOT NULL,
location varchar(50) NOT NULL,
valid_from timestamptz NOT NULL,
valid_to timestamptz NOT NULL DEFAULT 'infinity',
changed_by varchar(64) NOT NULL,
PRIMARY KEY (equipment_id, valid_from)
);
INSERT INTO equipment_history VALUES
(1,'プロジェクター A','available','3F 倉庫','2026-10-01 09:00+09','infinity','u1001');
BEGIN;
UPDATE equipment_history SET valid_to = '2026-10-03 14:00+09'
WHERE equipment_id = 1 AND valid_to = 'infinity';
INSERT INTO equipment_history VALUES
(1,'プロジェクター A','repair','3F 倉庫','2026-10-03 14:00+09','infinity','u1024');
COMMIT;
BEGIN;
UPDATE equipment_history SET valid_to = '2026-10-05 10:30+09'
WHERE equipment_id = 1 AND valid_to = 'infinity';
INSERT INTO equipment_history VALUES
(1,'プロジェクター A','available','5F 棚 1','2026-10-05 10:30+09','infinity','u1024');
COMMIT;
SET timezone = 'Asia/Tokyo';
SELECT equipment_id, status, location, valid_from, valid_to, changed_by
FROM equipment_history ORDER BY valid_from;
equipment_id | status | location | valid_from | valid_to | changed_by
--------------+-----------+----------+------------------------+------------------------+------------
1 | available | 3F 倉庫 | 2026-10-01 09:00:00+09 | 2026-10-03 14:00:00+09 | u1001
1 | repair | 3F 倉庫 | 2026-10-03 14:00:00+09 | 2026-10-05 10:30:00+09 | u1024
1 | available | 5F 棚 1 | 2026-10-05 10:30:00+09 | infinity | u1024
(3 rows)
この形の持ち味は、ある時点の姿を1つの SELECT で取り出せることです。
-- 2026-10-04 12:00 時点の姿
SELECT equipment_id, status, location
FROM equipment_history
WHERE equipment_id = 1
AND valid_from <= '2026-10-04 12:00+09'
AND valid_to > '2026-10-04 12:00+09';
equipment_id | status | location
--------------+--------+----------
1 | repair | 3F 倉庫
(1 row)
履歴テーブルは valid_to を UPDATE するので、追記専用にはなりません。証跡としての監査ログとは役割が別で、併用する形が自然です。
3方式の比較と選び方
| 観点 | アプリで書く | トリガーで書く | 履歴テーブルを持つ |
|---|---|---|---|
| 誰が・どこから | そのまま書ける | アプリから値を渡す仕組みが要る | 変更者だけ持つことが多い |
| 操作の意味(承認した、取り消した など) | 書ける | テーブル名と INSERT / UPDATE / DELETE まで | 書けない |
| 拒否された操作・ログイン・閲覧 | 残せる | 残せない(行が変わらないため) | 残せない |
| SQL の直接実行やバッチの変更 | 書き忘れると残らない | 自動で残る | 仕組みによる |
| 対象テーブルが増えたとき | 処理ごとに書く | トリガーを付けるだけ | テーブルごとに履歴テーブルを作る |
| ある時点の姿の再現 | 変更前後をたどって組み立てる | 同左 | 1つの SELECT で取れる |
| 追記専用にできるか | できる | できる | できない(期間の終わりを更新する) |
| 条件 | 選ぶ方式 |
|---|---|
| ログイン、権限変更、拒否された操作まで説明したい | アプリで書く |
| 運用担当が SQL を直接流すことがあり、それも漏らさず残したい | トリガーで書く |
| 「先月末時点の一覧」を画面や帳票で出したい | 履歴テーブルを持つ |
| 対象テーブルが多く、書き忘れを防ぎたい | トリガーで書く(利用者情報はアプリから渡す) |
| 操作の証跡と、データの世代の両方が要る | アプリで書く+履歴テーブル |
方式1と方式2を同じテーブルに重ねると、1回の更新で監査ログが2行できます。テーブルごとにどちらで書くかを決めて、設計書の一覧に残しておくと混ざりません。
追記専用にする権限設定
監査ログは「書けるが、直せない・消せない」状態にします。アプリ用のユーザーに INSERT と SELECT だけを与えます。
PostgreSQL
CREATE ROLE app_user NOLOGIN;
REVOKE ALL ON audit_log FROM PUBLIC;
GRANT INSERT, SELECT ON audit_log TO app_user;
SET ROLE app_user;
INSERT INTO audit_log (actor_id, action, target_type, target_id, result, reason)
VALUES ('u3001', 'equipment.delete', 'equipment', '1', 'denied', '権限なし');
UPDATE audit_log SET actor_id = 'x' WHERE audit_id = 1;
DELETE FROM audit_log WHERE audit_id = 1;
TRUNCATE audit_log;
RESET ROLE;
INSERT 0 1
psql:audit.sql:153: ERROR: permission denied for table audit_log
psql:audit.sql:154: ERROR: permission denied for table audit_log
psql:audit.sql:155: ERROR: permission denied for table audit_log
INSERT だけが通り、UPDATE・DELETE・TRUNCATE は3つとも権限エラーで止まりました。付与された権限は次の SQL で確かめられます。
SELECT grantee, string_agg(privilege_type, ', ' ORDER BY privilege_type) AS privileges
FROM information_schema.role_table_grants
WHERE table_name = 'audit_log' AND grantee = 'app_user'
GROUP BY grantee;
grantee | privileges
----------+----------------
app_user | INSERT, SELECT
(1 row)
MySQL
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'change-me';
GRANT SELECT, INSERT, UPDATE, DELETE ON appdb.equipment TO 'app_user'@'localhost';
GRANT SELECT, INSERT ON appdb.audit_log TO 'app_user'@'localhost';
SHOW GRANTS FOR 'app_user'@'localhost';
+---------------------------------------------------------------------------------------+
| Grants for app_user@localhost |
+---------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `app_user`@`localhost` |
| GRANT SELECT, INSERT ON `appdb`.`audit_log` TO `app_user`@`localhost` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `appdb`.`equipment` TO `app_user`@`localhost` |
+---------------------------------------------------------------------------------------+
app_user で接続して UPDATE・DELETE・TRUNCATE を順に実行した結果です。
ERROR 1142 (42000) at line 1: UPDATE command denied to user 'app_user'@'localhost' for table 'audit_log'
ERROR 1142 (42000) at line 1: DELETE command denied to user 'app_user'@'localhost' for table 'audit_log'
ERROR 1142 (42000) at line 1: DROP command denied to user 'app_user'@'localhost' for table 'audit_log'
MySQL の TRUNCATE は DROP 権限で判定されるため、3行目のエラー文は「DROP command denied」になります。
権限の分担表
| ユーザー | audit_log への権限 | 用途 |
|---|---|---|
| アプリ用(app_user) | INSERT, SELECT | 書く、画面に履歴を出す |
| 参照専用(監査の担当者) | SELECT | 調べる |
| 保守用(テーブルの所有者) | すべて | パーティションの追加と取り外し。日常の業務では使わない |
テーブルの所有者とスーパーユーザーは権限設定の外にいます。保守用のアカウントは使う人と場面を絞り、そのアカウントでの接続自体をデータベースのログに残す運用を組み合わせます。
保持期間の決め方とパーティションでの削除
保持期間は、次の順で決めると抜けがありません。期間の値そのものは、社内の規程や契約、適用される法令の定めを確認して決めます。
| 手順 | 決めること | 記入例 |
|---|---|---|
| 1 | 守るべき定め(規程・契約・法令)で求められる期間 | 規程で1年 |
| 2 | 問い合わせや調査でさかのぼる最長の期間 | 過去6か月の問い合わせが多い |
| 3 | 1と2の長いほうを保持期間にする | 1年 |
| 4 | すぐ検索できる期間と、保管だけする期間を分ける | 直近3か月はテーブル、以降はファイルに書き出して保管 |
| 5 | 1か月の件数と容量を見積もる | 1日2万件 × 30日 × 1件500バイト = 約300MB |
| 6 | 期限が来たデータの外し方と、実行する人を決める | 月初に保守用ユーザーが前年同月のパーティションを外す |
手順5の容量は「1日の件数 × 日数 × 1件の大きさ」で計算できます(20,000 × 30 × 500 = 300,000,000 バイト)。
PostgreSQL:月のパーティションを外す
INSERT INTO audit_log (occurred_at, actor_id, action, target_type, target_id)
VALUES ('2026-09-10 10:00+09', 'u1001', 'equipment.create', 'equipment', '1');
SELECT tableoid::regclass AS partition, count(*) FROM audit_log GROUP BY 1 ORDER BY 1;
ALTER TABLE audit_log DETACH PARTITION audit_log_2026_09;
SELECT tableoid::regclass AS partition, count(*) FROM audit_log GROUP BY 1 ORDER BY 1;
DROP TABLE audit_log_2026_09;
partition | count
-------------------+-------
audit_log_2026_09 | 1
audit_log_2026_10 | 4
(2 rows)
ALTER TABLE
partition | count
-------------------+-------
audit_log_2026_10 | 4
(1 row)
DROP TABLE
DETACH した時点で、その月は audit_log から見えなくなり、単独のテーブルとして残ります。書き出して保管してから DROP する、という2段階にできます。
MySQL:パーティションを落として次の月を足す
ALTER TABLE audit_log DROP PARTITION p2026_09;
ALTER TABLE audit_log ADD PARTITION (PARTITION p2026_11 VALUES LESS THAN ('2026-12-01'));
SELECT partition_name FROM information_schema.partitions
WHERE table_schema='appdb' AND table_name='audit_log';
+----------------+
| PARTITION_NAME |
+----------------+
| p2026_11 |
| p2026_10 |
+----------------+
次の月のパーティションを作り忘れた場合
範囲に合うパーティションが無い日時を INSERT すると、エラーで止まります。
-- PostgreSQL 16
ERROR: no partition of relation "audit_log" found for row
DETAIL: Partition key of the failing row contains (occurred_at) = (2026-12-01 00:00:00+09).
-- MySQL 8.0
ERROR 1526 (HY000) at line 1: Table has no partition for value from column_list
監査ログの INSERT が止まると、同じトランザクションの業務処理も止まります。数か月先までのパーティションをまとめて作る作業を、月次の手順に入れておきます。
確かめて分かったこと
- トリガー方式は、アプリを通さない変更も残した。SQL を直接流した DELETE が、接続ユーザー名つきで記録されました。MySQL でも、変数を渡さずに更新すると actor_id が app_user@localhost になりました。
- トリガー方式には、拒否された操作が入らない。行が変わらないとトリガーが動かないため、result が denied の行はアプリから INSERT する必要がありました。
- ロールバックすると監査ログも一緒に消える。トランザクション内で件数が 4 → 5 と増えたあと、ROLLBACK で 4 に戻りました。「試みたこと」を残したい操作(ログインの拒否など)は、業務処理とは別のトランザクションで書きます。
- MySQL の TRUNCATE は DROP 権限で判定される。INSERT と SELECT だけのユーザーでは「DROP command denied」で止まりました。
- パーティションを使うと主キーに発生日時が入る。PostgreSQL も MySQL も、パーティションキーを主キーに含める定義にして作成できました。audit_id だけで1行を指したい場合は、連番が重ならない採番(IDENTITY や AUTO_INCREMENT)に任せます。
- パーティションの無い月への INSERT はエラーになる。両方のデータベースで、行は入らずエラー文が返りました。
設計レビュー用のチェック表
| 確認すること | 見る場所 |
|---|---|
| 7つの問い(誰が・いつ・どこから・何に・何をした・変更前後・結果)に列が対応している | 項目一覧 |
| 記録する操作の一覧があり、action の名前がそろっている | 操作一覧 |
| 拒否された操作とログインが対象に入っている | 操作一覧 |
| 秘密の値(パスワード、トークン)が変更前後に入らない | 項目一覧の補足 |
| テーブルごとに、アプリで書くかトリガーで書くかが決まっている | 方式の一覧 |
| アプリ用ユーザーの権限が INSERT と SELECT だけになっている | 権限の分担表 |
| 保持期間、外し方、実行する人が決まっている | 保持期間の表 |
| 先の月のパーティションを作る手順がある | 運用手順 |
| 時刻の基準(データベースの時刻、タイムゾーン)が決まっている | 項目一覧 |
よくある質問
Q. 監査ログと操作ログの違いは何ですか? A. 指しているものはほぼ同じで、目的の言い方が違います。利用者の操作を記録したものを操作ログと呼び、それを証跡として書き換えられない形で保管し、あとから説明に使う場合に監査ログと呼ぶことが多いです。
Q. 変更前後の値は、列ごとに持つのと JSON で持つのとどちらがよいですか? A. 対象テーブルが複数あるなら JSON が扱いやすいです。列を足さずにどのテーブルの変更も入れられ、PostgreSQL では before_data->>'status' のように取り出せます。特定の列だけを頻繁に検索するなら、その列を別に持つ形も選べます。
Q. 監査ログが書けないとき、業務処理も止めるべきですか? A. 証跡が必須の操作(権限変更やデータの削除など)は、同じトランザクションに入れて一緒に止める設計にします。閲覧の記録のように欠けても業務に影響が小さいものは、別に書いて業務を先に進める設計も選べます。操作ごとに決めて一覧に書きます。
Q. 監査ログを削除できないようにするには? A. アプリ用のユーザーに INSERT と SELECT だけを与えます。この記事の例では、UPDATE・DELETE・TRUNCATE がすべて権限エラーで止まりました。所有者のアカウントは日常の業務で使わない運用と組み合わせます。
まとめ
- 監査ログの項目は「誰が・いつ・どこから・何に・何をした・変更前後・結果」の7つの問いで決める。
- 1枚の追記専用テーブルに、変更前後を JSON で持つ形から始めると、対象が増えても同じ形で書ける。
- アプリで書くと操作の意味や拒否も残せ、トリガーで書くと直接の SQL も漏れず、履歴テーブルはある時点の姿をすぐ出せる。
- アプリ用ユーザーは INSERT と SELECT だけ。権限エラーで止まることを実際に確かめる。
- 保持期間は月単位のパーティションで管理し、先の月を作る手順も合わせて決める。
- アプリログ・アクセスログとは request_id でつなぐと、1つの操作を横断して追える。
本記事の内容は、ご自由にお使いください。
ご利用の際は、出典として本ページへのリンクを記載いただけますようお願いします。
(記載例)出典:株式会社RJC「監査ログの設計項目一覧|操作ログのテーブル設計例とDDL」
株式会社RJC ― SI事業・SES事業・AI駆動開発。RJCは一緒に成長を楽しめる会社です。
WE ARE HIRING
RJCで一緒に開発しながら、
成長を楽しみませんか?
RJCは、Web・モバイル・AIを活用した開発プロジェクトで、テックリードやPM・PMOも活躍するシステム開発会社です。会社を知る、待遇を確かめる、話を聞いてみる。気になるところ見てみてください!
- 127日年間休日
- 12時間平均残業時間
- 毎日ガチャ遊びココロも大切にする福利厚生。アマギフなどの賞品ラインナップ!
ほかにも、チケットレストラン、書籍読み放題、2年ごとの慰労報奨(休暇 or 金一封)、11期連続の黒字決算。