【Oracle】ユーザーアカウントのロック解除手順｜ACCOUNT_STATUS 別対処・原因調査・EXPIRED 復旧・再発防止まで解説

-- ユーザーのアカウント状態を確認
SELECT username, account_status, lock_date, expiry_date, profile
FROM dba_users
WHERE username NOT IN ('SYS','SYSTEM','ANONYMOUS','XDB','DBSNMP','AUDSYS')
ORDER BY account_status, username;

-- DBA または SYS で接続
-- sqlplus / as sysdba

-- アカウントをアンロック
ALTER USER hr ACCOUNT UNLOCK;

-- 確認
SELECT username, account_status FROM dba_users WHERE username = 'HR';
-- account_status = OPEN であれば解除完了

-- パスワードの再設定（EXPIRED 解除）
ALTER USER hr IDENTIFIED BY new_password;

-- EXPIRED & LOCKED の場合はアンロックも同時に
ALTER USER hr IDENTIFIED BY new_password ACCOUNT UNLOCK;

-- 確認
SELECT username, account_status, expiry_date FROM dba_users WHERE username = 'HR';

-- PASSWORD_LOCK_TIME の設定を確認
SELECT resource_name, limit FROM dba_profiles
WHERE profile = (SELECT profile FROM dba_users WHERE username = 'HR')
  AND resource_name = 'PASSWORD_LOCK_TIME';
-- 例: 1（1 日後に自動解除）/ 1/24（1 時間後）

-- 手動で即座に解除したい場合
ALTER USER hr ACCOUNT UNLOCK;

-- LOCK_DATE でいつロックされたか確認
SELECT username, account_status, lock_date, expiry_date
FROM dba_users
WHERE account_status LIKE '%LOCKED%'
ORDER BY lock_date DESC;

-- 統合監査（12c 以降）
SELECT dbusername, os_username, userhost,
       event_timestamp, return_code
FROM unified_audit_trail
WHERE action_name = 'LOGON'
  AND return_code <> 0  -- 0 以外 = 失敗
  AND dbusername = 'HR'
ORDER BY event_timestamp DESC
FETCH FIRST 20 ROWS ONLY;

-- return_code の意味:
-- 1017 = パスワード不正
-- 28000 = アカウントロック
-- 28001 = パスワード期限切れ

-- 伝統的監査
SELECT username, os_username, userhost,
       timestamp, returncode
FROM dba_audit_trail
WHERE action_name = 'LOGON'
  AND returncode IN (1017, 28000)
  AND username = 'HR'
ORDER BY timestamp DESC;

-- IP アドレス別のログイン失敗回数（不正アクセスの調査）
SELECT userhost, dbusername,
       COUNT(*) AS fail_count,
       MAX(event_timestamp) AS last_attempt
FROM unified_audit_trail
WHERE action_name = 'LOGON'
  AND return_code = 1017  -- パスワード不正
  AND event_timestamp >= SYSDATE - 7
GROUP BY userhost, dbusername
ORDER BY fail_count DESC;

-- ロック中のユーザーを一括アンロック
BEGIN
    FOR rec IN (
        SELECT username FROM dba_users
        WHERE account_status LIKE '%LOCKED%'
          AND username NOT IN ('SYS','SYSTEM','ANONYMOUS','XDB','DBSNMP','AUDSYS')
    ) LOOP
        EXECUTE IMMEDIATE 'ALTER USER ' || rec.username || ' ACCOUNT UNLOCK';
        DBMS_OUTPUT.PUT_LINE('Unlocked: ' || rec.username);
    END LOOP;
END;
/

-- EXPIRED 状態のユーザー一覧（パスワード再設定が必要）
SELECT username, account_status, expiry_date
FROM dba_users
WHERE account_status LIKE '%EXPIRED%'
  AND username NOT IN ('SYS','SYSTEM','ANONYMOUS','XDB')
ORDER BY expiry_date;

-- 個別にパスワードを再設定 + アンロック
ALTER USER hr IDENTIFIED BY temp_password ACCOUNT UNLOCK;
ALTER USER app_user IDENTIFIED BY temp_password ACCOUNT UNLOCK;

-- ※ パスワードはセキュリティ上、一括自動生成できないため個別設定が必要

-- 現在の設定を確認
SELECT profile, resource_name, limit
FROM dba_profiles
WHERE resource_name IN ('FAILED_LOGIN_ATTEMPTS', 'PASSWORD_LOCK_TIME')
ORDER BY profile;

-- デフォルト: FAILED_LOGIN_ATTEMPTS=10, PASSWORD_LOCK_TIME=1（日）

-- ロック時間を 30 分に短縮（頻繁にロックされる環境向け）
ALTER PROFILE DEFAULT LIMIT
    FAILED_LOGIN_ATTEMPTS 10
    PASSWORD_LOCK_TIME 1/48;  -- 1/48 日 = 30 分

-- 開発環境: ロックを無効化
ALTER PROFILE dev_profile LIMIT
    FAILED_LOGIN_ATTEMPTS UNLIMITED;

-- アプリ接続ユーザーはロック閾値を高めに設定
CREATE PROFILE app_connection LIMIT
    FAILED_LOGIN_ATTEMPTS 20   -- 接続プールのリトライに耐える
    PASSWORD_LOCK_TIME 1/96    -- 15 分で自動解除
    PASSWORD_LIFE_TIME UNLIMITED;  -- アプリ用は期限なし

ALTER USER app_user PROFILE app_connection;

-- (1) 状態確認
SELECT username, account_status, lock_date FROM dba_users WHERE username = 'APP_USER';
-- LOCKED(TIMED) / lock_date = 2026-03-30 08:15

-- (2) アンロック
ALTER USER app_user ACCOUNT UNLOCK;

-- (3) 原因調査: 監査ログでログイン失敗を確認
SELECT userhost, COUNT(*) FROM unified_audit_trail
WHERE dbusername = 'APP_USER' AND return_code = 1017
  AND event_timestamp >= SYSDATE - 1
GROUP BY userhost;

-- (4) アプリの接続設定（パスワード）を確認・修正

-- EXPIRED & LOCKED の場合
ALTER USER hr IDENTIFIED BY NewSecurePass123 ACCOUNT UNLOCK;

-- 確認
SELECT account_status, expiry_date FROM dba_users WHERE username = 'HR';
-- OPEN / expiry_date = 180 日後

-- ロック中のユーザー一覧
SELECT username, account_status, lock_date, profile
FROM dba_users
WHERE account_status LIKE '%LOCKED%'
  AND username NOT IN ('SYS','SYSTEM')
ORDER BY lock_date;

-- 開発環境: 一括アンロック
BEGIN
    FOR rec IN (
        SELECT username FROM dba_users
        WHERE account_status LIKE '%LOCKED%'
          AND username NOT IN ('SYS','SYSTEM','ANONYMOUS','XDB')
    ) LOOP
        EXECUTE IMMEDIATE 'ALTER USER ' || rec.username || ' ACCOUNT UNLOCK';
    END LOOP;
END;
/

-- LOCKED 状態のユーザーを検知（日次バッチで実行）
SELECT username, account_status, lock_date
FROM dba_users
WHERE account_status LIKE '%LOCKED%'
  AND lock_date >= SYSDATE - 1
  AND username NOT IN ('SYS','SYSTEM','ANONYMOUS','XDB');
-- 結果があればアラート通知