【Oracle】パスワードポリシーをカスタマイズする方法｜PASSWORD_VERIFY_FUNCTION・複雑性チェック・履歴管理・カスタム関数まで解説

-- 検証関数のインストール（SYS ユーザーで実行、初回のみ）
-- 11g 用
@$ORACLE_HOME/rdbms/admin/utlpwdmg.sql

-- 12c+ 用（utlpwdmg.sql に ORA12C_VERIFY_FUNCTION も含まれる）
@$ORACLE_HOME/rdbms/admin/catpvf.sql

-- ORA12C_VERIFY_FUNCTION を DEFAULT プロファイルに設定
ALTER PROFILE DEFAULT LIMIT
    PASSWORD_VERIFY_FUNCTION ORA12C_VERIFY_FUNCTION;

-- STRONG 版を設定（記号必須）
ALTER PROFILE DEFAULT LIMIT
    PASSWORD_VERIFY_FUNCTION ORA12C_STRONG_VERIFY_FUNCTION;

-- 検証関数を無効化（チェックなし）
ALTER PROFILE DEFAULT LIMIT
    PASSWORD_VERIFY_FUNCTION NULL;

-- 現在の設定を確認
SELECT profile, resource_name, limit
FROM dba_profiles
WHERE resource_name = 'PASSWORD_VERIFY_FUNCTION'
ORDER BY profile;

-- 弱いパスワードで ALTER USER を実行するとエラーになる
ALTER USER test_user IDENTIFIED BY test;
-- ORA-28003: password verification for the specified password failed
-- ORA-20001: Password length less than 8

-- 複雑なパスワードなら成功
ALTER USER test_user IDENTIFIED BY Str0ng_P@ss2026;

-- SYS ユーザーで実行
CREATE OR REPLACE FUNCTION custom_pwd_verify(
    username     VARCHAR2,
    password     VARCHAR2,
    old_password VARCHAR2
) RETURN BOOLEAN IS
BEGIN
    -- ルール(1): 最低 10 文字
    IF LENGTH(password) < 10 THEN
        RAISE_APPLICATION_ERROR(-20001, '10 文字以上で設定してください');
    END IF;

    -- ルール(2): 大文字を 1 文字以上含む
    IF NOT REGEXP_LIKE(password, '[A-Z]') THEN
        RAISE_APPLICATION_ERROR(-20002, '大文字を 1 文字以上含めてください');
    END IF;

    -- ルール(3): 小文字を 1 文字以上含む
    IF NOT REGEXP_LIKE(password, '[a-z]') THEN
        RAISE_APPLICATION_ERROR(-20003, '小文字を 1 文字以上含めてください');
    END IF;

    -- ルール(4): 数字を 1 文字以上含む
    IF NOT REGEXP_LIKE(password, '[0-9]') THEN
        RAISE_APPLICATION_ERROR(-20004, '数字を 1 文字以上含めてください');
    END IF;

    -- ルール(5): 記号を 1 文字以上含む
    IF NOT REGEXP_LIKE(password, '[!@#$%^&*()_+\-=]') THEN
        RAISE_APPLICATION_ERROR(-20005, '記号を 1 文字以上含めてください');
    END IF;

    -- ルール(6): ユーザー名をパスワードに含まない
    IF UPPER(password) LIKE '%' || UPPER(username) || '%' THEN
        RAISE_APPLICATION_ERROR(-20006, 'ユーザー名をパスワードに含めないでください');
    END IF;

    -- ルール(7): 前回のパスワードと異なる（old_password が NULL でない場合）
    IF old_password IS NOT NULL AND password = old_password THEN
        RAISE_APPLICATION_ERROR(-20007, '前回と同じパスワードは使用できません');
    END IF;

    RETURN TRUE;
END;
/

-- カスタム関数をプロファイルに設定
ALTER PROFILE prod_profile LIMIT
    PASSWORD_VERIFY_FUNCTION custom_pwd_verify;

-- 1 年間 かつ 12 回変更するまで再利用不可
ALTER PROFILE prod_profile LIMIT
    PASSWORD_REUSE_TIME 365
    PASSWORD_REUSE_MAX  12;

-- 再利用制限を無効化
ALTER PROFILE dev_profile LIMIT
    PASSWORD_REUSE_TIME UNLIMITED
    PASSWORD_REUSE_MAX  UNLIMITED;

CREATE PROFILE dev_profile LIMIT
    PASSWORD_LIFE_TIME        UNLIMITED
    PASSWORD_GRACE_TIME       UNLIMITED
    PASSWORD_REUSE_TIME       UNLIMITED
    PASSWORD_REUSE_MAX        UNLIMITED
    FAILED_LOGIN_ATTEMPTS     UNLIMITED
    PASSWORD_VERIFY_FUNCTION  NULL;

CREATE PROFILE prod_profile LIMIT
    PASSWORD_LIFE_TIME        90
    PASSWORD_GRACE_TIME       7
    PASSWORD_REUSE_TIME       365
    PASSWORD_REUSE_MAX        12
    FAILED_LOGIN_ATTEMPTS     5
    PASSWORD_LOCK_TIME        1/24    -- 1 時間
    PASSWORD_VERIFY_FUNCTION  ORA12C_VERIFY_FUNCTION;

-- PCI DSS Requirement 8 に準拠
CREATE PROFILE pci_profile LIMIT
    PASSWORD_LIFE_TIME        90      -- 90 日で期限切れ
    PASSWORD_GRACE_TIME       0       -- 猶予なし（即時ロック）
    PASSWORD_REUSE_TIME       365     -- 1 年間再利用不可
    PASSWORD_REUSE_MAX        4       -- 4 世代は再利用不可
    FAILED_LOGIN_ATTEMPTS     6       -- 6 回で自動ロック
    PASSWORD_LOCK_TIME        1/48    -- 30 分ロック
    PASSWORD_VERIFY_FUNCTION  ORA12C_STRONG_VERIFY_FUNCTION;

-- 全プロファイルのパスワード設定
SELECT profile, resource_name, limit
FROM dba_profiles
WHERE resource_type = 'PASSWORD'
ORDER BY profile, resource_name;

-- ユーザーごとのプロファイル割り当て
SELECT username, profile, account_status
FROM dba_users
WHERE username NOT IN ('SYS','SYSTEM','ANONYMOUS','XDB')
ORDER BY profile, username;

-- PASSWORD_VERIFY_FUNCTION の関数本体を確認
SELECT text FROM dba_source
WHERE owner = 'SYS' AND name = 'ORA12C_VERIFY_FUNCTION'
ORDER BY line;

-- パスワード変更後、24 時間は旧パスワードでもログイン可能
ALTER PROFILE app_profile LIMIT
    PASSWORD_ROLLOVER_TIME 1;  -- 1 日

-- 無効化
ALTER PROFILE app_profile LIMIT
    PASSWORD_ROLLOVER_TIME 0;