【Oracle】監査（AUDIT）機能の設定方法完全ガイド｜伝統的監査・統合監査・FGA・監査ログ確認・設計例まで解説

-- 現在の設定を確認
SHOW PARAMETER audit_trail;
-- audit_trail = NONE（デフォルト: 監査無効）

-- DB テーブルに記録（最も一般的）
ALTER SYSTEM SET audit_trail = DB SCOPE=SPFILE;

-- DB テーブル + 拡張情報（SQL テキスト含む）
ALTER SYSTEM SET audit_trail = DB,EXTENDED SCOPE=SPFILE;

-- OS ファイルに記録
ALTER SYSTEM SET audit_trail = OS SCOPE=SPFILE;

-- 設定変更後は DB 再起動が必要
SHUTDOWN IMMEDIATE;
STARTUP;

-- 全ユーザーのログオンを監査
AUDIT SESSION;

-- 特定ユーザーのログオンのみ監査
AUDIT SESSION BY hr, scott;

-- 失敗したログオンのみ記録（不正アクセス検出）
AUDIT SESSION WHENEVER NOT SUCCESSFUL;

-- CREATE TABLE / DROP TABLE を監査
AUDIT CREATE TABLE;
AUDIT DROP TABLE;

-- 全 DDL を監査
AUDIT CREATE ANY TABLE;
AUDIT ALTER ANY TABLE;
AUDIT DROP ANY TABLE;

-- 特定ユーザーの DDL のみ
AUDIT CREATE TABLE BY hr;

-- employees テーブルへの SELECT を監査
AUDIT SELECT ON hr.employees;

-- employees テーブルへの全 DML を監査
AUDIT INSERT, UPDATE, DELETE ON hr.employees;

-- 成功した操作のみ記録
AUDIT SELECT ON hr.employees WHENEVER SUCCESSFUL;

-- 失敗した操作のみ記録（権限不足エラー等）
AUDIT SELECT ON hr.employees WHENEVER NOT SUCCESSFUL;

-- ログオン監査を解除
NOAUDIT SESSION;

-- 特定テーブルの監査を解除
NOAUDIT SELECT ON hr.employees;

-- 全監査設定を確認
SELECT * FROM dba_stmt_audit_opts;   -- 文レベル
SELECT * FROM dba_obj_audit_opts;    -- オブジェクトレベル

-- (1) ポリシーを作成
CREATE AUDIT POLICY pol_logon_audit
    ACTIONS LOGON, LOGOFF;

-- (2) ポリシーを有効化（全ユーザー対象）
AUDIT POLICY pol_logon_audit;

-- 特定ユーザーのみ対象
AUDIT POLICY pol_logon_audit BY hr, scott;

-- 失敗のみ記録
AUDIT POLICY pol_logon_audit WHENEVER NOT SUCCESSFUL;

-- 特定テーブルへの DML を監査
CREATE AUDIT POLICY pol_emp_dml
    ACTIONS INSERT ON hr.employees,
            UPDATE ON hr.employees,
            DELETE ON hr.employees;

AUDIT POLICY pol_emp_dml;

-- DBA 権限の使用を監査
CREATE AUDIT POLICY pol_dba_actions
    PRIVILEGES CREATE ANY TABLE, DROP ANY TABLE, ALTER USER;

AUDIT POLICY pol_dba_actions;

-- 特定の IP アドレスからのアクセスのみ監査
CREATE AUDIT POLICY pol_external_access
    ACTIONS SELECT ON hr.employees
    WHEN 'SYS_CONTEXT(''USERENV'', ''IP_ADDRESS'') NOT IN (''10.0.0.1'', ''10.0.0.2'')'
    EVALUATE PER SESSION;

AUDIT POLICY pol_external_access;

-- ポリシーを無効化
NOAUDIT POLICY pol_logon_audit;

-- ポリシーを削除
DROP AUDIT POLICY pol_logon_audit;

-- 現在のポリシー一覧を確認
SELECT policy_name, enabled_option, entity_name
FROM audit_unified_enabled_policies
ORDER BY policy_name;

-- 直近 24 時間の監査ログ
SELECT username, action_name, obj_name,
       timestamp, returncode, os_username, userhost
