【PL/SQL】ロギングとトレーサビリティを両立する監査設計

【PL/SQL】ロギングとトレーサビリティを両立する監査設計 PL/SQL
2025.10.13

PL/SQLの業務処理で障害が起きたとき、「どの画面・どのユーザー・どの処理IDから実行され、どのSQLや監査記録につながるのか」を追えないと、原因調査に時間がかかります。一方で、何でもログに残すとI/Oが増え、個人情報や機微情報を余計に保存し、監査ログそのものが運用リスクになります。

この記事では、アプリケーションログ、データベース監査、SQLトレースを役割ごとに分け、相関IDで横断検索できる設計をまとめます。DBMS_APPLICATION_INFO、DBMS_SESSION.SET_IDENTIFIER、AUTONOMOUS_TRANSACTION、Unified Auditing、FGA、JSONログ、保持期間、マスキングまで、実務で破綻しにくい形に寄せて整理します。

先に結論

  • 業務ログは「業務イベントの再構成」、監査は「真正性と責任追跡」、トレースは「性能・根因分析」に分けます。
  • 三つの記録を correlation_id と client_identifier で結びます。
  • ロガーは AUTONOMOUS_TRANSACTION で残せますが、業務データとログの整合は別管理になります。
  • JSONは文字列連結で作らず、JSON_OBJECT などで生成します。
  • 個人情報・機微情報は、保存前にマスク、ハッシュ化、保持期間の制限を決めます。

ジョブ実行ログの設計は ジョブ実行履歴の集中管理と異常検知、例外時の再送出やリトライは 例外設計と再試行パターン、性能調査は 実行計画と統計情報でSQL性能診断する方法 と合わせて読むと、運用全体としてつながります。

スポンサーリンク

ログ・監査・トレースの役割を分ける

最初に、何をどこへ残すかを分けます。ここを曖昧にすると、アプリログに監査要件を詰め込みすぎたり、監査ビューに業務調査を期待しすぎたりして、どちらも使いにくくなります。

  • アプリケーションログ: 注文作成、承認、取消、外部連携など、業務イベントを検索できる形で残します。
  • データベース監査: 誰がどの表や機微列へアクセスしたかを、改ざんされにくい監査証跡として残します。
  • トレース: 待機イベント、バインド、実行経路など、性能や不具合の根因分析に必要な情報を一時的に収集します。

全てを常時最大量で残すのではなく、普段は業務ログと必要最小限の監査、問題発生時だけ対象モジュールのトレースを有効化するのが現実的です。

監査対応ログテーブルを設計する

ログテーブルは検索しやすい固定列と、拡張しやすいJSON列を分けます。バックトレースやコールスタックは長くなるため、VARCHAR2(256) のような短い列へ詰め込まないようにします。

create-app-log-table.sql
CREATE TABLE app_log (
  log_id           NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  log_ts           TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
  severity         VARCHAR2(10) NOT NULL
                   CHECK (severity IN ('DEBUG','INFO','WARN','ERROR','FATAL')),
  module           VARCHAR2(64),
  action           VARCHAR2(64),
  client_id        VARCHAR2(128),
  correlation_id   VARCHAR2(64),
  user_name        VARCHAR2(128),
  target_type      VARCHAR2(64),
  target_key       VARCHAR2(128),
  result_code      VARCHAR2(64),
  message          VARCHAR2(1000),
  payload_json     CLOB CHECK (payload_json IS JSON),
  error_stack      VARCHAR2(4000),
  error_backtrace  VARCHAR2(4000),
  call_stack       CLOB,
  created_by       VARCHAR2(128) DEFAULT SYS_CONTEXT('USERENV','SESSION_USER')
)
LOB (payload_json) STORE AS SECUREFILE (ENABLE STORAGE IN ROW)
LOB (call_stack) STORE AS SECUREFILE (ENABLE STORAGE IN ROW);

CREATE INDEX ix_app_log_correlation
  ON app_log(correlation_id, log_ts);

