Oracle 用戶授權與管理全攻略：從創建到角色體系的最佳實踐

權限體系雙軌制：系統權限 vs 對象權限

Oracle 權限模型採雙軌並行：

• 系統權限：授予用戶執行特定**數據庫操作**的能力 (如 `CREATE TABLE`, `CREATE SESSION`, `ALTER SYSTEM`)，**作用於整個數據庫實例**，由 DBA (SYS/SYSTEM 或具備 `GRANT ANY PRIVILEGE` 用戶) 授予。
• 對象權限：授予用戶對特定**模式對象** (表、視圖、序列、過程、函數、包、類型、物化視圖、目錄等) 的操作權限 (如 `SELECT`, `INSERT`, `UPDATE`, `DELETE`, `EXECUTE`, `REFERENCES`, `INDEX`, `ALTER`)，**作用於特定對象**，由對象擁有者或具備 `GRANT ANY OBJECT PRIVILEGE` 用戶授予。

用戶生命週期管理

1. 創建用戶 — 現代最佳實踐 (12c+ 推薦)

-- 僅創建用戶，密碼複雜度由 Profile 控制
CREATE USER app_user IDENTIFIED BY "Str0ngP@ssw0rd!"
    DEFAULT TABLESPACE app_data
    TEMPORARY TABLESPACE temp
    QUOTA UNLIMITED ON app_data
    QUOTA 100M ON app_index
    PROFILE app_profile
    ACCOUNT UNLOCK;

-- 12c+ 專用：創建僅供應用使用的「無密碼」用戶 (僅通過代理認證/外部認證登錄)
CREATE USER app_proxy_user IDENTIFIED GLOBALLY AS 'CN=app_proxy,OU=DB,O=ORACLE';
-- 或
CREATE USER app_ext_user IDENTIFIED EXTERNALLY; -- OS/Kerberos/RADIUS 認證

關鍵參數解析：

• `DEFAULT TABLESPACE`：對象創建默認表空間，**禁止使用 SYSTEM/SYSAUX**。
• `TEMPORARY TABLESPACE`：排序/哈希/全局臨時表默認臨時表空間。
• `QUOTA`：限制用戶在表空間佔用上限，**生產環境嚴禁 UNLIMITED ON SYSTEM/SYSAUX**，防止誤操作撐爆系統表空間。
• `PROFILE`：綁定密碼策略 (生命週期、複雜度、鎖定/失敗次數)、資源限制 (CPU/邏輯讀/會話/連接時間)。
• `ACCOUNT UNLOCK | LOCK`：創建即解鎖/鎖定。

2. 密碼管理 — 安全與運維平衡

-- 修改密碼 (用戶自助)
ALTER USER app_user IDENTIFIED BY "NewStr0ngP@ss!";
-- 或使用 PASSWORD 命令 (SQL*Plus/SQLcl/SQL Developer 隱式輸入，更安全)
PASSWORD  -- 修改當前用戶密碼
PASSWORD app_user  -- DBA 修改他人密碼

-- 密碼驗證函數 (Profile 關聯)
-- 12c+ 內置 ORA12C_VERIFY_FUNCTION / ORA12C_STRONG_VERIFY_FUNCTION
-- 自定義驗證函數示例 (PL/SQL)
CREATE OR REPLACE FUNCTION my_verify_fn (
    username VARCHAR2, password VARCHAR2, old_password VARCHAR2
) RETURN BOOLEAN IS
BEGIN
    -- 長度、複雜度、歷史、字典詞檢查邏輯
    RETURN TRUE; -- 通過
END;
/
-- 關聯到 Profile
ALTER PROFILE app_profile LIMIT PASSWORD_VERIFY_FUNCTION my_verify_fn;

3. 刪除用戶 — 級聯清理與風險控制

-- 僅刪用戶 (若擁有對象報錯 ORA-01922)
DROP USER app_user;

-- 級聯刪除用戶及其所有對象 (表、索引、視圖、過程、類型、序列、同義詞、DB Link、Job 等)
DROP USER app_user CASCADE;

