How to Collect and Review Audit Logs in Aurora MySQL

この文書の適用条件

そのまま実行できるコマンド

-- 対象: Aurora MySQL 2.x / 3.x
-- 権限: 接続権限（グローバル変数の参照のみ）
-- 変更作業: なし（参照のみ）
-- Production 実行: 可能
SHOW GLOBAL VARIABLES WHERE Variable_name IN (
    'server_audit_logging',
    'server_audit_events',
    'server_audit_incl_users',
    'server_audit_excl_users'
);

# 対象: Aurora MySQL（DBクラスターパラメータグループ）
# 権限: IAM rds:DescribeDBClusterParameters
# 変更作業: なし（参照のみ）
# Production 実行: 可能
aws rds describe-db-cluster-parameters \
  --db-cluster-parameter-group-name example-aurora-mysql-cluster-params \
  --query "Parameters[?starts_with(ParameterName, 'server_audit')].{Name:ParameterName,Value:ParameterValue,Apply:ApplyType,Source:Source}" \
  --output table

# 対象: Aurora MySQL（DBクラスターパラメータグループ）
# 権限: IAM rds:ModifyDBClusterParameterGroup
# 変更作業: あり（クラスター全体に効く監査設定の変更）
# Production 実行: 変更管理を通したうえで実施。記録量の増加を必ず見込むこと

# 値にカンマを含むため、shorthand ではなく JSON 形式で指定する
aws rds modify-db-cluster-parameter-group \
  --db-cluster-parameter-group-name example-aurora-mysql-cluster-params \
  --parameters '[
    {"ParameterName":"server_audit_logging","ParameterValue":"1","ApplyMethod":"immediate"},
    {"ParameterName":"server_audit_events","ParameterValue":"CONNECT,QUERY_DDL,QUERY_DCL","ApplyMethod":"immediate"},
    {"ParameterName":"server_audit_excl_users","ParameterValue":"rdsadmin","ApplyMethod":"immediate"}
  ]'

# 対象: Aurora MySQL（DBクラスター）
# 権限: IAM rds:ModifyDBCluster
# 変更作業: あり（ログ出力先の追加）
# Production 実行: 可能。ただしCloudWatch Logsの取り込み量に応じた料金が発生する

aws rds modify-db-cluster \
  --db-cluster-identifier example-aurora-cluster \
  --cloudwatch-logs-export-configuration '{"EnableLogTypes":["audit"]}' \
  --apply-immediately

# 現在の出力設定を確認する
aws rds describe-db-clusters \
  --db-cluster-identifier example-aurora-cluster \
  --query 'DBClusters[0].EnabledCloudwatchLogsExports' \
  --output text

# 対象: Aurora MySQL インスタンス
# 権限: IAM rds:DescribeDBLogFiles, rds:DownloadDBLogFilePortion
# 変更作業: なし（参照のみ）
# Production 実行: 可能

# 1) 監査ログのファイル一覧を取得する
aws rds describe-db-log-files \
  --db-instance-identifier example-aurora-instance \
  --filename-contains audit \
  --query 'DescribeDBLogFiles[].{Name:LogFileName,Size:Size,LastWritten:LastWritten}' \
  --output table

# 2) 1) で得たファイル名を指定して内容を取得する
aws rds download-db-log-file-portion \
  --db-instance-identifier example-aurora-instance \
  --log-file-name "audit/audit.log.0.0" \
  --starting-token 0 \
  --output text

# 対象: CloudWatch Logs（Aurora MySQL の audit ロググループ）
# 権限: IAM logs:StartQuery, logs:GetQueryResults
# 変更作業: なし（参照のみ）
# Production 実行: 可能

# 監査ログはカンマ区切りのレコードとして出力されるため、まず生の行を数件確認し、
# 実際のフィールド順を目視で確認してから parse のパターンを決める
fields @timestamp, @message
| sort @timestamp desc
| limit 20

# 特定ユーザーの操作だけを抽出する例（フィールド順を確認したうえで使う）
fields @timestamp, @message
| filter @message like /sample_user/
| sort @timestamp desc
| limit 100