FROM dba_audit_trail
WHERE timestamp >= SYSDATE - 1
ORDER BY timestamp DESC;

-- ログイン失敗の一覧（不正アクセス調査）
SELECT username, userhost, os_username, timestamp, returncode
FROM dba_audit_trail
WHERE action_name = 'LOGON'
  AND returncode <> 0   -- 0 = 成功、それ以外 = 失敗
  AND timestamp >= SYSDATE - 7
ORDER BY timestamp DESC;

-- 直近 24 時間の監査ログ
SELECT dbusername, action_name, object_name, object_schema,
       event_timestamp, return_code, os_username, userhost,
       unified_audit_policies
FROM unified_audit_trail
WHERE event_timestamp >= SYSDATE - 1
ORDER BY event_timestamp DESC;

-- 特定ポリシーのログだけ確認
SELECT dbusername, action_name, object_name, event_timestamp
FROM unified_audit_trail
WHERE unified_audit_policies LIKE '%POL_EMP_DML%'
  AND event_timestamp >= SYSDATE - 7
ORDER BY event_timestamp DESC;

-- 給与列が参照されたときだけ監査
BEGIN
    DBMS_FGA.ADD_POLICY(
        object_schema  => 'HR',
        object_name    => 'EMPLOYEES',
        policy_name    => 'FGA_SALARY_ACCESS',
        audit_column   => 'SALARY',
        audit_condition => 'DEPARTMENT_ID = 10',  -- 部門 10 のみ
        statement_types => 'SELECT, UPDATE'
    );
END;
/

-- FGA ログの確認
SELECT db_user, object_name, policy_name,
       sql_text, timestamp
FROM dba_fga_audit_trail
ORDER BY timestamp DESC;

-- 90 日以前の監査ログを削除
DELETE FROM sys.aud$ WHERE ntimestamp# < SYSDATE - 90;
COMMIT;

-- クリーンアップの初期設定（初回のみ）
BEGIN
    DBMS_AUDIT_MGMT.INIT_CLEANUP(
        audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
        default_cleanup_interval => 720  -- 時間（30 日）
    );
END;
/

-- 手動クリーンアップ（90 日以前を削除）
BEGIN
    DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
        audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
        last_archive_time => SYSDATE - 90
    );
    DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
        audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
        use_last_arch_timestamp => TRUE
    );
END;
/

-- ログオン監査（全ユーザー + 失敗のみ別ポリシー）
CREATE AUDIT POLICY pol_logon ACTIONS LOGON;
AUDIT POLICY pol_logon;

CREATE AUDIT POLICY pol_logon_fail ACTIONS LOGON;
AUDIT POLICY pol_logon_fail WHENEVER NOT SUCCESSFUL;

-- DDL 監査
CREATE AUDIT POLICY pol_ddl
    ACTIONS CREATE TABLE, ALTER TABLE, DROP TABLE,
            CREATE INDEX, DROP INDEX, CREATE USER, DROP USER;
AUDIT POLICY pol_ddl;

-- 機密テーブルの DML 監査
CREATE AUDIT POLICY pol_sensitive_dml
    ACTIONS SELECT ON hr.employees,
            UPDATE ON hr.employees,
            DELETE ON hr.employees,
            SELECT ON finance.transactions;
AUDIT POLICY pol_sensitive_dml;

-- 過去 7 日間のログイン失敗を IP 別に集計
SELECT userhost, dbusername,
       COUNT(*) AS fail_count,
       MIN(event_timestamp) AS first_attempt,
       MAX(event_timestamp) AS last_attempt
FROM unified_audit_trail
WHERE action_name = 'LOGON'
  AND return_code <> 0
  AND event_timestamp >= SYSDATE - 7
GROUP BY userhost, dbusername
ORDER BY fail_count DESC;

-- 特定ユーザーの直近 24 時間の操作
SELECT event_timestamp, action_name,
       object_schema || '.' || object_name AS object,
       sql_text, return_code
FROM unified_audit_trail
WHERE dbusername = 'HR'
  AND event_timestamp >= SYSDATE - 1
ORDER BY event_timestamp;