CREATE INDEX ix_app_log_module_action
  ON app_log(module, action, log_ts);

CREATE INDEX ix_app_log_target
  ON app_log(target_type, target_key, log_ts);

CREATE INDEX ix_app_log_severity_ts
  ON app_log(severity, log_ts);

FORMAT_ERROR_BACKTRACE と FORMAT_CALL_STACK は短い文字列とは限りません。ログのためのINSERTが列長エラーで失敗すると、本当に欲しい障害情報が消えます。エラースタックは VARCHAR2(4000) で切り詰め、詳細なコールスタックは CLOB に逃がすと安全です。

相関IDと実行文脈を統一する

ログ、監査、トレースを後から結ぶには、同じ相関IDを複数の場所へ設定します。アプリケーションログには correlation_id、セッションには CLIENT_IDENTIFIER、実行文脈には MODULE と ACTION を入れます。

pkg-app-context.sql
CREATE OR REPLACE CONTEXT app_ctx USING pkg_app_ctx;
/

CREATE OR REPLACE PACKAGE pkg_app_ctx AUTHID DEFINER AS
  PROCEDURE init(
    p_correlation_id IN VARCHAR2,
    p_client_id      IN VARCHAR2,
    p_module         IN VARCHAR2,
    p_action         IN VARCHAR2
  );

  FUNCTION correlation_id RETURN VARCHAR2;
END pkg_app_ctx;
/

CREATE OR REPLACE PACKAGE BODY pkg_app_ctx AS
  PROCEDURE init(
    p_correlation_id IN VARCHAR2,
    p_client_id      IN VARCHAR2,
    p_module         IN VARCHAR2,
    p_action         IN VARCHAR2
  ) IS
  BEGIN
    DBMS_SESSION.SET_CONTEXT('APP_CTX', 'CORRELATION_ID', p_correlation_id);
    DBMS_SESSION.SET_IDENTIFIER(SUBSTR(p_client_id, 1, 64));
    DBMS_APPLICATION_INFO.SET_MODULE(
      module_name => SUBSTR(p_module, 1, 48),
      action_name => SUBSTR(p_action, 1, 32)
    );
  END;

  FUNCTION correlation_id RETURN VARCHAR2 IS
  BEGIN
    RETURN SYS_CONTEXT('APP_CTX', 'CORRELATION_ID');
  END;
END pkg_app_ctx;
/

DBMS_APPLICATION_INFO の MODULE と ACTION は長さ制限があります。長い業務名をそのまま入れるのではなく、検索しやすい短いコードへ寄せます。利用権限やDefiner/Invokerの考え方は AUTHIDと権限管理の設計 も参考になります。

JSONは文字列連結ではなくJSON_OBJECTで作る

ログのペイロードを文字列連結で作ると、ダブルクォート、改行、NULL、マルチバイト文字で壊れやすくなります。Oracleのバージョンが対応しているなら、JSON_OBJECT で生成します。

build-json-payload.sql
SELECT JSON_OBJECT(
         'order_id' VALUE :p_order_id,
         'customer_id' VALUE :p_customer_id,
         'amount' VALUE :p_amount,
         'result' VALUE :p_result_code,
         'created_at' VALUE SYSTIMESTAMP
         RETURNING CLOB
       ) AS payload_json
FROM dual;

個人情報やカード番号などは、JSONへそのまま入れない方針にします。必要な場合でも、末尾4桁、ハッシュ値、業務キーなど、調査に必要な最小限に変換してから保存します。

ロガーパッケージを実装する

ログは障害時にも残したいので、ロガーは AUTONOMOUS_TRANSACTION で実装することがあります。ただし、これは業務トランザクションとは独立してコミットされるという意味です。業務処理がROLLBACKしてもログだけ残るため、ログを「確定データ」と誤読しない運用説明が必要です。

