您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫基礎

Oracle ALTER USER 完整語法指南：密碼變更、表空間調整、帳號狀態、預設角色管理

編輯：Oracle 資料庫基礎

標準語法圖

ALTER USER username
  [IDENTIFIED {BY password | EXTERNALLY | GLOBALLY AS 'DN'}]
  [DEFAULT TABLESPACE tablespace]
  [TEMPORARY TABLESPACE tablespace]
  [QUOTA {size [K|M|G|T] | UNLIMITED} ON tablespace [, ...]]
  [PROFILE profile_name]
  [PASSWORD EXPIRE]
  [ACCOUNT {LOCK | UNLOCK}]
  [DEFAULT ROLE {role [, role]... | ALL [EXCEPT role [, role]...] | NONE}]
  [ENABLE EDITIONS];  -- 12c+

參數對照：CREATE USER vs ALTER USER

子句	CREATE USER	ALTER USER	備註
IDENTIFIED BY/EXTERNALLY/GLOBALLY	必要	選項	ALTER 可變更認證方式
DEFAULT TABLESPACE	選項	選項	僅影響後續建立物件，不移動現有物件
TEMPORARY TABLESPACE	選項	選項	立即生效
QUOTA	選項	選項	可增加/減少/移除配額
PROFILE	選項	選項	立即生效，密碼策略下次登入生效
PASSWORD EXPIRE	選項	選項	強制下次登入改密碼
ACCOUNT LOCK/UNLOCK	選項	選項	即時生效
DEFAULT ROLE	無	ALTER 專有	控制登入時自動啟用的角色
ENABLE EDITIONS	12c+ 選項	12c+ 選項	不可逆，啟用後無法關閉

核心操作實戰範例

1. 變更密碼（最常用）

-- 管理員為他人變更
ALTER USER app_user IDENTIFIED BY "NewStr0ngP@ssw0rd!";

-- 使用者自行變更 (需舊密碼)
-- SQL> PASSWORD
-- 或
ALTER USER app_user IDENTIFIED BY "NewPass" REPLACE "OldPass";  -- 11g+

2. 帳號鎖定/解鎖

-- 安全事件：緊急鎖定
ALTER USER compromised_user ACCOUNT LOCK;

-- 維護完成：解鎖
ALTER USER maintenance_user ACCOUNT UNLOCK;

3. 強制密碼過期（下次登入必改）

ALTER USER app_user PASSWORD EXPIRE;
-- 用戶登入時會提示：
-- ERROR: ORA-28001: the password has expired
-- 輸入新密碼後方可進入

4. 調整表空間配額

-- 增加配額
ALTER USER app_user QUOTA 1G ON app_data;

-- 移除配額 (無法再建立物件，現有物件不受影響)
ALTER USER app_user QUOTA 0 ON old_data;

-- 無限制
ALTER USER app_user QUOTA UNLIMITED ON app_index;

5. 變更預設表空間

ALTER USER app_user DEFAULT TABLESPACE app_data_new;
-- 注意：現有表/索引仍留在舊表空間，需 ALTER TABLE ... MOVE 遷移

6. 變更暫存表空間

ALTER USER app_user TEMPORARY TABLESPACE temp_new;
-- 立即生效，進行中的排序操作可能失敗

7. 變更 Profile（密碼/資源策略）

ALTER USER app_user PROFILE strict_profile;
-- 密碼策略：下次登入/改密碼時生效
-- 資源限制：下次 Session 生效

8. 管理預設角色（ALTER USER 獨有）

-- 登入自動啟用特定角色
ALTER USER app_user DEFAULT ROLE app_read_role, app_write_role;

-- 登入自動啟用所有授予角色，排除特定
ALTER USER dba_user DEFAULT ROLE ALL EXCEPT dba_audit_role;

-- 登入不自動啟用任何角色 (需 SET ROLE 手動啟用)
ALTER USER security_user DEFAULT ROLE NONE;

9. 變更認證方式

-- 資料庫認證 → 外部認證
ALTER USER app_user IDENTIFIED EXTERNALLY;

-- 外部認證 → 資料庫認證 (需設定密碼)
ALTER USER ops$oracle IDENTIFIED BY "NewPass!";

批次管理腳本

批次解鎖所有被鎖定帳號（排除系統帳號）

BEGIN
  FOR u IN (SELECT username FROM dba_users 
            WHERE account_status LIKE 'LOCKED%'
            AND username NOT IN ('SYS','SYSTEM','SYSMAN','DBSNMP','OUTLN','CTXSYS','MDSYS')) LOOP
    EXECUTE IMMEDIATE 'ALTER USER ' || u.username || ' ACCOUNT UNLOCK';
    DBMS_OUTPUT.PUT_LINE('Unlocked: ' || u.username);
  END LOOP;
END;
/

批次強制密碼過期（密碼策略變更後）

BEGIN
  FOR u IN (SELECT username FROM dba_users 
            WHERE profile = 'OLD_PROFILE' AND account_status = 'OPEN') LOOP
    EXECUTE IMMEDIATE 'ALTER USER ' || u.username || ' PASSWORD EXPIRE';
    DBMS_OUTPUT.PUT_LINE('Expired: ' || u.username);
  END LOOP;
END;
/

批次調整配額（表空間擴容後）

BEGIN
  FOR u IN (SELECT username FROM dba_users 
            WHERE default_tablespace = 'APP_DATA' AND username NOT LIKE 'SYS%') LOOP
    EXECUTE IMMEDIATE 'ALTER USER ' || u.username || ' QUOTA UNLIMITED ON APP_DATA';
  END LOOP;
END;
/

DEFAULT ROLE 深度解析

角色啟用機制

登入 → 檢查 DEFAULT ROLE 設定
  ├─ ALL (預設) → 啟用所有授予角色 (含後續新增)
  ├─ ALL EXCEPT ... → 啟用除外角色
  ├─ role1, role2 ... → 僅啟用列表角色
  └─ NONE → 不自動啟用，需手動 SET ROLE

Session 中可動態切換：
SET ROLE role1, role2;        -- 啟用
SET ROLE ALL;                 -- 啟用所有
SET ROLE NONE;                -- 全部停用
SET ROLE ALL EXCEPT role1;    -- 排除停用

常見誤區

誤區	正確認知
DEFAULT ROLE 影響權限授予	只影響「登入自動啟用」，不影響 GRANT/REVOKE
GRANT ROLE 後自動生效	需登入時自動啟用 (DEFAULT ROLE ALL) 或手動 SET ROLE
PASSWORD EXPIRE 立即失效	下次登入才要求變更，當前 Session 不受影響
QUOTA 0 會刪除現有物件	只限制新建物件，現有物件保留
ALTER USER 需要 ALTER ANY USER 權限	自己修改密碼/預設角色只需 CREATE SESSION；他人需 ALTER USER 系統權限

查詢變更歷史（審計）

-- 需啟用審計: AUDIT ALTER USER BY ACCESS
SELECT timestamp, os_username, username, action_name, return_code, obj_name
FROM dba_audit_trail
WHERE action_name = 'ALTER USER'
ORDER BY timestamp DESC;