【Oracle】ORA-28000 完全ガイド｜the account is locked（アカウントロック）の原因・解除・監視・防止まで解説

-- DBA_USERS でアカウントの状態を確認する（DBA権限が必要）
SELECT
    username,
    account_status,        -- OPEN / LOCKED / EXPIRED / EXPIRED & LOCKED / LOCKED(TIMED)
    lock_date,             -- ロックされた日時（手動ロックまたは自動ロック）
    expiry_date,           -- パスワードの有効期限日
    failed_logins,         -- 連続ログイン失敗回数（Oracle 12c 以降）
    profile                -- 適用されているプロファイル名
FROM DBA_USERS
WHERE username = 'APP_USER';

-- ACCOUNT_STATUS の値:
-- OPEN                  : 正常（ログイン可能）
-- LOCKED                : 手動でロックされた（ALTER USER ... ACCOUNT LOCK）
-- LOCKED(TIMED)         : FAILED_LOGIN_ATTEMPTS 超過で自動ロックされた
-- EXPIRED               : パスワードが有効期限切れ（次回ログイン時に変更を求められる）
-- EXPIRED & LOCKED      : 期限切れかつロック
-- EXPIRED(GRACE)        : 猶予期間中

-- V$LOCKED_USERS: 現在ロックされているユーザーの一覧（DBA権限）
SELECT
    user_name,
    account_status,
    lock_date
FROM V$LOCKED_USERS;

-- SYS.USER_HISTORY$ でログイン失敗の履歴を確認する（DBA権限）
-- （Unified Auditing を使っていない環境）
SELECT
    u.name          AS username,
    h.password_date AS failed_time,
    h.password      AS ('hashed_value_at_failure')   -- 参考情報
FROM SYS.USER_HISTORY$ h
JOIN SYS.USER$ u ON h.user# = u.user#
WHERE u.name = 'APP_USER'
ORDER BY h.password_date DESC;

-- Unified Auditing 環境での失敗ログイン確認（Oracle 12c 以降）
SELECT
    event_timestamp,
    db_username,
    authentication_type,
    return_code           -- 28000 がログイン失敗によるロック
FROM UNIFIED_AUDIT_TRAIL
WHERE db_username = 'APP_USER'
  AND return_code IN (1017, 28000)   -- 1017: パスワード誤り, 28000: ロック
ORDER BY event_timestamp DESC;

-- アカウントのロックを解除する（DBA権限が必要）
ALTER USER app_user ACCOUNT UNLOCK;

-- 解除後にアカウント状態を確認する
SELECT username, account_status, lock_date FROM DBA_USERS WHERE username = 'APP_USER';
-- account_status が OPEN になっていれば解除完了

-- パスワードも期限切れになっている場合は同時にリセットする
ALTER USER app_user IDENTIFIED BY "NewPassword123#" ACCOUNT UNLOCK;
-- ↑ パスワードリセット + ロック解除を同時に行う

-- EXPIRED 状態（有効期限切れ）の場合はパスワードを再設定してからアンロック
ALTER USER app_user IDENTIFIED BY "NewPassword123#";
-- 次回ログイン時に新パスワードで接続できる

-- 有効期限を延長するだけなら（強制変更なし）
ALTER USER app_user ACCOUNT UNLOCK;
ALTER USER app_user PASSWORD EXPIRE;    -- 意図的に期限切れにする場合
-- または期限を再設定する
ALTER PROFILE default LIMIT PASSWORD_LIFE_TIME UNLIMITED;  -- 全ユーザーに影響するため注意

-- 現在のプロファイル設定を確認する
SELECT profile, resource_name, limit
FROM DBA_PROFILES
WHERE resource_name IN (
    'FAILED_LOGIN_ATTEMPTS',   -- ロックまでのログイン失敗回数
    'PASSWORD_LOCK_TIME',      -- 自動ロックが解除されるまでの時間（日単位）
    'PASSWORD_LIFE_TIME',      -- パスワードの有効期限（日単位）
    'PASSWORD_GRACE_TIME'      -- 期限切れ後の猶予期間（日単位）
)
ORDER BY profile, resource_name;

-- DEFAULT プロファイルのデフォルト値:
-- FAILED_LOGIN_ATTEMPTS = 10    （10回失敗でロック）
-- PASSWORD_LOCK_TIME    = 1/1440（1分後に自動解除 ≒ 1/1440日）
-- PASSWORD_LIFE_TIME    = 180   （180日で期限切れ）
-- PASSWORD_GRACE_TIME   = 7     （期限切れ後7日間は警告のみ）

-- FAILED_LOGIN_ATTEMPTS を変更する（カスタムプロファイルを使う場合）
CREATE PROFILE app_profile LIMIT
    FAILED_LOGIN_ATTEMPTS 5       -- 5回失敗でロック
    PASSWORD_LOCK_TIME    1/24    -- 1時間後に自動解除（1/24 日）
    PASSWORD_LIFE_TIME    UNLIMITED  -- 有効期限なし（開発・バッチ用途）
    PASSWORD_GRACE_TIME   UNLIMITED;

ALTER USER app_user PROFILE app_profile;

-- DEFAULT プロファイルの設定を変更する（全ユーザーに影響するため注意）
ALTER PROFILE default LIMIT FAILED_LOGIN_ATTEMPTS 10;

-- UNLIMITED に設定するとロックが発生しなくなる（推奨しない）
ALTER PROFILE default LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED;

-- ユーザーに適用されているプロファイルを確認する
SELECT username, profile FROM DBA_USERS WHERE username = 'APP_USER';

-- 失敗回数が閾値に近いアカウントを検出するクエリ
-- （Oracle 12c 以降: DBA_USERS.FAILED_LOGINS が利用可能）
SELECT
    u.username,
    u.failed_logins                                AS current_failures,
    p.limit                                        AS max_allowed,
    ROUND(u.failed_logins / TO_NUMBER(p.limit) * 100) AS pct_used,
    u.account_status
FROM DBA_USERS u
JOIN DBA_PROFILES p
    ON u.profile = p.profile
    AND p.resource_name = 'FAILED_LOGIN_ATTEMPTS'
    AND p.limit NOT IN ('UNLIMITED', 'DEFAULT')
WHERE u.account_status = 'OPEN'
  AND u.failed_logins >= TO_NUMBER(p.limit) - 2   -- 残り2回以内になったら検出
ORDER BY pct_used DESC;

-- 現在ロック中のアカウントを一覧する
SELECT username, account_status, lock_date
FROM DBA_USERS
WHERE account_status LIKE '%LOCKED%'
ORDER BY lock_date DESC;

-- DBMS_SCHEDULER でロック監視ジョブを定期実行する
-- （ロックが発生したら DBA_USERS.ACCOUNT_STATUS が変わる）
BEGIN
    DBMS_SCHEDULER.CREATE_JOB(
        job_name        => 'MONITOR_LOCKED_ACCOUNTS',
        job_type        => 'PLSQL_BLOCK',
        job_action      => q'[
DECLARE
    v_count NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_count
    FROM DBA_USERS
    WHERE account_status LIKE '%LOCKED%'
      AND lock_date >= SYSDATE - 1/24;  -- 直近1時間のロック
    IF v_count > 0 THEN
        -- 通知処理（DBMS_ALERT や UTL_MAIL でアラートを送る）
        DBMS_OUTPUT.PUT_LINE(v_count || ' account(s) locked in last 1 hour');
    END IF;
END;]',
        start_date      => SYSTIMESTAMP,
        repeat_interval => 'FREQ=MINUTELY;INTERVAL=15',   -- 15分ごとに実行
        enabled         => TRUE
    );
END;
/