pkg-logger.sql
CREATE OR REPLACE PACKAGE pkg_logger AUTHID DEFINER AS
  PROCEDURE write_log(
    p_severity    IN VARCHAR2,
    p_message     IN VARCHAR2,
    p_target_type IN VARCHAR2 DEFAULT NULL,
    p_target_key  IN VARCHAR2 DEFAULT NULL,
    p_payload     IN CLOB DEFAULT NULL
  );

  PROCEDURE info(
    p_message     IN VARCHAR2,
    p_target_type IN VARCHAR2 DEFAULT NULL,
    p_target_key  IN VARCHAR2 DEFAULT NULL,
    p_payload     IN CLOB DEFAULT NULL
  );

  PROCEDURE error(
    p_message     IN VARCHAR2,
    p_target_type IN VARCHAR2 DEFAULT NULL,
    p_target_key  IN VARCHAR2 DEFAULT NULL,
    p_payload     IN CLOB DEFAULT NULL
  );
END pkg_logger;
/

CREATE OR REPLACE PACKAGE BODY pkg_logger AS
  PROCEDURE write_log(
    p_severity    IN VARCHAR2,
    p_message     IN VARCHAR2,
    p_target_type IN VARCHAR2 DEFAULT NULL,
    p_target_key  IN VARCHAR2 DEFAULT NULL,
    p_payload     IN CLOB DEFAULT NULL
  ) IS
    PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN
    INSERT INTO app_log(
      severity,
      module,
      action,
      client_id,
      correlation_id,
      user_name,
      target_type,
      target_key,
      message,
      payload_json,
      error_stack,
      error_backtrace,
      call_stack
    ) VALUES (
      SUBSTR(p_severity, 1, 10),
      SYS_CONTEXT('USERENV', 'MODULE'),
      SYS_CONTEXT('USERENV', 'ACTION'),
      SYS_CONTEXT('USERENV', 'CLIENT_IDENTIFIER'),
      pkg_app_ctx.correlation_id,
      SYS_CONTEXT('USERENV', 'SESSION_USER'),
      SUBSTR(p_target_type, 1, 64),
      SUBSTR(p_target_key, 1, 128),
      SUBSTR(p_message, 1, 1000),
      p_payload,
      SUBSTR(DBMS_UTILITY.FORMAT_ERROR_STACK, 1, 4000),
      SUBSTR(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, 1, 4000),
      DBMS_UTILITY.FORMAT_CALL_STACK
    );

    COMMIT;
  EXCEPTION
    WHEN OTHERS THEN
      -- ロガーの失敗で業務処理を巻き込まない。
      -- ただし完全に無視すると検知できないため、監視側でログ欠損やエラー率を見る。
      ROLLBACK;
  END;

  PROCEDURE info(
    p_message     IN VARCHAR2,
    p_target_type IN VARCHAR2 DEFAULT NULL,
    p_target_key  IN VARCHAR2 DEFAULT NULL,
    p_payload     IN CLOB DEFAULT NULL
  ) IS
  BEGIN
    write_log('INFO', p_message, p_target_type, p_target_key, p_payload);
  END;

  PROCEDURE error(
    p_message     IN VARCHAR2,
    p_target_type IN VARCHAR2 DEFAULT NULL,
    p_target_key  IN VARCHAR2 DEFAULT NULL,
    p_payload     IN CLOB DEFAULT NULL
  ) IS
  BEGIN
    write_log('ERROR', p_message, p_target_type, p_target_key, p_payload);
  END;
END pkg_logger;
/

WHEN OTHERS THEN ROLLBACK でロガー失敗を握りつぶす場合でも、完全に見えなくしてよいわけではありません。ログ件数の急減、FATALが出ていないのに処理失敗が増える、ログ表領域が逼迫している、といった監視を別途置きます。

例外捕捉では記録してから再送出する

例外をログに残したあと、原則として RAISE で呼び出し元へ再送出します。ログを残して正常終了扱いにすると、上位層のリトライや補償処理が動かなくなります。

log-error-and-reraise.sql
CREATE OR REPLACE PACKAGE pkg_order AS
  PROCEDURE place_order(p_order_id IN NUMBER);
