Oracle密碼過期與Profile策略實戰：關閉驗證、自定義Profile與用戶綁定

業務痛點

測試/開發環境頻繁報ORA-28003: password verification failed、ORA-28001: password has expired，干擾開發效率。生產環境**嚴禁**關閉密碼策略，本文僅針對非生產環境。

核心視圖：DBA_PROFILES

-- 查看所有Profile資源限制
SELECT profile, resource_name, resource_type, limit
FROM dba_profiles
ORDER BY profile, resource_type, resource_name;

-- 專注密碼參數
SELECT profile, resource_name, limit
FROM dba_profiles
WHERE resource_type = 'PASSWORD'
ORDER BY profile, resource_name;

默認Profile關鍵密碼參數

方案一：關閉DEFAULT Profile密碼驗證（全局生效，慎用）

-- 1. 取消密碼複雜度驗證
ALTER PROFILE DEFAULT LIMIT PASSWORD_VERIFY_FUNCTION NULL;

-- 2. 設定密碼永不過期
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

-- 3. 允許重用相同密碼
ALTER PROFILE DEFAULT LIMIT PASSWORD_REUSE_TIME UNLIMITED;
ALTER PROFILE DEFAULT LIMIT PASSWORD_REUSE_MAX UNLIMITED;

-- 4. 取消登錄失敗鎖定
ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED;
ALTER PROFILE DEFAULT LIMIT PASSWORD_LOCK_TIME UNLIMITED;

方案二：創建專用寬鬆Profile（推薦，隔離影響）

CREATE PROFILE dev_relaxed LIMIT
  SESSIONS_PER_USER UNLIMITED
  CPU_PER_SESSION UNLIMITED
  CPU_PER_CALL 3000
  CONNECT_TIME 480
  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;

-- 綁定用戶
ALTER USER scott PROFILE dev_relaxed;
ALTER USER app_dev PROFILE dev_relaxed;

方案三：生產環境嚴格Profile範例

CREATE PROFILE prod_strict LIMIT
  FAILED_LOGIN_ATTEMPTS 5
  PASSWORD_LIFE_TIME 90
  PASSWORD_REUSE_TIME 365
  PASSWORD_REUSE_MAX 10
  PASSWORD_VERIFY_FUNCTION VERIFY_FUNCTION_11G  -- 或自定義函數
  PASSWORD_LOCK_TIME 1
  PASSWORD_GRACE_TIME 3;

-- 綁定生產用戶
ALTER USER prod_app PROFILE prod_strict;

自定義驗證函數模板（11g+）

CREATE OR REPLACE FUNCTION custom_verify_function
  (username VARCHAR2, password VARCHAR2, old_password VARCHAR2)
RETURN BOOLEAN IS
BEGIN
  -- 1. 長度≥10
  IF LENGTH(password) < 10 THEN
    RAISE_APPLICATION_ERROR(-20001, 'Password too short (min 10)');
  END IF;
  -- 2. 包含大小寫/數字/特殊字符
  IF NOT REGEXP_LIKE(password, '[A-Z]') OR
     NOT REGEXP_LIKE(password, '[a-z]') OR
     NOT REGEXP_LIKE(password, '[0-9]') OR
     NOT REGEXP_LIKE(password, '[^A-Za-z0-9]') THEN
    RAISE_APPLICATION_ERROR(-20002, 'Password must contain upper, lower, digit, special');
  END IF;
  -- 3. 不包含用戶名
  IF INSTR(LOWER(password), LOWER(username)) > 0 THEN
    RAISE_APPLICATION_ERROR(-20003, 'Password cannot contain username');
  END IF;
  -- 4. 與舊密碼差異度（簡化）
  IF old_password IS NOT NULL AND password = old_password THEN
    RAISE_APPLICATION_ERROR(-20004, 'Password same as old');
  END IF;
  RETURN TRUE;
END;
/

-- 綁定
ALTER PROFILE prod_strict LIMIT PASSWORD_VERIFY_FUNCTION CUSTOM_VERIFY_FUNCTION;

驗證與巡檢

-- 用戶當前Profile
SELECT username, profile FROM dba_users WHERE username = 'APP_USER';

-- Profile參數生效確認
SELECT resource_name, limit FROM dba_profiles
WHERE profile = 'DEV_RELAXED' AND resource_type = 'PASSWORD';

總結

Profile是Oracle密碼/資源策略的「容器」。非生產環境採用專用寬鬆Profile綁定，生產環境採用嚴格Profile+自定義驗證函數。**切記：生產環境絕不關閉密碼策略**，合規審計的紅線。