您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫基礎

Oracle 11g 預設審計完整解析：AUDIT_TRAIL=DB、23 項系統權限、28 項語句審計、AUD$ 空間風險與 OS 審計遷移建議

編輯：Oracle 資料庫基礎

11g 審計預設行為大變革：開箱即用的合規基線

Oracle 10g AUDIT_TRAIL=NONE (預設關閉) → **11g AUDIT_TRAIL=DB (預設開啟)**。資料庫安裝完成即自動審計 23 項系統權限與 28 項關鍵語句，審計記錄寫入 SYS.AUD$ (SYSTEM 表空間)。

預設審計的 23 項系統權限

SQL> SELECT PRIVILEGE, SUCCESS, FAILURE FROM DBA_PRIV_AUDIT_OPTS;

PRIVILEGE                              SUCCESS    FAILURE
-------------------------------------- ---------- ----------
CREATE EXTERNAL JOB                    BY ACCESS  BY ACCESS
CREATE ANY JOB                         BY ACCESS  BY ACCESS
GRANT ANY OBJECT PRIVILEGE             BY ACCESS  BY ACCESS
EXEMPT ACCESS POLICY                   BY ACCESS  BY ACCESS
CREATE ANY LIBRARY                     BY ACCESS  BY ACCESS
GRANT ANY PRIVILEGE                    BY ACCESS  BY ACCESS
DROP PROFILE                           BY ACCESS  BY ACCESS
ALTER PROFILE                          BY ACCESS  BY ACCESS
DROP ANY PROCEDURE                     BY ACCESS  BY ACCESS
ALTER ANY PROCEDURE                    BY ACCESS  BY ACCESS
CREATE ANY PROCEDURE                   BY ACCESS  BY ACCESS
ALTER DATABASE                         BY ACCESS  BY ACCESS
GRANT ANY ROLE                         BY ACCESS  BY ACCESS
CREATE PUBLIC DATABASE LINK            BY ACCESS  BY ACCESS
DROP ANY TABLE                         BY ACCESS  BY ACCESS
ALTER ANY TABLE                        BY ACCESS  BY ACCESS
CREATE ANY TABLE                       BY ACCESS  BY ACCESS
DROP USER                              BY ACCESS  BY ACCESS
ALTER USER                             BY ACCESS  BY ACCESS
CREATE USER                            BY ACCESS  BY ACCESS
CREATE SESSION                         BY ACCESS  BY ACCESS   -- ★ 關鍵：登入審計
AUDIT SYSTEM                           BY ACCESS  BY ACCESS
ALTER SYSTEM                           BY ACCESS  BY ACCESS

預設審計的 28 項語句操作

SQL> SELECT AUDIT_OPTION, SUCCESS, FAILURE FROM DBA_STMT_AUDIT_OPTS;

AUDIT_OPTION                           SUCCESS    FAILURE
-------------------------------------- ---------- ----------
ALTER SYSTEM                           BY ACCESS  BY ACCESS
SYSTEM AUDIT                           BY ACCESS  BY ACCESS
CREATE SESSION                         BY ACCESS  BY ACCESS
CREATE USER                            BY ACCESS  BY ACCESS
ALTER USER                             BY ACCESS  BY ACCESS
DROP USER                              BY ACCESS  BY ACCESS
PUBLIC SYNONYM                         BY ACCESS  BY ACCESS
DATABASE LINK                          BY ACCESS  BY ACCESS
ROLE                                   BY ACCESS  BY ACCESS
PROFILE                                BY ACCESS  BY ACCESS
CREATE ANY TABLE                       BY ACCESS  BY ACCESS
ALTER ANY TABLE                        BY ACCESS  BY ACCESS
DROP ANY TABLE                         BY ACCESS  BY ACCESS
CREATE PUBLIC DATABASE LINK            BY ACCESS  BY ACCESS
GRANT ANY ROLE                         BY ACCESS  BY ACCESS
ALTER DATABASE                         BY ACCESS  BY ACCESS
CREATE ANY PROCEDURE                   BY ACCESS  BY ACCESS
ALTER ANY PROCEDURE                    BY ACCESS  BY ACCESS
DROP ANY PROCEDURE                     BY ACCESS  BY ACCESS
ALTER PROFILE                          BY ACCESS  BY ACCESS
DROP PROFILE                           BY ACCESS  BY ACCESS
GRANT ANY PRIVILEGE                    BY ACCESS  BY ACCESS
CREATE ANY LIBRARY                     BY ACCESS  BY ACCESS
EXEMPT ACCESS POLICY                   BY ACCESS  BY ACCESS
GRANT ANY OBJECT PRIVILEGE             BY ACCESS  BY ACCESS
CREATE ANY JOB                         BY ACCESS  BY ACCESS
CREATE EXTERNAL JOB                    BY ACCESS  BY ACCESS

核心風險：CREATE SESSION 審計導致 SYSTEM 表空間撐爆 → 所有用戶無法登入

故障鏈：

預設審計 CREATE SESSION → 每次登入寫入 SYS.AUD$

