Oracle 權限分類與管理體系深度解析：系統權限、對象權限、角色傳遞與審計實戰

權限模型雙軌制：系統權限 vs 對象權限

系統權限三大預定義角色 (歷史兼容層)

系統權限授予/撤銷/傳遞實戰語法

-- 1. 授予系統權限 (DBA 執行)
GRANT CREATE SESSION, CREATE TABLE, CREATE SEQUENCE, CREATE PROCEDURE TO app_dev;
GRANT CREATE ANY TABLE, ALTER ANY TABLE, DROP ANY TABLE TO schema_admin; -- 高危，謹慎

-- 2. 帶傳遞選項授予 (極度謹慎)
GRANT CREATE TABLE TO app_power_user WITH ADMIN OPTION;
-- app_power_user 現在可將 CREATE TABLE 授予他人/角色

-- 3. 撤銷系統權限
REVOKE CREATE ANY TABLE FROM schema_admin;
-- 不級聯：若 schema_admin 已授予他人 CREATE TABLE，他人權限保留

-- 4. 查詢用戶系統權限 (含間接角色權限)
SELECT * FROM dba_sys_privs WHERE grantee = 'APP_DEV';
SELECT * FROM session_privs; -- 當前會話生效權限

對象權限授予/撤銷/傳遞實戰語法

-- 1. 對象擁有者授予
GRANT SELECT, INSERT, UPDATE, DELETE ON schema_core.customers TO app_rw;
GRANT EXECUTE ON schema_core.pkg_order_mgmt TO app_rw;
GRANT REFERENCES (cust_id) ON schema_core.customers TO app_dev; -- 列級 REFERENCES

-- 2. 全部對象權限
GRANT ALL ON schema_core.orders TO app_admin;

-- 3. 帶傳遞選項授予 (對象權限傳遞)
GRANT SELECT ON schema_core.products TO app_power_user WITH GRANT OPTION;
-- app_power_user 可將 SELECT 權限再授予他人

-- 4. 撤銷對象權限 (級聯生效)
REVOKE SELECT ON schema_core.customers FROM app_rw;
-- 若 app_rw 已授予 app_user SELECT，app_user 權限同步失效

-- 5. 查詢對象權限
SELECT * FROM dba_tab_privs WHERE table_name = 'CUSTOMERS' AND owner = 'SCHEMA_CORE';
SELECT * FROM user_tab_privs; -- 當前用戶授予/被授予

企業級角色體系設計 (RBAC 最佳實踐)

三層角色架構

-- 第 1 層：基礎功能角色 (細顆粒度，單一職責)
CREATE ROLE r_read_schema_core;      -- 核心模式只讀
GRANT SELECT ON schema_core.customers TO r_read_schema_core;
GRANT SELECT ON schema_core.orders TO r_read_schema_core;
GRANT SELECT ON schema_core.products TO r_read_schema_core;

CREATE ROLE r_write_schema_core;     -- 核心模式讀寫
GRANT r_read_schema_core TO r_write_schema_core; -- 繼承只讀
GRANT INSERT, UPDATE, DELETE ON schema_core.orders TO r_write_schema_core;
GRANT INSERT, UPDATE ON schema_core.customers TO r_write_schema_core;

CREATE ROLE r_exec_pkg_core;         -- 核心包執行
GRANT EXECUTE ON schema_core.pkg_report TO r_exec_pkg_core;
GRANT EXECUTE ON schema_core.pkg_util TO r_exec_pkg_core;

CREATE ROLE r_dev_tools;             -- 開發工具權限
GRANT CREATE TABLE, CREATE VIEW, CREATE PROCEDURE, CREATE SEQUENCE, CREATE SYNONYM TO r_dev_tools;
GRANT SELECT_CATALOG_ROLE TO r_dev_tools; -- 查詢數據字典

-- 第 2 層：業務應用角色 (組合基礎角色)
CREATE ROLE app_customer_service;    -- 客服應用
GRANT r_read_schema_core TO app_customer_service;
GRANT r_write_schema_core TO app_customer_service;
GRANT r_exec_pkg_core TO app_customer_service;

CREATE ROLE app_order_mgmt;          -- 訂單管理應用
GRANT r_read_schema_core TO app_order_mgmt;
GRANT r_write_schema_core TO app_order_mgmt;
GRANT EXECUTE ON schema_core.pkg_order_mgmt TO app_order_mgmt;

CREATE ROLE app_reporting;           -- 報表/分析應用
GRANT r_read_schema_core TO app_reporting;
GRANT r_exec_pkg_core TO app_reporting;
GRANT CREATE MATERIALIZED VIEW TO app_reporting; -- 特定權限

-- 第 3 層：管理/審計/安全角色 (職責分離)
CREATE ROLE sec_auditor;             -- 安全審計員
GRANT SELECT_CATALOG_ROLE TO sec_auditor;
GRANT AUDIT_VIEWER TO sec_auditor;
GRANT SELECT ON dba_audit_trail TO sec_auditor;
GRANT SELECT ON dba_fga_audit_trail TO sec_auditor;

CREATE ROLE sec_admin;               -- 安全管理員 (高危)
GRANT CREATE USER, ALTER USER, DROP USER TO sec_admin;
GRANT GRANT ANY ROLE, GRANT ANY PRIVILEGE TO sec_admin;
GRANT AUDIT_ADMIN TO sec_admin;

CREATE ROLE ops_backup;              -- 運維備份
GRANT SYSBACKUP TO ops_backup;       -- 12c+ 專用備份角色

CREATE ROLE ops_dg;                  -- 運維 Data Guard
GRANT SYSDG TO ops_dg;               -- 12c+ 專用 DG 角色

安全應用角色 — 動態上下文授權

