Oracle 建立唯讀監控帳號：最小權限原則保障生產庫安全

核心原則：最小權限、唯讀視圖、無物件擁有權

監控帳號僅需「看」，不需「改」。透過封裝視圖暴露特定指標，徹底隔離底層字典表。

實施步驟

1. 建立監控用戶

SQLPLUS / AS SYSDBA
CREATE USER db_monitor IDENTIFIED BY "MonPass#2024"
DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp
ACCOUNT UNLOCK;

GRANT CREATE SESSION TO db_monitor;  -- 僅連線權限，無 RESOURCE/CONNECT 角色

2. 建立監控視圖（核心邏輯封裝）

-- 表空間使用率視圖
CREATE OR REPLACE VIEW v_tablespace_usage AS
SELECT
    a.tablespace_name,
    ROUND(SUM(a.bytes)/1024/1024,2) AS total_mb,
    ROUND(SUM(b.bytes)/1024/1024,2) AS free_mb,
    ROUND(SUM(b.bytes)/SUM(a.bytes)*100,2) AS pct_free
FROM dba_data_files a
JOIN dba_free_space b ON a.file_id = b.file_id
GROUP BY a.tablespace_name;

-- 長時間運行 SQL 視圖
CREATE OR REPLACE VIEW v_longops AS
SELECT sid, serial#, opname, target, sofar, totalwork,
       ROUND(sofar/totalwork*100,2) AS pct_done,
       elapsed_seconds, time_remaining
FROM v$session_longops
WHERE totalwork > 0 AND sofar < totalwork;

-- 等待事件視圖
CREATE OR REPLACE VIEW v_wait_events AS
SELECT event, total_waits, time_waited_micro/1000000 AS time_waited_sec
FROM v$system_event
WHERE wait_class != 'Idle'
ORDER BY time_waited_micro DESC;

3. 授予視圖 SELECT 權限

GRANT SELECT ON v_tablespace_usage TO db_monitor;
GRANT SELECT ON v_longops TO db_monitor;
GRANT SELECT ON v_wait_events TO db_monitor;
-- 持續新增視圖，權限同步授予

4. 驗證權限隔離

CONN db_monitor/MonPass#2024
SELECT * FROM USER_TAB_PRIVS;  -- 僅顯示上述視圖權限
SELECT * FROM V_TABLESPACE_USAGE;  -- 正常查詢
SELECT * FROM DBA_TABLES;          -- ORA-00942: table or view does not exist

進階擴展：監控視圖標準化庫

自動化部署腳本片段

#!/bin/bash
# deploy_monitor_views.sh
sqlplus -s / as sysdba <