結果の読み方

こういう状況で使います

• Can't later check "who changed this table and when"
• An audit requirement came up, but it's unclear how much is currently being recorded
• The audit log seems to have been enabled, but the log file is not growing
• Enabling the audit log caused the log volume to far exceed expectations
• A slow query log exists, but there is no record of successful operations

考えられる原因（可能性の高い順）

1. 01

   `server_audit_logging` is not enabled

   Even with event types configured, nothing is recorded while `server_audit_logging` stays 0. Check this value first.

2. 02

   The instance-level parameter group was edited instead

   Advanced Auditing settings live in the DB cluster parameter group. Editing the instance-level DB parameter group has no effect.

3. 03

   The `server_audit_incl_users` setting narrows the scope too much

   When you specify users to include, operations by any other user are not recorded. Check whether something was unintentionally excluded.

4. 04

   A `static` parameter has not been applied via a restart

   Items whose `ApplyType` is `static` do not take effect until a restart. The value in the parameter group and the actual running value can be out of sync.

5. 05

   Including `QUERY` in `server_audit_events` caused a surge in recording volume

   `QUERY` covers every SQL statement. On a high-traffic instance, this greatly increases recording volume, affecting both log storage cost and write load.

確認手順

1. 1

   Check both the running value and the parameter group value

   参照のみ

   Check the global variables via SQL and the parameter group value plus `ApplyType` via the CLI. A mismatch between the two means the change has not been applied.

2. 2

   Check how log files are being generated

   参照のみ

   Use `describe-db-log-files` to check the list of audit log files, their sizes, and last-written times.

3. 3

   Retrieve a few actual records and check the format

   参照のみ

   Check raw lines via `download-db-log-file-portion` or CloudWatch Logs to see whether the needed information is present.

4. 4

   Measure the actual increase in recording volume

   参照のみ

   Compare the growth in log file size before and after the setting change. Measure it rather than relying on an estimate.

5. 5

   Measure the performance impact in a test environment

   中

   Apply a load equivalent to production and measure the difference with and without auditing. The magnitude depends on the workload.

対応方法

バージョン・環境による違い

これで解決しない場合に確認すること

• Check whether the reader instance's audit log is also being collected

  Logs are output per instance. If reads are directed to the reader, the reader-side log is needed too.

• Check whether the audit log retention period meets requirements

  RDS log files are rotated automatically. If long-term retention is needed, you'll need a design that moves them to CloudWatch Logs or S3.

• Check whether the application connects using a shared account

  If everyone connects as the same DB user, collecting the audit log still won't let you identify "who."

• Sort out how this differs from the general log

  The general log records every connection and SQL statement but has no audit-style filtering or exclusion. They serve different purposes.

• Check who has access to the log

  If too many people can view the audit log, that access itself becomes a leak vector.

この文書の根拠と限界

よくある質問

この文書がカバーする質問

• Want to find out who deleted a table in Aurora MySQL
• Want to export RDS's audit log to CloudWatch Logs
• Want to know the difference between the audit log and the general / slow query logs
• Want to know how to reduce the audit log recording volume

リスク表示の意味

• 参照のみデータと設定を変更しません。
• 低影響は限定的ですが、権限と負荷の確認が必要です。
• 中性能・ロック・コストに影響する可能性があります。
• 高障害・データ損失・復旧作業が発生する可能性があります。
• 専門家レビュー必須本番適用前に別途レビューが必須です。

GIIPの対応範囲

The audit log is a system where "starting to collect it" is easier than "keeping it collected and readable when needed." GIIP periodically cross-checks whether the audit configuration has drifted from its intended state, and manages the log volume trend together with the log group retention period. Events worth detecting — such as granting privileges or dropping a table — are routed to notifications, with an AI agent performing the initial check after a notification, while a human decides whether a response is actually needed.

関連サービス

• 24時間監視の対象を確認する
• AWSマネージド運用の範囲を確認する