MIS系統Oracle安全策略落地實施：備份恢復體系與RBAC權限模型

從理論到落地：MIS關鍵業務Oracle安全策略的工程化實施指南

針對MIS系統核心數據資產，本文將安全策略轉化為可執行、可驗證、可審計的工程交付物：RMAN備份腳本、Data Guard搭建清單、PITR演練腳本、角色權限矩陣表、審計策略部署包，確保RPO=0、RTO<30min、合規零缺陷。

一、備份體系工程化交付

1. RMAN備份腳本模板 (rman_backup.sh)

#!/bin/bash
# 每日全備 + 歸檔備份，保留7天磁帶/異地
export ORACLE_SID=ORCL
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
LOG=/backup/rman_$(date +%F).log

rman target / <> $LOG 2>&1
RUN {
  CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
  CONFIGURE BACKUP OPTIMIZATION ON;
  CONFIGURE CONTROLFILE AUTOBACKUP ON;
  CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO COMPRESSED BACKUPSET;
  
  BACKUP AS COMPRESSED BACKUPSET
    DATABASE PLUS ARCHIVELOG
    FORMAT '/backup/db_%d_%T_%s_%p.bkp'
    TAG 'DAILY_FULL_ARCH';
  
  DELETE NOPROMPT OBSOLETE;
  DELETE NOPROMPT ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-7';
}
EXIT
EOF

# 郵件告警
if [ $? -ne 0 ]; then
  mail -s "RMAN Backup FAILED $(hostname)" dba@company.com < $LOG
fi

2. Data Guard物理備庫搭建檢查清單

• ☐ 主庫FORCE LOGGING、ARCHIVELOG、FLASHBACK ON
• ☐ 主庫STANDBY_FILE_MANAGEMENT=AUTO
• ☐ 主備tnsnames.ora/listener.ora雙向配置
• ☐ RMAN DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE
• ☐ 備庫ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT
• ☐ DGMGRL驗證：SHOW CONFIGURATION、SHOW DATABASE verbose ORCL
• ☐ 定期Switchover演練（季度一次），記錄RTO

3. 邏輯備份 (Data Pump) 參數化腳本

expdp system/pwd \
  schemas=MIS_APP,MIS_BATCH \
  directory=DPUMP_DIR \
  dumpfile=MIS_%U_%T.dmp \
  logfile=expdp_MIS.log \
  parallel=4 \
  compression=ALL \
  encryption=ALL \
  encryption_password=StrongPwd#2024 \
  reuse_dumpfiles=Y

二、分級恢復SOP腳本化

1. 介質故障恢復 (recover_datafile.sh)

#!/bin/bash
# 參數: $1=文件號 $2=還原目標路徑
rman target / <

#!/bin/bash
# 在備用庫執行，模擬誤刪表SCOTT.EMP於 2024-01-15 10:30:00
TARGET_TIME="TO_DATE('2024-01-15 10:30:00','YYYY-MM-DD HH24:MI:SS')"

rman target / auxiliary / <

CREATE ROLE MIS_RO;
CREATE ROLE MIS_RW;
CREATE ROLE MIS_BATCH;
CREATE ROLE MIS_DBA;

GRANT SELECT ON MIS_APP.T_ORDERS TO MIS_RO;
GRANT SELECT,INSERT,UPDATE,DELETE ON MIS_APP.T_ORDERS TO MIS_RW;
GRANT EXECUTE ON MIS_APP.PKG_ORDER_PROC TO MIS_BATCH;

GRANT MIS_RW TO APP_USER;
GRANT MIS_RO TO BI_USER;
GRANT MIS_BATCH TO SCHED_USER;
GRANT MIS_DBA TO DBA_ONCALL;

-- 審計核心表
AUDIT SELECT, INSERT, UPDATE, DELETE ON MIS_APP.T_ORDERS BY ACCESS;
AUDIT EXECUTE ON MIS_APP.PKG_ORDER_PROC BY ACCESS;

# 每日巡檢腳本 (daily_compliance.sh)
# 1. 備份成功率
# 2. Data Guard延遲 < 5min
# 3. 歸檔目錄使用率 < 80%
# 4. 審計表空間增長率
# 5. 角色權限異變 (對比基線 role_matrix.csv)
# 6. 失效帳號、鎖定帳號、密碼即將過期
# 7. 輸出HTML報表郵件發送