Oracle用戶密碼過期與驗證失敗綜合解決方案

現象診斷

ALTER USER lh IDENTIFIED BY lh;
ORA-28003: password verification failed
ORA-20001: Password same as or similar to user

提示：密碼驗證函數PASSWORD_VERIFY_FUNCTION攔截，判定新密碼與用戶名過於相似或不符合複雜度。

快速通道：放寬DEFAULT PROFILE限制

-- 1. 查看當前密碼相關限制
SELECT resource_name, limit
FROM dba_profiles
WHERE profile = 'DEFAULT' AND resource_type = 'PASSWORD';

-- 2. 一鍵解除所有密碼複雜度/過期/重用限制（僅測試環境！）
ALTER PROFILE DEFAULT LIMIT
  FAILED_LOGIN_ATTEMPTS UNLIMITED
  PASSWORD_LIFE_TIME UNLIMITED
  PASSWORD_REUSE_TIME UNLIMITED
  PASSWORD_REUSE_MAX UNLIMITED
  PASSWORD_VERIFY_FUNCTION NULL
  PASSWORD_LOCK_TIME UNLIMITED
  PASSWORD_GRACE_TIME UNLIMITED;

-- 3. 即可任意設置密碼
ALTER USER lh IDENTIFIED BY lh;

生產環境規範做法：創建專用寬鬆Profile

-- 1. 創建測試/開發專用Profile
CREATE PROFILE dev_test LIMIT
  SESSIONS_PER_USER UNLIMITED
  CPU_PER_SESSION UNLIMITED
  CPU_PER_CALL 3000
  CONNECT_TIME 45
  LOGICAL_READS_PER_SESSION DEFAULT
  LOGICAL_READS_PER_CALL 1000
  PRIVATE_SGA 15K
  COMPOSITE_LIMIT 5000000
  -- 密碼策略極度寬鬆
  FAILED_LOGIN_ATTEMPTS UNLIMITED
  PASSWORD_LIFE_TIME UNLIMITED
  PASSWORD_REUSE_TIME UNLIMITED
  PASSWORD_REUSE_MAX UNLIMITED
  PASSWORD_VERIFY_FUNCTION NULL
  PASSWORD_LOCK_TIME UNLIMITED
  PASSWORD_GRACE_TIME UNLIMITED;

-- 2. 將目標用戶遷移至新Profile
ALTER USER lh PROFILE dev_test;

-- 3. 修改密碼即可生效
ALTER USER lh IDENTIFIED BY lh;

進階：自定義密碼驗證函數（生產必備）

-- 示例：強制長度≥10、含大小寫數字特殊字符、不含用戶名
CREATE OR REPLACE FUNCTION strict_pwd_verify(
  username VARCHAR2,
  password VARCHAR2,
  old_password VARCHAR2
) RETURN BOOLEAN AS
  v_len   NUMBER;
  v_has_upper   BOOLEAN := FALSE;
  v_has_lower   BOOLEAN := FALSE;
  v_has_digit   BOOLEAN := FALSE;
  v_has_special BOOLEAN := FALSE;
BEGIN
  v_len := LENGTH(password);
  IF v_len < 10 THEN
    RAISE_APPLICATION_ERROR(-20001, 'Password must be at least 10 characters');
  END IF;
  FOR i IN 1..v_len LOOP
    IF SUBSTR(password,i,1) BETWEEN 'A' AND 'Z' THEN v_has_upper := TRUE;
    ELSIF SUBSTR(password,i,1) BETWEEN 'a' AND 'z' THEN v_has_lower := TRUE;
    ELSIF SUBSTR(password,i,1) BETWEEN '0' AND '9' THEN v_has_digit := TRUE;
    ELSE v_has_special := TRUE;
    END IF;
  END LOOP;
  IF NOT (v_has_upper AND v_has_lower AND v_has_digit AND v_has_special) THEN
    RAISE_APPLICATION_ERROR(-20002, 'Password must contain upper, lower, digit, special');
  END IF;
  IF INSTR(LOWER(password), LOWER(username)) > 0 THEN
    RAISE_APPLICATION_ERROR(-20003, 'Password cannot contain username');
  END IF;
  RETURN TRUE;
END;
/

-- 綁定到生產Profile
ALTER PROFILE production LIMIT PASSWORD_VERIFY_FUNCTION strict_pwd_verify;

密碼過期後的強制修改流程

-- 用戶登錄時提示ORA-28001: the password has expired
-- 必須在同一會話中完成修改
CONN lh/old_pass
-- 提示：ERROR: ORA-28001: the password has expired
-- Changing password for lh
New password: NewPass#123
Retype new password: NewPass#123
-- Password changed
-- Connected.

策略建議：
• 生產/核心庫：嚴格Profile + 自定義驗證函數 + 定期審計DBA_USERS.PASSWORD_VERSIONS確認11g/12c雜湊。
• 開發/測試庫：專用dev_test Profile，密碼永不過期、無複雜度、無鎖定。
• 遷移用戶Profile時，現有會話不受影響，新連接即刻生效新策略。
• 12c+引入PASSWORD_ROLLOVER_TIME平滑輪換密碼，避免應用中斷。