【Oracle】プロファイル（PROFILE）完全ガイド｜パスワードポリシー・リソース制限・CREATE PROFILE・ALTER USER PROFILE まで解説

-- パスワードポリシーを設定したプロファイルの作成例
CREATE PROFILE app_user_profile LIMIT

    -- パスワードの有効期限設定
    PASSWORD_LIFETIME         90      -- パスワードの有効期間（日数）。90日ごとに変更が必要
    PASSWORD_GRACE_TIME       7       -- 有効期限切れ後、猶予期間（日数）。猶予期間中は警告が出るが接続できる
    PASSWORD_REUSE_TIME       365     -- 以前のパスワードを再利用できるまでの日数
    PASSWORD_REUSE_MAX        10      -- 以前のパスワードを再利用できるまでの変更回数

    -- ログイン失敗によるロック設定
    FAILED_LOGIN_ATTEMPTS     5       -- 連続失敗した回数でアカウントをロックする
    PASSWORD_LOCK_TIME        1/24    -- ロック時間（日単位: 1/24 = 1時間、1 = 1日、UNLIMITED で手動解除まで）

    -- パスワード複雑性チェック関数（NULL でチェックなし）
    PASSWORD_VERIFY_FUNCTION  ora12c_strong_verify_function;  -- Oracle 提供の関数
    -- ora12c_strong_verify_function: 大文字・小文字・数字・特殊文字を含む8文字以上を要求する

-- 一般ユーザー向け（セキュリティ要件が緩い場合）
CREATE PROFILE app_internal_profile LIMIT
    PASSWORD_LIFETIME         180     -- 180日
    PASSWORD_GRACE_TIME       14
    FAILED_LOGIN_ATTEMPTS     10
    PASSWORD_LOCK_TIME        1/24
    PASSWORD_VERIFY_FUNCTION  NULL    -- 複雑性チェックなし

    -- リソース制限（RESOURCE_LIMIT=TRUE の場合のみ有効）
    SESSIONS_PER_USER         5       -- 同一ユーザーの同時接続セッション数
    IDLE_TIME                 60;     -- アイドルタイム（分）を超えたセッションを切断する

-- DEFAULT プロファイルの設定を変更する（全ユーザーに影響するため慎重に）
ALTER PROFILE DEFAULT LIMIT
    PASSWORD_LIFETIME         UNLIMITED   -- パスワードを無期限にする（要件による）
    FAILED_LOGIN_ATTEMPTS     10
    PASSWORD_LOCK_TIME        1/24;

-- Oracle が提供するパスワード検証関数（$ORACLE_HOME/rdbms/admin/utlpwdmg.sql で定義）
-- ora12c_strong_verify_function: Oracle 12c 以降
--   大文字1文字以上 + 小文字1文字以上 + 数字1文字以上 + 特殊文字1文字以上
--   最低8文字以上が必要
-- ora12c_stig_verify_function: より厳格なルール（STIG 基準準拠）
-- verify_function: 古い形式（8文字以上・英数字混在）

-- 使用可能な検証関数を確認する
SELECT object_name FROM DBA_OBJECTS
WHERE object_type = 'FUNCTION' AND owner = 'SYS'
  AND object_name LIKE '%VERIFY%'
ORDER BY object_name;

-- カスタムの PASSWORD_VERIFY_FUNCTION を作成する例
-- 関数のシグネチャ: username IN VARCHAR2, password IN VARCHAR2, old_password IN VARCHAR2
-- 戻り値: BOOLEAN（TRUE: パスワード有効、FALSE またはエラー: 無効）
CREATE OR REPLACE FUNCTION my_pwd_verify(
    username     IN VARCHAR2,
    password     IN VARCHAR2,
    old_password IN VARCHAR2
) RETURN BOOLEAN IS
BEGIN
    -- 8文字以上を要求する
    IF LENGTH(password) < 8 THEN
        RAISE_APPLICATION_ERROR(-20001, 'パスワードは8文字以上にしてください');
    END IF;
    -- ユーザー名を含まないことを確認する
    IF INSTR(UPPER(password), UPPER(username)) > 0 THEN
        RAISE_APPLICATION_ERROR(-20002, 'パスワードにユーザー名を含めないでください');
    END IF;
    RETURN TRUE;
