Oracle 資料安全全方位防護：備份恢復策略、加密、審計、VPD、Label Security 與資料遮罩

資料安全三大支柱

一、備份與恢復策略 (可用性基石)

RMAN 備份分級

# Level 0 完整備份 (每週)
BACKUP INCREMENTAL LEVEL 0 DATABASE PLUS ARCHIVELOG;

# Level 1 累積差異 (每日)
BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE PLUS ARCHIVELOG;

# 歸檔日誌備份 (每小時)
BACKUP ARCHIVELOG ALL DELETE INPUT;

恢復目標設定

Flashback 技術 (邏輯錯誤秒級恢復)

-- 啟用
ALTER DATABASE FLASHBACK ON;
ALTER TABLESPACE users FLASHBACK ON;

-- 表級恢復
FLASHBACK TABLE emp TO TIMESTAMP (SYSDATE - 1/24);

-- 交易回查
SELECT * FROM emp VERSIONS BETWEEN TIMESTAMP (SYSDATE-1) AND SYSDATE;

-- 資料庫級 (需 Mount 狀態)
FLASHBACK DATABASE TO TIMESTAMP (SYSDATE - 1);

二、透明資料加密 TDE (機密性核心)

Tablespace 加密 (11g+ 推薦)

-- 1. 設定 Keystore (Wallet)
ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/u01/wallet' IDENTIFIED BY "WalletPass123";
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "WalletPass123";
ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY "WalletPass123" WITH BACKUP;

-- 2. 建立加密表空間
CREATE TABLESPACE secure_ts
  DATAFILE '/u01/oradata/secure_ts01.dbf' SIZE 100M
  ENCRYPTION USING 'AES256' DEFAULT STORAGE (ENCRYPT);

-- 3. 將敏感表移入
ALTER TABLE hr.employees MOVE TABLESPACE secure_ts;
ALTER INDEX hr.emp_emp_id_pk REBUILD TABLESPACE secure_ts;

Column 加密 (細粒度)

CREATE TABLE credit_cards (
  id      NUMBER PRIMARY KEY,
  card_no VARCHAR2(16) ENCRYPT USING 'AES256' NO SALT,
  cvv     VARCHAR2(4)  ENCRYPT USING '3DES168' SALT
);

• SALT：相同值加密後不同，防彩虹表 (預設開啟)
• NO SALT：允許索引等值查詢

三、細粒度存取控制

VPD (Virtual Private Database) - 行級安全

-- 1. 定義 Policy Function
CREATE OR REPLACE FUNCTION vpd_emp_policy (p_schema VARCHAR2, p_obj VARCHAR2)
RETURN VARCHAR2 IS
BEGIN
  RETURN 'department_id = SYS_CONTEXT(''USERENV'', ''CLIENT_IDENTIFIER'')';
END;
/

-- 2. 套用 Policy
BEGIN
  DBMS_RLS.ADD_POLICY(
    object_schema => 'HR',
    object_name   => 'EMPLOYEES',
    policy_name   => 'EMP_DEPT_POLICY',
    function_schema => 'SECURITY',
    policy_function => 'VPD_EMP_POLICY',
    statement_types => 'SELECT,INSERT,UPDATE,DELETE',
    update_check => TRUE);
END;
/

-- 3. 應用設定 Context
EXEC DBMS_SESSION.SET_IDENTIFIER('10');  -- 僅看部門 10

OLS (Oracle Label Security) - 多層級安全

• 軍事/政府分級：Top Secret > Secret > Confidential > Unclassified
• 標籤元件：Level、Compartment、Group
• 適用：多租戶隔離、資料分級管控

四、審計與合規

統一審計 (12c+)

-- 建立審計政策
CREATE AUDIT POLICY hr_sensitive
  ACTIONS SELECT, INSERT, UPDATE, DELETE ON hr.employees
  ACTIONS COMPONENT=DATAPUMP ALL;

-- 啟用
AUDIT POLICY hr_sensitive BY hr, app_user;

-- 查詢審計軌跡
SELECT * FROM UNIFIED_AUDIT_TRAIL WHERE object_name='EMPLOYEES';

Fine-Grained Auditing (FGA)

BEGIN
  DBMS_FGA.ADD_POLICY(
    object_schema => 'HR',
    object_name   => 'EMPLOYEES',
    policy_name   => 'FGA_SALARY_ACCESS',
    audit_condition => 'salary > 10000',
    audit_column  => 'SALARY',
    handler_schema => 'SECURITY',
    handler_module => 'ALERT_HANDLER',
    enable => TRUE);
END;
/
-- 僅薪資 > 10000 存取才審計，可觸發警報程序

五、資料遮罩 (非生產環境保護)

-- Data Masking Pack (Enterprise Manager)
-- 或手動實作
CREATE OR REPLACE FUNCTION mask_id_card (p_val VARCHAR2) RETURN VARCHAR2 IS
BEGIN
  RETURN SUBSTR(p_val,1,3) || '********' || SUBSTR(p_val,-4);
END;
/
-- 匯出前遮罩
CREATE TABLE emp_masked AS 
SELECT empno, ename, mask_id_card(id_card) id_card, sal FROM emp;

六、新特性：區塊鏈表 (19c+)

CREATE TABLE bank_ledger (
  id       NUMBER PRIMARY KEY,
  txn_dt   DATE,
  amount   NUMBER,
  hash     RAW(2000)
) BLOCKCHAIN;

• 資料列鏈接雜湊，不可竄改、可驗證
• 適用：稽核追蹤、金融帳本、供應鏈溯源

安全強化檢查清單