HeatWavejp_Meetup_13_HeatWave MySQL で DB監査ログ運用を実装してみた！[倉重正義氏 (パナソニック インフォメーションシステムズ)]

HeatWavejp Meetup #13 HeatWave MySQL でDB監査ログ運用を実装してみた！ パナソニック インフォメーションシステムズ株式会社 Panasonic Information Systems Co., Ltd. Copyright（C） Panasonic Information Systems Co., Ltd. All rights reserved.

自己紹介 氏名：倉重 正義 所属：パナソニック インフォメーションシステムズ株式会社 業務：データベース構築・運用 （Oracle, MySQL, PostgreSQL, SQL Server） Panasonic Information Systems Co., Ltd. 2

事業領域 Panasonic Information Systems Co., Ltd. 3

Panasonic Transformation（PX） とは 各事業のDX支援と グループ全体のIT経営基盤の底上げ 事業の競争力強化に向けて 働き方・ビジネスのやり方含めて変革 経営のスピードアップ目指す 2021年 5月 PX準備プロジェクト発足 Panasonic Information Systems Co., Ltd. 4

アジェンダ １．HeatWaveサービス化検証 ２．DB監査ログについて ３．基本的な使い方 ４．DB監査ログ利用時の注意点 ５．DB監査ログの外部保管 ６．外部保管DB監査ログの参照 ７．まとめ Panasonic Information Systems Co., Ltd. 5

１．HeatWaveサービス化検証 パナソニックグループ向けHeatWaveのサービス提供に向け検証 今回はDB監査ログ運用について検証した内容を発表 パフォーマンス 運用評価 可用性 （メンテナンスなど） クラウド上で セキュリティー 他システム連携 NWレイテンシ SLAが満たせるか 基準評価 （オンプレ連携など） 影響調査 コスト評価 HeatWave独自機能 （機械学習・生成AIなど） Panasonic Information Systems Co., Ltd. 6