-- 生產環境謹慎操作建議：
-- 1. 先鎖定用戶防止新連接
ALTER USER app_user ACCOUNT LOCK;
-- 2. 殺掉現有會話
BEGIN
    FOR s IN (SELECT sid, serial# FROM v$session WHERE username = 'APP_USER') LOOP
        EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION ''' || s.sid || ',' || s.serial# || ''' IMMEDIATE';
    END LOOP;
END;
/
-- 3. 導出對象 DDL (DBMS_METADATA) 備份
-- 4. 確認無業務依賴後執行 DROP USER ... CASCADE

三大預定義角色 — 歷史兼容與現代定位

自定義角色體系設計 — 企業級最佳實踐

角色分層架構

-- 1. 功能角色 (應用業務邏輯)
CREATE ROLE app_read_only;
GRANT SELECT ON schema_core.customers TO app_read_only;
GRANT SELECT ON schema_core.orders TO app_read_only;
GRANT EXECUTE ON schema_core.pkg_report TO app_read_only;

CREATE ROLE app_read_write;
GRANT app_read_only TO app_read_write;  -- 角色嵌套
GRANT INSERT, UPDATE, DELETE ON schema_core.orders TO app_read_write;
GRANT INSERT, UPDATE ON schema_core.customers TO app_read_write;
GRANT EXECUTE ON schema_core.pkg_order_mgmt TO app_read_write;

CREATE ROLE app_admin;
GRANT app_read_write TO app_admin;
GRANT CREATE TABLE, CREATE VIEW, CREATE PROCEDURE TO app_admin; -- 系統權限
GRANT ALTER ANY TABLE, DROP ANY TABLE TO app_admin; -- 高危，極度謹慎

-- 2. 基礎設施角色 (跨應用通用)
CREATE ROLE dev_read_only;
GRANT SELECT_CATALOG_ROLE TO dev_read_only; -- 查詢數據字典
GRANT SELECT ON v_$session TO dev_read_only; -- 動態視圖需額外授予

CREATE ROLE monitoring_user;
GRANT SELECT ON v_$session TO monitoring_user;
GRANT SELECT ON v_$sql TO monitoring_user;
GRANT SELECT ON v_$active_session_history TO monitoring_user;
GRANT SELECT ON dba_hist_active_sess_history TO monitoring_user;

-- 3. 安全分離角色 (職責分離)
CREATE ROLE security_admin;
GRANT CREATE USER, ALTER USER, DROP USER TO security_admin;
GRANT GRANT ANY ROLE, GRANT ANY PRIVILEGE TO security_admin;
GRANT AUDIT_ADMIN, AUDIT_VIEWER TO security_admin;

CREATE ROLE backup_admin;
GRANT SYSBACKUP TO backup_admin; -- 12c+ 專用備份恢復角色

角色授予策略

• 用戶授予角色：`GRANT app_read_write TO app_user;` — 標準方式。
• 角色授予角色：`GRANT app_read_only TO app_read_write;` — 實現權限繼承，簡化維護。
• WITH ADMIN OPTION：`GRANT app_admin TO super_user WITH ADMIN OPTION;` — 允許受授者再授予/撤銷該角色，**極度謹慎**，僅限安全管理員。
• DEFAULT ROLE：`ALTER USER app_user DEFAULT ROLE app_read_write, monitoring_user;` — 登錄自動啟用，**禁止** `DEFAULT ROLE ALL` (含 DBA 風險)。
• 安全應用角色 (Secure Application Role)：`CREATE ROLE app_dynamic_role IDENTIFIED USING pkg_auth.check_access;` — 通過 PL/SQL 包邏輯動態啟用 (如時間、IP、上下文驗證)，**防止角色被盜用**。

權限傳遞與撤銷機制深度解析

關鍵數據字典視圖 — 權限審計與排查利器

常見權限問題排查速查表

1. ORA-01031: insufficient privileges → 缺少系統權限。查 `DBA_SYS_PRIVS` / `SESSION_PRIVS` 確認，DBA 授予缺失權限。
2. ORA-00942: table or view does not exist → 非對象擁有者且無對象權限/同義詞。查 `DBA_TAB_PRIVS` 確認 `SELECT/INSERT/UPDATE/DELETE`，或創建公共/私有同義詞 `CREATE SYNONYM tab FOR schema.tab;`。
3. ORA-01045: user lacks CREATE SESSION privilege → 用戶無 `CONNECT` 役或 `CREATE SESSION` 系統權限。`GRANT CREATE SESSION TO user;`。
4. ORA-01950: no privileges on tablespace 'XXX' → 用戶在表空間無配額。`ALTER USER user QUOTA 100M ON XXX;` 或 `QUOTA UNLIMITED`。
5. 存儲過程/函數/包/觸發器編譯報權限錯誤 → PL/SQL 靜態 SQL 執行需**直接授予對象權限**，**角色權限在定義者權限模塊中不生效** (除非 `AUTHID CURRENT_USER`)。解決：`GRANT SELECT ON schema.tab TO proc_owner;` (不可授予角色)。
6. DBA 角色用戶無法在存儲過程中訪問他人表 → 同原因，DBA 角色權限在定義者權限 PL/SQL 中失效。需顯式授予對象權限給存儲過程擁有者。
7. 密碼過期/鎖定導致登錄失敗 → `ORA-28001/ORA-28000`。查 `DBA_USERS.ACCOUNT_STATUS`，`ALTER USER user ACCOUNT UNLOCK; ALTER USER user IDENTIFIED BY new_pass;`。