SYSTEM 表空間滿 (無法自動擴展/磁碟滿)

INSERT INTO SYS.AUD$ 失敗

觸發邏輯：**審計失敗 → 登入失敗** (非 SYSDBA)

**所有普通用戶無法連接資料庫**，業務全面中斷

緊急恢復方案 (需 SYSDBA)

-- 1. SYSDBA 仍可登入 (不受審計失敗影響)
CONN / AS SYSDBA

-- 2. 方案 A：擴展 SYSTEM 表空間 (若磁碟有空間)
ALTER TABLESPACE SYSTEM ADD DATAFILE '/u01/oradata/ORCL/system02.dbf' SIZE 1G AUTOEXTEND ON;

-- 3. 方案 B：清理 AUD$ (立即恢復登入)
TRUNCATE TABLE SYS.AUD$;  -- 或 DELETE 保留近期
-- 注意：TRUNCATE 會重置 High Water Mark，空間立即釋放

-- 4. 方案 C：移動 AUD$ 到專用表空間 (根治)
CREATE TABLESPACE AUDIT_TS DATAFILE '/u01/oradata/ORCL/audit_ts01.dbf' SIZE 5G AUTOEXTEND ON;
BEGIN
  DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
    AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
    AUDIT_TRAIL_LOCATION_VALUE => 'AUDIT_TS');
END;
/
-- 11gR2+ 支援，將 AUD$ 移出 SYSTEM 表空間

審計記錄查詢與分析

-- 按操作類型統計
SELECT ACTION_NAME, COUNT(*) 
FROM DBA_AUDIT_TRAIL 
GROUP BY ACTION_NAME 
ORDER BY COUNT(*) DESC;

-- 典型輸出：
-- ACTION_NAME                    COUNT(*)
-- ---------------------------- ----------
-- LOGON                                 90
-- LOGOFF                                59
-- ALTER SYSTEM                           5
-- CREATE DATABASE LINK                   5
-- DROP DATABASE LINK                     5
-- CREATE PUBLIC SYNONYM                  2
-- DROP PUBLIC SYNONYM                    2
-- ALTER DATABASE                         3
-- SYSTEM REVOKE                          1

-- 查詢特定用戶登入歷史
SELECT USERNAME, TIMESTAMP, RETURNCODE, OS_USERNAME, USERHOST, TERMINAL
FROM DBA_AUDIT_TRAIL
WHERE ACTION_NAME = 'LOGON'
  AND USERNAME = 'APP_USER'
ORDER BY TIMESTAMP DESC;

AUDIT_TRAIL 模式選擇與 OS 審計遷移建議

模式	參數值	儲存位置	優點	缺點	建議場景
資料庫審計	`DB` / `DB,EXTENDED`	`SYS.AUD$` (SYSTEM 表空間)	SQL 查詢方便、支援 `EXTENDED` 記錄 SQL_TEXT/BIND	佔用 SYSTEM 表空間、風險高、效能開銷大	非核心庫、審計量小
作業系統審計	`OS`	`$ORACLE_BASE/admin/$ORACLE_SID/adump/*.aud`	不佔用資料庫空間、檔案可獨立備份/清理、效能較好、安全性高	查詢需 OS 工具/外部表、Windows Event Log 格式特殊	生產核心庫強烈推薦
XML 審計	`XML` / `XML,EXTENDED`	OS XML 檔案	結構化、易解析	檔案較大	需自動化分析管道

遷移至 OS 審計步驟

-- 1. 設定審計檔案目錄 (預設 $ORACLE_BASE/admin/$ORACLE_SID/adump)
ALTER SYSTEM SET AUDIT_FILE_DEST = '/u01/app/oracle/admin/ORCL/adump' SCOPE=SPFILE;

-- 2. 切換審計模式 (需重啟)
ALTER SYSTEM SET AUDIT_TRAIL = OS SCOPE=SPFILE;

-- 3. 重啟資料庫
SHUTDOWN IMMEDIATE;
STARTUP;

-- 4. 驗證
SHOW PARAMETER AUDIT_TRAIL;
-- 生成 .aud 檔案於 AUDIT_FILE_DEST

-- 5. 定期清理策略 (crontab)
find /u01/app/oracle/admin/ORCL/adump -name '*.aud' -mtime +30 -delete

進階審計管理：DBMS_AUDIT_MGMT (11gR2+)

-- 1. 初始化審計管理 (建立內部作業)
BEGIN
  DBMS_AUDIT_MGMT.INIT_CLEANUP(
    AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
    DEFAULT_CLEANUP_INTERVAL => 24);  -- 小時
END;
/

-- 2. 設定清理策略：保留 90 天
BEGIN
  DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
    AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
    LAST_ARCHIVE_TIME => SYSDATE - 90);
END;
/

-- 3. 執行清理 (可排程)
BEGIN
  DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
    AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
    USE_LAST_ARCH_TIMESTAMP => TRUE);
END;
/

-- 4. 移動 AUD$ 到專用表空間 (如前節)
-- 5. 統一管理 OS/.aud、XML、DB 審計