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

【講演内容】
HeatWave MySQL で DB監査ログ運用を実装してみた！

　１．HeatWaveサービス化検証
　２．DB監査ログについて
　３．基本的な使い方
　４．DB監査ログ利用時の注意点
　５．DB監査ログの外部保管
　６．外部保管DB監査ログの参照
　７．まとめ

【発表者】
パナソニック インフォメーションシステムズ株式会社
インフラソリューション本部 プラットフォームサービス事業部 インフラ標準サービス部 DB基盤チーム
倉重 正義 氏

【イベント情報】
HeatWavejp Meetup #13
https://heatwavejp.connpass.com/event/349599/

Transcript

1. Copyright（C） Panasonic Information Systems Co., Ltd. All rights reserved. HeatWavejp Meetup #13 HeatWave MySQL でDB監査ログ運用を実装してみた！ パナソニック インフォメーションシステムズ株式会社 Panasonic Information Systems Co., Ltd.
2. 2 Panasonic Information Systems Co., Ltd. 自己紹介 氏名：倉重 正義 所属：パナソニック インフォメーションシステムズ株式会社 業務：データベース構築・運用 （Oracle, MySQL, PostgreSQL, SQL Server）
3. 3 Panasonic Information Systems Co., Ltd. 事業領域
4. Panasonic Transformation（PX） とは 事業の競争力強化に向けて 働き方・ビジネスのやり方含めて変革 経営のスピードアップ目指す 各事業のDX支援と グループ全体のIT経営基盤の底上げ 2021年 5月 PX準備プロジェクト発足 4 Panasonic Information Systems Co., Ltd.
5. 5 Panasonic Information Systems Co., Ltd. アジェンダ １．HeatWaveサービス化検証 ２．DB監査ログについて ３．基本的な使い方 ４．DB監査ログ利用時の注意点 ５．DB監査ログの外部保管 ６．外部保管DB監査ログの参照 ７．まとめ
6. １．HeatWaveサービス化検証 6 Panasonic Information Systems Co., Ltd. パナソニックグループ向けHeatWaveのサービス提供に向け検証 クラウド上で SLAが満たせるか セキュリティー 基準評価 他システム連携 （オンプレ連携など） NWレイテンシ 影響調査 コスト評価 パフォーマンス 可用性 運用評価 （メンテナンスなど） HeatWave独自機能 （機械学習・生成AIなど） 今回はDB監査ログ運用について検証した内容を発表
7. ２．DB監査ログについて 7 Panasonic Information Systems Co., Ltd. セキュリティ関連の機能の１つでDB内での操作を記録するもの。 重要なシステムを運用する上で、重要な役割を果たす。 HeatWaveでは、MySQL Enterprise Audit にてDB監査ログ取得が可能 DB監査ログ 監査ログ出力内容 ・いつ（日時） ・どこで（接続元端末） ・誰が（DBユーザ） ・何をした（発行クエリなど） 重要テーブルへの アクセス検知 SOX(内部統制）証跡 インシデント発生時の 調査
8. ３．基本的な使い方 デフォルトでは監査ログ出力されない。監査ログを出力する為の設定が必要。 ▪監査ログ設定 ①ログフィルタ作成 どの操作をログ出力するか設定 ②ログフィルタ割当 DBユーザに作成したログフィルタを割当 ▪監査ログ設定後 ③ログ参照 ログ参照ファンクション経由で監査ログ参照 ※監査ログファイルへの直接参照不可 8 Panasonic Information Systems Co., Ltd. 監査ログファイル（JSON形式） ログフィルタ DBユーザ ①ログフィルタ作成 ②ログフィルタ割当 監査ログ出力 DBユーザ ログ参照ファンクション ③ログ参照
9. ３．基本的な使い方 ー ①ログフィルタ設定 どの操作を監査ログを出力するか設定。フィルタ設定内容は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; 9 Panasonic Information Systems Co., Ltd.
10. ３．基本的な使い方 ー ①ログフィルタ設定 ▪イベント、イベントサブクラス 【参考】 https://dev.mysql.com/doc/refman/8.0/ja/audit-log-filter-definitions.html イベントクラス イベントサブクラス 説明 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 10 Panasonic Information Systems Co., Ltd.
11. ３．基本的な使い方 ー ②ログフィルタ割当 ①で作成したログフィルタを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; 11 Panasonic Information Systems Co., Ltd.
12. ３．基本的な使い方 ー ③ログ参照 監査ログ参照ファンクションを使ってログ参照。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(); 12 Panasonic Information Systems Co., Ltd.
13. ３．基本的な使い方 ー ③ログ参照 JSON形式のログは視覚的に分かりづらいため、JSON_TABLE関数にてテーブル形式の表示も可能 13 Panasonic Information Systems Co., Ltd.
14. ４．DB監査ログ利用時の注意点 １．ログファイルへのアクセスがファンクション経由 ファンクション経由でのログアクセスに制限されており、使い勝手が悪い（ログ一括取得ができない等） ２．ログローテートや保持期間の設定が固定 下記パラメータで定義されているが、変更不可 audit_log_rotate_on_size = 52428800 ⇒ 50MBでログローテート audit_log_prune_seconds = 604800 ⇒ ローテートされた監査ログファイルを7週間経過で削除 audit_log_max_size = 5368709120 ⇒ 監査ログファイルの合計が5GBを超えると 最も古いローテートされた監査ファイルが削除される ３．ログファイル削除時に外部保管されない 放っておくと監査ログが削除されてしまうため、利用者側で外部保管しておく必要がある。 ログの保管期間の要件があるシステムは外部保管対応必須！ ⇒ 実運用を見据え、ログ外部保管の追加対応を実施 14 Panasonic Information Systems Co., Ltd.
15. ５．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 ▪スクリプト処理概要 15 Panasonic Information Systems Co., Ltd. クライアント （スクリプト実行元） HeatWave OCI Object Storage ①監査ログ取得 監査ログ格納テーブル （新規作成） 監査ログファイル ②取得したログを投入 ③監査ログテーブルエクスポート (MySQL Shellのエクスポートコマンド) ④Object Storageへアップロード 監査ログダンプファイル
16. ５．DB監査ログの外部保管 公開されているスクリプトを実行した結果 ⇒ 欲しい項目（table_accessイベントの内容）がない。 カラム長不足で、長文クエリがテーブルに入っていない。 【対応】 欲しい情報を取得できるようにテーブル定義及びスクリプト修正 16 Panasonic Information Systems Co., Ltd. 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; ")
17. ５．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'); 17 Panasonic Information Systems Co., Ltd.
18. ６．外部保管DB監査ログの参照 18 Panasonic Information Systems Co., Ltd. 外部保管した監査ログの参照ができるかどうか２パターンで確認 ①MySQL ShellのインポートコマンドでMDSにインポート ⇒ 問題なくインポート及び参照可能 ②Lakehouseの機能を利用して、ダンプファイルをHeatWaveクラスタにロード ⇒ ダンプが圧縮ありで出力されていた為、ロード不可 公開スクリプトを修正し、非圧縮でcsvファイルをエクスポートするように変更後、ロードできることを確認
19. ７．まとめ 19 Panasonic Information Systems Co., Ltd. ・DB監査ログが取得できることを確認 ・DB監査ログはログ消込の設定がされており、そのままにしておくとログ消失してしまう ・利用者自身でDB監査ログを退避する必要があり、公開されているスクリプトにて外部保管が可能 ※要件に応じてスクリプトの修正必要 ・外部保管したDB監査ログをDBへロード及び参照できることを確認 【Oracle社への要望】 ・DB監査ログのローテートや保持期間の各種パラメータのカスタマイズ ・DB監査ログの外部保管をHeatWaveのサービスとして実装
20. ご清聴ありがとうございました 20 Panasonic Information Systems Co., Ltd.