END pkg_order;
/

CREATE OR REPLACE PACKAGE BODY pkg_order AS
  PROCEDURE place_order(p_order_id IN NUMBER) IS
    v_payload CLOB;
  BEGIN
    SELECT JSON_OBJECT(
             'order_id' VALUE p_order_id,
             'phase' VALUE 'start'
             RETURNING CLOB
           )
    INTO v_payload
    FROM dual;

    pkg_logger.info(
      p_message     => 'start place_order',
      p_target_type => 'ORDER',
      p_target_key  => TO_CHAR(p_order_id),
      p_payload     => v_payload
    );

    -- 業務処理
    NULL;

    pkg_logger.info('end place_order', 'ORDER', TO_CHAR(p_order_id), NULL);
  EXCEPTION
    WHEN OTHERS THEN
      SELECT JSON_OBJECT(
               'order_id' VALUE p_order_id,
               'sqlcode' VALUE SQLCODE,
               'sqlerrm' VALUE SQLERRM
               RETURNING CLOB
             )
      INTO v_payload
      FROM dual;

      pkg_logger.error(
        p_message     => SQLERRM,
        p_target_type => 'ORDER',
        p_target_key  => TO_CHAR(p_order_id),
        p_payload     => v_payload
      );
      RAISE;
  END;
END pkg_order;
/

FGAとUnified Auditingの条件を実在列で書く

FGAは機微列の参照を監査するのに有効ですが、audit_condition は対象表に存在する列や条件で書きます。存在しない new_value のような列を例にすると、実装時に混乱します。

unified-audit-and-fga.sql
-- Unified Auditing: 顧客表の参照と更新を監査する例
CREATE AUDIT POLICY pol_customer_access
  ACTIONS SELECT, UPDATE ON app.customer
  WHEN 'SYS_CONTEXT(''USERENV'',''CLIENT_IDENTIFIER'') IS NOT NULL'
  EVALUATE PER SESSION;

AUDIT POLICY pol_customer_access;

-- FGA: 機微列を含む行を参照した場合だけ監査する例
BEGIN
  DBMS_FGA.ADD_POLICY(
    object_schema   => 'APP',
    object_name     => 'CUSTOMER',
    policy_name     => 'FGA_CUSTOMER_SENSITIVE',
    audit_condition => 'CARD_NO IS NOT NULL OR SSN IS NOT NULL',
    audit_column    => 'CARD_NO,SSN',
    statement_types => 'SELECT,UPDATE',
    handler_module  => NULL,
    enable          => TRUE
  );
END;
/

監査ポリシーは強力ですが、対象を広げすぎると監査量が増え、SYSAUXや監査表領域を圧迫します。監査対象の表、列、操作、条件を業務要件に合わせて絞り込みます。

監査ログとアプリログを相関IDで突き合わせる

障害調査では、相関IDを起点にアプリログ、監査ビュー、セッション情報を横断します。CLIENT_IDENTIFIER に相関IDまたはユーザー識別子を入れておくと、突き合わせがしやすくなります。

correlation-investigation.sql
-- アプリログを相関IDで検索
SELECT
  log_ts,
  severity,
  module,
  action,
  target_type,
  target_key,
  message
FROM app_log
WHERE correlation_id = :correlation_id
ORDER BY log_ts;

-- Unified Audit Trailと粗く突き合わせる
SELECT
  a.event_timestamp,
  a.dbusername,
  a.action_name,
  a.object_schema,
  a.object_name,
  a.client_identifier,
  l.message
FROM unified_audit_trail a
LEFT JOIN app_log l
  ON l.client_id = a.client_identifier
WHERE a.client_identifier = :client_identifier
ORDER BY a.event_timestamp;

本番では、相関ID、クライアントID、業務キー、時刻範囲のどれで引けるかが重要です。問い合わせ対応や障害調査の初動で使う検索キーを先に決め、それに合わせて索引を作ります。

オンデマンドトレースを安全に使う

