Oracle用戶權限與角色管理深度解析：系統權限、對象權限、角色體系與實戰授權

權限分類總覽

三大標準角色（向後兼容）

警示：RESOURCE含UNLIMITED TABLESPACE，授予後用戶可在SYSTEM表空間建表，**生產環境建議拆解細粒度權限，不直接授予標準角色**。

用戶生命周期管理

創建用戶

CREATE USER user01 IDENTIFIED BY u01
  DEFAULT TABLESPACE users
  TEMPORARY TABLESPACE temp
  QUOTA 100M ON users
  PROFILE app_profile;
  -- PROFILE 綁定密碼/資源策略

修改密碼

-- 管理員重置
ALTER USER user01 IDENTIFIED BY new_pass;

-- 用戶自改（舊密碼不回顯）
PASSWORD
-- 或
ALTER USER user01 IDENTIFIED BY new_pass REPLACE old_pass;

刪除用戶

DROP USER user01;              -- 無對象時
DROP USER user01 CASCADE;      -- 連帶刪除所有對象

角色管理實戰

創建業務角色

CREATE ROLE app_readonly;
CREATE ROLE app_readwrite;
CREATE ROLE app_admin;

-- 授予系統權限
GRANT CREATE SESSION TO app_readonly, app_readwrite, app_admin;
GRANT CREATE TABLE, CREATE VIEW, CREATE PROCEDURE TO app_readwrite, app_admin;

-- 授予對象權限（核心：按Schema批量）
GRANT SELECT ON schema_a.table_x TO app_readonly;
GRANT SELECT, INSERT, UPDATE, DELETE ON schema_a.table_x TO app_readwrite;
GRANT ALL ON schema_a.table_x TO app_admin;

-- 授予角色給用戶
GRANT app_readonly TO user_report;
GRANT app_readwrite TO user_app;
GRANT app_admin TO user_dba;

刪除角色

DROP ROLE app_readonly;  -- 級聯撤銷所有授予

權限傳遞鏈與WITH ADMIN/GRANT OPTION

撤銷權限影響分析

• REVOKE CONNECT, RESOURCE FROM user01：僅禁止訪問，**不刪除用戶創建的對象**，其他有權用戶仍可訪問該對象。
• REVOKE SELECT ON table_x FROM role_y：立即生效，持有role_y的會話下次解析失效。

審計與合規查詢

-- 1. 用戶直接系統權限
SELECT * FROM dba_sys_privs WHERE grantee = 'APP_USER';

-- 2. 用戶角色（含嵌套）
SELECT * FROM dba_role_privs WHERE grantee = 'APP_USER';

-- 3. 角色擁有的系統權限
SELECT * FROM role_sys_privs WHERE role = 'APP_ADMIN';

-- 4. 對象權限矩陣
SELECT grantee, owner, table_name, privilege, grantable
FROM dba_tab_privs
WHERE owner = 'SCHEMA_A'
ORDER BY grantee, table_name;

最佳實踐原則

1. 最小權限：業務用戶僅獲CREATE SESSION + 所需對象權限，通過角色聚合。
2. 角色分層：READONLY → READWRITE → ADMIN，避免直接授予DBA。
3. 審計關鍵權限：AUDIT GRANT ANY PRIVILEGE, CREATE USER, DROP USER BY ACCESS;
4. 定期認證：季度審核DBA_ROLE_PRIVS、DBA_SYS_PRIVS、DBA_TAB_PRIVS，撤銷過期授權。

總結

Oracle權限體系 = 系統權限(做什麼事) + 對象權限(動什麼東西) + 角色(打包分發)。掌握GRANT/REVOKE語法、標準角色陷阱、WITH ADMIN/GRANT OPTION風險、嵌套角色查詢，即可構建清晰可控的權限矩陣。