２．DB監査ログについて セキュリティ関連の機能の１つでDB内での操作を記録するもの。 重要なシステムを運用する上で、重要な役割を果たす。 HeatWaveでは、MySQL Enterprise Audit にてDB監査ログ取得が可能 重要テーブルへの アクセス検知 SOX(内部統制）証跡 監査ログ出力内容 ・いつ（日時） DB監査ログ ・どこで（接続元端末） ・誰が（DBユーザ） ・何をした（発行クエリなど） インシデント発生時の 調査 Panasonic Information Systems Co., Ltd. 7

３．基本的な使い方 デフォルトでは監査ログ出力されない。監査ログを出力する為の設定が必要。 ■監査ログ設定 ①ログフィルタ作成 どの操作をログ出力するか設定 ②ログフィルタ割当 DBユーザに作成したログフィルタを割当 ②ログフィルタ割当 ①ログフィルタ作成 監査ログ出力 ログフィルタ DBユーザ ■監査ログ設定後 ③ログ参照 ログ参照ファンクション経由で監査ログ参照 ※監査ログファイルへの直接参照不可 監査ログファイル（JSON形式） ③ログ参照 DBユーザ ログ参照ファンクション Panasonic Information Systems Co., Ltd. 8

３．基本的な使い方 ー ①ログフィルタ設定
どの操作を監査ログを出力するか設定。フィルタ設定内容はJSON形式で指定。
SELECT audit_log_filter_set_filter(‘ログフィルタ名’, ‘{“filter”: {フィルタ設定}}’);

例）すべての操作の監査ログ出力をしたい場合
SELECT audit_log_filter_set_filter('log_all', '{"filter": {"log":true}}’);

例）特定クラスの監査ログ出力したい場合
SELECT audit_log_filter_set_filter('log_conn_events','{ "filter": { "class": { "name": "connection" } } }’);

設定したフィルタは下記クエリで確認可能
SELECT * FROM mysql_audit.audit_log_filter;

Panasonic Information Systems Co., Ltd.

9

３．基本的な使い方 ー ①ログフィルタ設定 ■イベント、イベントサブクラス イベントクラス イベントサブクラス 説明 connection connect 接続の開始 connection change_user ユーザ切替 connection disconnect 接続の終了 general status コマンドの戻り値 message internal 内部で生成されたメッセージ message user audit_api_message_emit_udf に生成されたメッセージ table_access read データ参照SQL table_access delete データ削除SQL table_access insert データ挿入SQL table_access update データ更新SQL 【参考】 https://dev.mysql.com/doc/refman/8.0/ja/audit-log-filter-definitions.html Panasonic Information Systems Co., Ltd. 10

• https://dev.mysql.com/doc/refman/8.0/ja/audit-log-filter-definitions.html

３．基本的な使い方 ー ②ログフィルタ割当 ①で作成したログフィルタをDBユーザに割当 SELECT audit_log_filter_set_user(‘DBユーザ名’, ‘ログフィルタ名’); 例）すべてのユーザにフィルタを割当する場合 SELECT audit_log_filter_set_user('%', 'log_all’); 例）特定ユーザにフィルタ割当する場合 SELECT audit_log_filter_set_user('user_abc@%', 'log_all’); 設定内容は下記クエリで確認可能 SELECT * FROM mysql_audit.audit_log_user; Panasonic Information Systems Co., Ltd. 11

３．基本的な使い方 ー ③ログ参照
監査ログ参照ファンクションを使ってログ参照。JSON形式で返ってくる
例）直近のタイムスタンプのログを参照する場合
SELECT audit_log_read(audit_log_read_bookmark());

例）指定した時間以降のログを参照する場合
SELECT audit_log_read('{ "start": { "timestamp": "2025-03-18 15:33:37" } }’);

⇒ バッファサイズに収まる行数だけ返ってくる。全行は返ってこない。
以降のログを参照したい場合は、下記を繰り返し実行
SELECT audit_log_read();

Panasonic Information Systems Co., Ltd.

12

３．基本的な使い方 ー ③ログ参照 JSON形式のログは視覚的に分かりづらいため、JSON_TABLE関数にてテーブル形式の表示も可能 Panasonic Information Systems Co., Ltd. 13

４．DB監査ログ利用時の注意点 １．ログファイルへのアクセスがファンクション経由 ファンクション経由でのログアクセスに制限されており、使い勝手が悪い（ログ一括取得ができない等） ２．ログローテートや保持期間の設定が固定 下記パラメータで定義されているが、変更不可 audit_log_rotate_on_size = 52428800 audit_log_prune_seconds = 604800 audit_log_max_size = 5368709120 ⇒ 50MBでログローテート ⇒ ローテートされた監査ログファイルを7週間経過で削除 ⇒ 監査ログファイルの合計が5GBを超えると 最も古いローテートされた監査ファイルが削除される ３．ログファイル削除時に外部保管されない 放っておくと監査ログが削除されてしまうため、利用者側で外部保管しておく必要がある。 ログの保管期間の要件があるシステムは外部保管対応必須！ ⇒ 実運用を見据え、ログ外部保管の追加対応を実施 Panasonic Information Systems Co., Ltd. 14

５．DB監査ログの外部保管 DB監査ログのテーブル投入・外部保管のスクリプトが公開されている 【Oralce MySQLブログ】 https://blogs.oracle.com/mysql/post/heatwave-audit-archive-and-dump-to-object-storage 【スクリプト配布先】 https://github.com/ivanxma/mysql_audit_archive/tree/main ■スクリプト処理概要 HeatWave 監査ログファイル ①監査ログ取得 ②取得したログを投入 クライアント （スクリプト実行元） 監査ログ格納テーブル （新規作成） ③監査ログテーブルエクスポート (MySQL Shellのエクスポートコマンド) OCI Object Storage 監査ログダンプファイル ④Object Storageへアップロード Panasonic Information Systems Co., Ltd. 15

• https://blogs.oracle.com/mysql/post/heatwave-audit-archive-and-dump-to-object-storage
• https://github.com/ivanxma/mysql_audit_archive/tree/main

５．DB監査ログの外部保管 公開されているスクリプトを実行した結果 ⇒ 欲しい項目（table_accessイベントの内容）がない。 カラム長不足で、長文クエリがテーブルに入っていない。 【対応】 欲しい情報を取得できるようにテーブル定義及びスクリプト修正 ■テーブル定義変更 赤字部分を追加・修正 CREATE TABLE if not exists audit_archive.`audit_data` ( `server_uuid` varchar(45) NOT NULL, ・ ・ ・ `command_status` varchar(40) DEFAULT NULL, `query` text DEFAULT NULL, `query_status` int DEFAULT NULL, ・ ・ ・ `server_id` varchar(80) DEFAULT NULL, `access_db` varchar(80) DEFAULT NULL, `access_table` varchar(80) DEFAULT NULL, `access_sql_command` varchar(80) DEFAULT NULL, `access_query` text DEFAULT NULL, PRIMARY KEY (`server_uuid`,`id`,`ts`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; ■pythonスクリプトを変更 赤字部分を追加・修正 audit_sql1 = ("SELECT @@server_uuid as server_uuid, id, ts, class, event, the_account,login_ip,login_os,login_user,login_proxy,connection_id,db, " " status,connection_type,connect_os,pid,_client_name,_client_version, " " program_name,_platform,command,sql_command,command_status,query, " " query_status,start_server_id,server_os_version,server_mysqlversion,args, " " account_host,mysql_version,the_os,the_os_ver,server_id, " " access_db, access_table, access_sql_command, access_query “ ・ ・ ・ " query TEXT PATH '$.general_data.query’, “ " query_status INT PATH '$.general_data.status', " ・ ・ ・ " server_id VARCHAR(80) PATH '$.startup_data.server_id', " " access_db VARCHAR(80) PATH '$.table_access_data.db', " " access_table VARCHAR(80) PATH '$.table_access_data.table', " " access_sql_command VARCHAR(40) PATH '$.table_access_data.sql_command', " " access_query TEXT PATH '$.table_access_data.query' " " )" ") AS auditdata; ") Panasonic Information Systems Co., Ltd. 16

５．DB監査ログの外部保管
全DBユーザに対して監査ログ出力設定を仕込み、定期的に外部保管スクリプトを実行
⇒ ログ参照ファンクションの処理時間が遅くなる事象が発生
詳細を確認すると、HeatWaveのデフォルトユーザのログ件数が急増していることが発覚
【対応】
HeatWaveのデフォルトユーザの監査ログ取得をしないように設定変更
ログ取得しないフィルタ作成
SELECT audit_log_filter_set_filter('log_nothing', '{"filter": {"log":false}}’);
作成したフィルタをデフォルトユーザに適用
SELECT audit_log_filter_set_user('ociadmin', 'log_nothing’);
SELECT audit_log_filter_set_user('ocidbm', 'log_nothing’);
SELECT audit_log_filter_set_user('oracle-cloud-agent', 'log_nothing');

Panasonic Information Systems Co., Ltd.

17

６．外部保管DB監査ログの参照 外部保管した監査ログの参照ができるかどうか２パターンで確認 ①MySQL ShellのインポートコマンドでMDSにインポート ⇒ 問題なくインポート及び参照可能 ②Lakehouseの機能を利用して、ダンプファイルをHeatWaveクラスタにロード ⇒ ダンプが圧縮ありで出力されていた為、ロード不可 公開スクリプトを修正し、非圧縮でcsvファイルをエクスポートするように変更後、ロードできることを確認 Panasonic Information Systems Co., Ltd. 18

７．まとめ ・DB監査ログが取得できることを確認 ・DB監査ログはログ消込の設定がされており、そのままにしておくとログ消失してしまう ・利用者自身でDB監査ログを退避する必要があり、公開されているスクリプトにて外部保管が可能 ※要件に応じてスクリプトの修正必要 ・外部保管したDB監査ログをDBへロード及び参照できることを確認 【Oracle社への要望】 ・DB監査ログのローテートや保持期間の各種パラメータのカスタマイズ ・DB監査ログの外部保管をHeatWaveのサービスとして実装 Panasonic Information Systems Co., Ltd. 19

ご清聴ありがとうございました Panasonic Information Systems Co., Ltd. 20