詳細トレースは強力ですが、常時有効にすると負荷とファイル量が増えます。対象サービス、モジュール、アクションを絞り、調査が終わったら必ず無効化します。サービス名は環境によって異なるため、事前に確認してから指定します。

enable-module-trace.sql
-- サービス名を確認
SELECT SYS_CONTEXT('USERENV', 'SERVICE_NAME') AS service_name
FROM dual;

-- 対象モジュールだけトレースを有効化
BEGIN
  DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE(
    service_name => 'APP_SERVICE',
    module_name  => 'ORDER',
    action_name  => 'PLACE',
    waits        => TRUE,
    binds        => TRUE
  );
END;
/

-- 調査後は必ず無効化
BEGIN
  DBMS_MONITOR.SERV_MOD_ACT_TRACE_DISABLE(
    service_name => 'APP_SERVICE',
    module_name  => 'ORDER',
    action_name  => 'PLACE'
  );
END;
/

バインド値を取るトレースでは、機微情報がトレースファイルに残る可能性があります。調査対象、期間、権限、ファイルの削除ルールを決めてから有効化します。

個人情報と保持期間を設計する

監査設計では、ログに何を残すかだけでなく、何を残さないかも重要です。個人情報、カード番号、認証情報、秘密鍵、アクセストークンなどは、原則としてログへ保存しません。必要な場合はマスク、ハッシュ、トークン化、保持期間の短縮を検討します。

  • カード番号は末尾4桁だけ残す
  • メールアドレスはハッシュ化またはドメインのみ残す
  • アクセストークン、パスワード、秘密鍵は残さない
  • DEBUGログは本番では原則無効化する
  • 保存期間は監査要件と容量見積もりから決める
purge-app-log.sql
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'JOB_APP_LOG_PURGE',
    job_type        => 'PLSQL_BLOCK',
    job_action      => q'[
      DECLARE
        v_cutoff TIMESTAMP WITH TIME ZONE := SYSTIMESTAMP - INTERVAL '90' DAY;
      BEGIN
        DELETE FROM app_log
        WHERE log_ts < v_cutoff
          AND severity NOT IN ('FATAL', 'ERROR');
        COMMIT;
      END;]',
    start_date      => SYSTIMESTAMP + INTERVAL '5' MINUTE,
    repeat_interval => 'FREQ=DAILY;BYHOUR=3;BYMINUTE=0;BYSECOND=0',
    enabled         => TRUE,
    auto_drop       => FALSE
  );
END;
/

大量ログをDELETEで消すだけではUNDOやREDOが増えます。ログ量が大きい場合は、日付パーティション、圧縮、パーティションDROP、別表退避を検討します。ジョブとして運用するなら、実行履歴や異常検知も合わせて設計します。

運用チェックリスト

  • ログ、監査、トレースの役割が分かれているか
  • 相関IDとCLIENT_IDENTIFIERで横断検索できるか
  • JSONを文字列連結で作っていないか
  • バックトレースやコールスタックの列長が不足していないか
  • AUTONOMOUS_TRANSACTIONの意味を運用者が理解しているか
  • FGA条件が実在列と業務要件に合っているか
  • トレースに機微情報が残る可能性を把握しているか
  • 個人情報のマスク、保持期間、削除方法が決まっているか
  • ログ表の容量、索引、パーティション方針を決めているか

まとめ

PL/SQLの監査設計では、アプリケーションログ、データベース監査、トレースを同じものとして扱わず、それぞれの役割を分けることが大切です。業務イベントはログへ、真正性が必要なアクセス記録は監査へ、性能や根因分析はオンデマンドトレースへ寄せます。

そのうえで、相関ID、DBMS_APPLICATION_INFO、CLIENT_IDENTIFIER を使って記録をつなげば、障害時に実行経路を再構成しやすくなります。ログの安全性は、書き方だけでなく、個人情報を残さない設計、保持期間、容量管理、監査対象の絞り込みまで含めて考える必要があります。