Oracle 使用者權限管理實戰：從建立用戶到角色授權的完整體系

Oracle 權限模型嚴謹且分層：**系統權限** 控制「能做什麼操作」(建表、建會話)、**物件權限** 控制「能動什麼對象」(查詢某表、執行某程序)、**角色** 將權限打包便於批量授予。本文提供從用戶建立、密碼管理、系統/物件權限授予撤銷、權限傳遞、角色體系建立的完整操作指南。

預設帳號與登入方式

注意： SYS 擁有 SYSDBA 特權，實際上綁定 SYS schema，SYSTEM 為普通 DBA 帳號。

用戶生命週期管理

-- 建立用戶 (指定預設/臨時表空間、配額、密碼過期策略)
CREATE USER zhangsan IDENTIFIED BY "StrongPass#2024"
  DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp
  QUOTA UNLIMITED ON users
  PASSWORD EXPIRE ACCOUNT UNLOCK;

-- 修改密碼 (需 ALTER USER 權限或本人)
ALTER USER scott IDENTIFIED BY new_password;

-- 鎖定/解鎖
ALTER USER scott ACCOUNT LOCK / UNLOCK;

-- 刪除用戶 (CASCADE 連同物件一併刪除)
DROP USER zhangsan CASCADE;

系統權限授予與撤銷

核心系統權限： CREATE SESSION (登入)、CREATE TABLE/VIEW/PROCEDURE/SEQUENCE/TRIGGER、UNLIMITED TABLESPACE (忽略配額)、DBA (含所有系統權限 + SYSDBA 類操作)。

-- 授予 (WITH ADMIN OPTION 允許受授者再轉授)
GRANT CREATE SESSION, CREATE TABLE, UNLIMITED TABLESPACE TO zhangsan;
GRANT CREATE ANY TABLE, DROP ANY TABLE TO zhangsan WITH ADMIN OPTION; -- 強權限謹慎用

-- 撤銷
REVOKE CREATE TABLE FROM zhangsan;
REVOKE CREATE ANY TABLE FROM zhangsan;

關鍵區別： WITH ADMIN OPTION 用於系統權限/角色轉授；WITH GRANT OPTION 用於物件權限轉授。

物件權限授予與撤銷

常用物件權限： SELECT/INSERT/UPDATE/DELETE/REFERENCES/EXECUTE/INDEX/ALTER/FLASHBACK/ON COMMIT REFRESH。

-- 表/視圖 權限 (可指定欄位)
GRANT SELECT, INSERT, UPDATE ON hr.employees TO zhangsan;
GRANT UPDATE (salary, commission_pct) ON hr.employees TO zhangsan; -- 欄位級

-- 程序/函數/套件
GRANT EXECUTE ON hr.emp_pkg TO zhangsan;

-- 序列
GRANT SELECT ON hr.emp_seq TO zhangsan;

-- 撤銷
REVOKE UPDATE ON hr.employees FROM zhangsan;

權限傳遞鏈與安全邊界

• A --(WITH ADMIN)--> B --(WITH ADMIN)--> C：系統權限/角色可多級傳遞。
• A --(WITH GRANT)--> B --(WITH GRANT)--> C：物件權限可多級傳遞。
• 撤銷級聯： 撤銷 A 授予 B 的權限，**預設不級聯撤銷 B 授予 C 的權限** (需手動清理或使用 CASCADE 選項，視版本支援)。
• 物件擁有者 (Owner) 永擁有所有物件權限且不可撤銷。

角色：權限的容器化管理

-- 1. 建立角色
CREATE ROLE app_read_only;
CREATE ROLE app_developer;

-- 2. 角色授予權限
GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW TO app_developer;
GRANT SELECT ON hr.employees TO app_read_only;
GRANT SELECT ON hr.departments TO app_read_only;

-- 3. 角色授予用戶 (可再授予 WITH ADMIN OPTION)
GRANT app_read_only TO zhangsan;
GRANT app_developer TO lisi WITH ADMIN OPTION;

-- 4. 啟用/停用角色 (Session 級)
SET ROLE app_read_only; -- 或 SET ROLE ALL / NONE
-- 預設角色 (登入自動啟用)
ALTER USER zhangsan DEFAULT ROLE app_read_only;

-- 5. 刪除角色
DROP ROLE app_read_only;

角色限制與最佳實務

• 不可授予角色的權限： UNLIMITED TABLESPACE、任何 ANY 系統權限 (如 SELECT ANY TABLE) —— 必須直接授予用戶。
• 密碼保護角色： CREATE ROLE secure_role IDENTIFIED BY pwd; 啟用時需密碼，防盜用。
• 外部/全域角色： IDENTIFIED EXTERNALLY/GLOBALLY 整合 OS/目錄服務認證。
• 命名規範： ROLE_<業務域>_<權限層級> (如 ROLE_HR_READONLY, ROLE_FINANCE_APPDEV)。
• 定期審計： 查詢 DBA_SYS_PRIVS, DBA_TAB_PRIVS, DBA_ROLE_PRIVS 排查過度授權、孤立權限。

跨 Schema 存取語法

-- 必須加 Schema 前綴
SELECT * FROM zhangsan.my_table;
EXECUTE lisi.my_proc;
SELECT lisi.my_seq.NEXTVAL FROM DUAL;