-- 僅在 PL/SQL 包驗證通過時啟用 (防盜用)
CREATE ROLE app_sensitive_data_access IDENTIFIED USING pkg_security.check_sensitive_access;

-- 包體實現邏輯：驗證 IP、時間、MFA、上下文變量
CREATE OR REPLACE PACKAGE BODY pkg_security AS
    FUNCTION check_sensitive_access RETURN BOOLEAN IS
        v_client_ip VARCHAR2(45) := SYS_CONTEXT('USERENV','IP_ADDRESS');
        v_app_ctx   VARCHAR2(100) := SYS_CONTEXT('APP_CTX','ACCESS_LEVEL');
    BEGIN
        -- 僅允許內網 IP + 高權限上下文 + 工作時間
        IF v_client_ip LIKE '10.1.%' AND v_app_ctx = 'LEVEL_3' 
           AND TO_CHAR(SYSDATE,'HH24') BETWEEN '09' AND '18' THEN
            RETURN TRUE;
        END IF;
        RETURN FALSE;
    END;
END;
/

-- 用戶獲取角色：SET ROLE app_sensitive_data_access; (包自動驗證)
-- 或 GRANT app_sensitive_data_access TO app_user; (登錄時不自動啟用，需顯式 SET ROLE)

用戶授予角色策略

-- 標準授予
GRANT app_customer_service TO user_cs001;
GRANT app_order_mgmt TO user_om001;

-- 設置默認角色 (登錄自動啟用，**禁止 DEFAULT ROLE ALL**)
ALTER USER user_cs001 DEFAULT ROLE app_customer_service, r_dev_tools;
ALTER USER user_om001 DEFAULT ROLE app_order_mgmt;

-- 密碼保護角色 (需密碼啟用，雙因子)
CREATE ROLE app_financial_admin IDENTIFIED BY "Fin@dm1nP@ss";
GRANT app_financial_admin TO user_fin001;
-- 用戶需執行: SET ROLE app_financial_admin IDENTIFIED BY "Fin@dm1nP@ss";

關鍵數據字典視圖 — 權限全景掃描

常見權限故障排查速查表

1. ORA-01031: insufficient privileges → 缺系統權限。查 `SESSION_PRIVS` / `DBA_SYS_PRIVS`，DBA 授予。
2. ORA-00942: table or view does not exist → 非擁有者無對象權限/同義詞。查 `DBA_TAB_PRIVS`，授予 `SELECT/INSERT/UPDATE/DELETE` 或創建同義詞 `CREATE PUBLIC SYNONYM tab FOR schema.tab;`。
3. ORA-01045: user lacks CREATE SESSION privilege → 無 `CREATE SESSION`。`GRANT CREATE SESSION TO user;` 或授予 `CONNECT` 役 (含該權限)。
4. ORA-01950: no privileges on tablespace 'XXX' → 表空間無配額。`ALTER USER user QUOTA 100M ON XXX;` 或 `UNLIMITED`。
5. PL/SQL 編譯/執行報權限錯 (ORA-00942/ORA-01031) → **定義者權限模塊 (默認) 中角色權限不生效**，需直接授予對象權限給模塊擁有者：`GRANT SELECT ON schema.tab TO proc_owner;`。或改用 `AUTHID CURRENT_USER` (調用者權限)。
6. DBA 角色用戶在存儲過程中無法訪問他人表 → 同原因，DBA 角色權限在定義者權限 PL/SQL 中失效，需顯式授予對象權限。
7. REVOKE 後權限仍存在 → 系統權限/角色不級聯，檢查是否被其他授予者授予 (`DBA_SYS_PRIVS` 顯示 GRANTOR)。對象權限級聯，檢查是否通過 `WITH GRANT OPTION` 鏈路授予。
8. 密碼過期/鎖定 (ORA-28001/ORA-28000) → `ALTER USER user ACCOUNT UNLOCK; ALTER USER user IDENTIFIED BY new_pass;` 檢查 Profile `PASSWORD_LIFE_TIME`/`PASSWORD_LOCK_TIME`。

統一審計 (12c+) — 權限變更全記錄

-- 啟用統一審計 (默認啟用)
-- 審計權限相關操作
CREATE AUDIT POLICY audit_priv_changes
    ACTIONS CREATE USER, ALTER USER, DROP USER,
            CREATE ROLE, ALTER ROLE, DROP ROLE,
            GRANT, REVOKE,
            AUDIT, NOAUDIT;
AUDIT POLICY audit_priv_changes;

-- 查詢審計記錄
SELECT event_timestamp, dbusername, action_name, object_name, return_code, sql_text
FROM unified_audit_trail
WHERE action_name IN ('GRANT','REVOKE','CREATE USER','ALTER USER')
  AND event_timestamp > SYSDATE - 1
ORDER BY event_timestamp DESC;

總結

• 權限雙軌：系統權限 (實例級) vs 對象權限 (對象級)，授予/撤銷/傳遞/級聯機制迥異，**必須精確區分**。
• 拒絕預設角色 (CONNECT/RESOURCE/DBA) 直接用於生產，**構建三層 RBAC 體系**：基礎功能角色 → 業務應用角色 → 管理審計角色。
• 利用 **安全應用角色** 實現動態上下文授權，**最小權限原則**落地到代碼邏輯。
• PL/SQL 定義者權限模塊**不識別角色權限**，對象權限需直接授予模塊擁有者，這是**最常見誤區**。
• 啟用統一審計 (12c+)，對所有權限變更 (GRANT/REVOKE/CREATE/ALTER USER/ROLE) 進行不可篡改記錄，滿足合規與事後追溯。
• 定期掃描高危權限 (`ANY` 系統權限、`WITH ADMIN/GRANT OPTION`、DBA 角色持有者、未用賬號)，建立季度複核機制。