END;
/

-- RESOURCE_LIMIT を有効にする（デフォルト: FALSE）
ALTER SYSTEM SET RESOURCE_LIMIT = TRUE;

-- リソース制限パラメータ
CREATE PROFILE app_heavy_user LIMIT

    -- セッション関連
    SESSIONS_PER_USER       3         -- 同一ユーザーの同時接続数
    CONCURRENT_SESSIONS     3         -- 並行セッション数（12c 以降）

    -- 接続時間
    CONNECT_TIME            480       -- 1つのセッションの最大接続時間（分）= 8時間
    IDLE_TIME               30        -- アイドル状態の最大時間（分）。超えると切断される

    -- CPU 使用量（1/100秒単位）
    CPU_PER_SESSION         UNLIMITED -- セッションあたりの CPU 時間（通常は UNLIMITED）
    CPU_PER_CALL            UNLIMITED -- 1回の SQL 実行あたりの CPU 時間

    -- 論理 I/O
    LOGICAL_READS_PER_SESSION UNLIMITED   -- セッションあたりの論理読み取りブロック数
    LOGICAL_READS_PER_CALL    UNLIMITED   -- 1回の SQL あたりの論理読み取りブロック数

    -- プライベートメモリ（Dedicated Server のみ）
    PRIVATE_SGA             UNLIMITED    -- セッションが使用できる SGA のプライベート領域

    -- パスワード設定も合わせて指定可能
    PASSWORD_LIFETIME       90
    FAILED_LOGIN_ATTEMPTS   5
    PASSWORD_LOCK_TIME      1/24;

-- ユーザー作成時にプロファイルを指定する
CREATE USER app_user IDENTIFIED BY "Pass123#"
DEFAULT TABLESPACE app_data
PROFILE app_user_profile;    -- 作成したプロファイルを割り当て

-- 既存ユーザーのプロファイルを変更する
ALTER USER app_user PROFILE app_user_profile;
ALTER USER app_user PROFILE DEFAULT;   -- DEFAULT プロファイルに戻す

-- ユーザーに割り当てられているプロファイルを確認する
SELECT username, profile, account_status, expiry_date
FROM DBA_USERS
WHERE username IN ('APP_USER', 'REPORT_USER')
ORDER BY username;

-- プロファイルの設定内容を確認する
SELECT profile, resource_name, limit
FROM DBA_PROFILES
WHERE profile = 'APP_USER_PROFILE'
ORDER BY resource_name;
-- limit: 設定値（UNLIMITED / DEFAULT / 数値）
-- DEFAULT は DEFAULT プロファイルの設定値を継承する

-- DEFAULT プロファイルの現在の設定を確認する
SELECT resource_name, limit
FROM DBA_PROFILES
WHERE profile = 'DEFAULT'
  AND resource_name IN (
      'PASSWORD_LIFETIME', 'PASSWORD_GRACE_TIME',
      'FAILED_LOGIN_ATTEMPTS', 'PASSWORD_LOCK_TIME'
  )
ORDER BY resource_name;

-- プロファイルを使用しているユーザーを確認する
SELECT username, account_status, expiry_date
FROM DBA_USERS
WHERE profile = 'APP_USER_PROFILE'
ORDER BY username;

-- プロファイルを削除する（CASCADE で使用中ユーザーを DEFAULT に戻す）
DROP PROFILE app_user_profile;          -- 使用中ユーザーがいる場合はエラー
DROP PROFILE app_user_profile CASCADE;  -- 使用中ユーザーを DEFAULT プロファイルに変更して削除