Oracle 進階 SQL 技巧：用戶權限體系、角色管理、數據庫鏈、集合運算、時間函數與層次查詢實戰

用戶存取控制體系

用戶創建與認證

-- 標準創建
CREATE USER user_1 IDENTIFIED BY pwd_1;

-- 外部認證 (OS/SSL/Kerberos)
CREATE USER ops$oracle IDENTIFIED EXTERNALLY;

-- 全局用戶 (企業目錄/OID)
CREATE USER global_user IDENTIFIED GLOBALLY AS 'CN=global_user,OU=users,DC=example,DC=com';

系統權限 vs 對象權限

授權實戰範例

-- 表級查詢授權
GRANT SELECT ON tt1 TO user_1;

-- 列級更新授權
GRANT UPDATE(col1, col2) ON table1 TO user_1;

-- 可轉授權限
GRANT SELECT ON tt1 TO user_1 WITH GRANT OPTION;

-- 公共授權
GRANT SELECT ON table1 TO PUBLIC;

權限回收

REVOKE SELECT ON table1 FROM user_1;
REVOKE CREATE TABLE FROM user_1;
-- CASCADE CONSTRAINTS 強制回收引用權限導致的約束

角色管理最佳實踐

角色生命周期

-- 1. 創建角色
CREATE ROLE manager;

-- 2. 授予角色權限
GRANT CREATE TABLE, CREATE VIEW TO manager;

-- 3. 授予用戶角色
GRANT manager TO user_1, user_2;

-- 4. 啟用/禁用角色 (會話級)
SET ROLE manager;          -- 啟用
SET ROLE ALL EXCEPT manager; -- 禁用特定
SET ROLE NONE;             -- 禁用所有

-- 5. 密碼保護角色
CREATE ROLE secure_role IDENTIFIED BY password;
SET ROLE secure_role IDENTIFIED BY password;

預定義角色速查

Database Link 分布式查詢

創建與使用

-- 創建公共 DB Link
CREATE PUBLIC DATABASE LINK hq.example.com 
CONNECT TO remote_user IDENTIFIED BY password 
USING 'tns_alias';

-- 查詢遠程表
SELECT * FROM employees@hq.example.com;

-- 分布式事務 (兩階段提交)
INSERT INTO local_table SELECT * FROM remote_table@hq.example.com;
COMMIT; -- 自動協調 2PC

DB Link 類型對比

集合運算：並集、交集、差集

語法規則

• 參與查詢的**列數、順序、數據類型必須兼容**
• UNION 去重並排序，UNION ALL 保留重複、不排序（性能優）
• INTERSECT 交集，MINUS 差集（第一集合減去共同部分）

實戰範例

-- 並集：所有員工與歷史崗位記錄
SELECT employee_id, job_id FROM employees
UNION
SELECT employee_id, job_id FROM job_history;

-- 保留重複的並集
SELECT employee_id, job_id FROM employees
UNION ALL
SELECT employee_id, job_id FROM job_history;

-- 交集：既是員工又有歷史崗位
SELECT employee_id, job_id FROM employees
INTERSECT
SELECT employee_id, job_id FROM job_history;

-- 差集：員工中無歷史崗位記錄
SELECT employee_id, job_id FROM employees
MINUS
SELECT employee_id, job_id FROM job_history;

時間與時區函數深度應用

核心函數對比

時區操作實戰

-- 查看時區偏移
SELECT TZ_OFFSET('US/Eastern') FROM dual;  -- -04:00 或 -05:00 (夏令時)

-- 設置會話時區
ALTER SESSION SET TIME_ZONE = '-8:00';
SELECT SESSIONTIMEZONE, CURRENT_DATE FROM dual;

-- 時區轉換
SELECT FROM_TZ(TIMESTAMP '2024-01-15 08:00:00', 'America/New_York') 
     AT TIME ZONE 'Asia/Shanghai' AS beijing_time FROM dual;

-- 間隔運算
SELECT 
    CURRENT_TIMESTAMP,
    CURRENT_TIMESTAMP + INTERVAL '1' DAY,
    CURRENT_TIMESTAMP + INTERVAL '2' HOUR,
    CURRENT_TIMESTAMP - INTERVAL '30' MINUTE
FROM dual;

時間戳構造與解析

-- 字符串轉 TIMESTAMP
TO_TIMESTAMP('2024-01-15 14:30:00', 'YYYY-MM-DD HH24:MI:SS')
TO_TIMESTAMP_TZ('2024-01-15 14:30:00 +08:00', 'YYYY-MM-DD HH24:MI:SS TZH:TZM')

-- 日期間隔
NUMTODSINTERVAL(3600, 'SECOND')  -- 1小時
NUMTOYMINTERVAL(12, 'MONTH')     -- 1年

層次查詢：組織架構、BOM、遞歸關係

核心語法

SELECT [LEVEL], column, expr...
FROM table
[WHERE condition]
[START WITH root_condition]
CONNECT BY [NOCYCLE] PRIOR child_col = parent_col;

遍歷方向對比

實戰範例：員工管理鏈路

-- 自頂向下：從 CEO 展開全員
SELECT LPAD(' ', (LEVEL-1)*2) || ename AS org_chart, LEVEL, empno, mgr
FROM emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr;

-- 自底向上：查詢某員工到 CEO 的管理鏈
SELECT ename, LEVEL
FROM emp
START WITH empno = 7788  -- SCOTT
CONNECT BY PRIOR mgr = empno;

層次查詢輔助函數

SELECT 
    ename,
    LEVEL,
    SYS_CONNECT_BY_PATH(ename, ' > ') AS path,  -- 路徑串聯
    CONNECT_BY_ISLEAF AS is_leaf,               -- 是否葉子節點
    CONNECT_BY_ROOT ename AS root_ename         -- 根節點名稱
FROM emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr;

綜合實戰：權限審計報表

-- 生成用戶-角色-權限三維矩陣
SELECT 
    u.username,
    r.granted_role AS role,
    p.privilege AS sys_priv,
    op.table_name, op.privilege AS obj_priv
FROM dba_users u
LEFT JOIN dba_role_privs r ON u.username = r.grantee
LEFT JOIN dba_sys_privs p ON u.username = p.grantee
LEFT JOIN dba_tab_privs op ON u.username = op.grantee
WHERE u.username NOT IN ('SYS','SYSTEM','DBSNMP','OUTLN')
ORDER BY u.username, r.granted_role;