Oracle Audit 与 Unified Audit Trail

适用版本：Oracle Database 11g / 12c / 19c / 23ai 文档版本：v1.0 / 2026-07

1. 概述

Oracle 审计机制用于记录数据库操作[1]：

类型	说明	版本
传统审计	audit_trail 参数控制	11g-
细粒度审计（FGA）	基于条件的审计	9i+
统一审计（Unified Audit）	统一审计架构	12c+

2. 传统审计

2.1 配置

-- 启用审计
ALTER SYSTEM SET audit_trail='DB,EXTENDED' SCOPE=SPFILE;
-- 重启生效

-- audit_trail 选项：
-- NONE: 不审计
-- DB: 审计到 SYS.AUD$
-- DB,EXTENDED: 审计到 SYS.AUD$，含 SQL 文本和绑定变量
-- OS: 审计到 OS 文件
-- XML: 审计到 XML 文件
-- XML,EXTENDED: XML + SQL 文本

2.2 审计语句

-- 审计权限使用
AUDIT SELECT TABLE BY scott BY ACCESS;
AUDIT INSERT TABLE BY scott BY ACCESS;
AUDIT UPDATE TABLE BY scott BY ACCESS;
AUDIT DELETE TABLE BY scott BY ACCESS;

-- 审计所有用户
AUDIT SELECT TABLE BY ACCESS;

-- 审计系统权限
AUDIT CREATE TABLE BY ACCESS;
AUDIT CREATE USER BY ACCESS;
AUDIT GRANT ANY PRIVILEGE BY ACCESS;

-- 审计对象
AUDIT SELECT ON scott.employees BY ACCESS;
AUDIT ALL ON scott.employees BY ACCESS;

-- 审计特权操作
AUDIT SYSDBA BY ACCESS;
AUDIT SYSOPER BY ACCESS;

2.3 BY ACCESS vs BY SESSION

选项	说明
BY ACCESS	每次操作一条记录
BY SESSION	每会话一条记录

2.4 查看审计

-- 传统审计视图
SELECT 
  timestamp,
  username,
  userhost,
  action_name,
  obj_name,
  returncode,
  sqltext
FROM dba_audit_trail
WHERE timestamp > SYSDATE-1
ORDER BY timestamp DESC;

-- 对象审计
SELECT * FROM dba_audit_object;

-- 语句审计
SELECT * FROM dba_audit_statement;

-- 权限审计
SELECT * FROM dba_audit_privileges;

3. 细粒度审计（FGA）

3.1 创建 FGA 策略

BEGIN
  DBMS_FGA.ADD_POLICY(
    object_schema => 'scott',
    object_name => 'employees',
    policy_name => 'salary_audit',
    audit_condition => 'salary > 10000',
    audit_column => 'salary',
    handler_schema => NULL,
    handler_module => NULL,
    enable => TRUE,
    statement_types => 'SELECT, UPDATE'
  );
END;
/

3.2 查看审计

-- FGA 审计记录
SELECT 
  db_user,
  os_user,
  object_schema,
  object_name,
  policy_name,
  sql_text,
  timestamp
FROM dba_fga_audit_trail
WHERE timestamp > SYSDATE-1;

3.3 管理 FGA

-- 禁用
BEGIN
  DBMS_FGA.DISABLE_POLICY(
    object_schema => 'scott',
    object_name => 'employees',
    policy_name => 'salary_audit'
  );
END;
/

-- 启用
BEGIN
  DBMS_FGA.ENABLE_POLICY(
    object_schema => 'scott',
    object_name => 'employees',
    policy_name => 'salary_audit',
    enable => TRUE
  );
END;
/

-- 删除
BEGIN
  DBMS_FGA.DROP_POLICY(
    object_schema => 'scott',
    object_name => 'employees',
    policy_name => 'salary_audit'
  );
END;
/

4. 统一审计（Unified Audit）

4.1 架构

12c 引入统一审计架构[2]：

统一审计策略
统一审计记录
所有审计信息在一个视图
性能优化

4.2 启用统一审计

-- 检查是否启用纯统一审计
SELECT value FROM v$option WHERE parameter='Unified Auditing';
-- TRUE: 纯统一审计
-- FALSE: 混合模式（默认）

-- 启用纯统一审计
-- 需要重新链接 Oracle
cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk uniaud_on ioracle

4.3 创建统一审计策略

-- 审计所有 SELECT
CREATE AUDIT POLICY select_all_pol
  ACTIONS SELECT ON scott.employees;

-- 启用
AUDIT POLICY select_all_pol;

-- 审计特权操作
CREATE AUDIT POLICY admin_actions_pol
  PRIVILEGES SYSDBA, SYSOPER;

AUDIT POLICY admin_actions_pol;

-- 审计角色
CREATE AUDIT POLICY dba_role_pol
  ROLES DBA;

AUDIT POLICY dba_role_pol;

-- 审计特定用户
CREATE AUDIT POLICY scott_actions_pol
  ACTIONS SELECT, INSERT, UPDATE, DELETE
  WHEN 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') = ''SCOTT'''
  EVALUATE PER SESSION;

AUDIT POLICY scott_actions_pol BY scott;

4.4 查看审计

-- 统一审计视图
SELECT 
  event_timestamp,
  dbusername,
  action_name,
  object_name,
  sql_text,
  return_code
FROM unified_audit_trail
WHERE event_timestamp > SYSDATE-1
ORDER BY event_timestamp DESC;

-- 特定审计策略
SELECT * FROM unified_audit_trail 
WHERE audit_type='Standard';

4.5 管理审计策略

-- 查看策略
SELECT * FROM audit_unified_policies;

-- 启用/禁用
AUDIT POLICY select_all_pol;
NOAUDIT POLICY select_all_pol;

-- 修改策略
ALTER AUDIT POLICY select_all_pol
  ADD ACTIONS INSERT ON scott.employees;

-- 删除策略
DROP AUDIT POLICY select_all_pol;

5. 强制审计

5.1 默认强制审计

不论配置如何，以下操作默认审计：

数据库启动（STARTUP）
数据库关闭（SHUTDOWN）
SYSDBA/SYSOPER 登录

5.2 查看强制审计

SELECT * FROM unified_audit_trail 
WHERE audit_type='Mandatory';

6. 审计管理

6.1 清理审计记录

-- 传统审计清理
EXEC DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
  audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,
  use_last_arch_timestamp => FALSE
);

-- 统一审计清理
EXEC DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
  audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
  use_last_arch_timestamp => FALSE
);

-- 设置归档时间戳
EXEC DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
  audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
  last_archive_time => SYSTIMESTAMP-7
);

6.2 自动清理

-- 创建清理任务
BEGIN
  DBMS_AUDIT_MGMT.CREATE_PURGE_JOB(
    audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
    audit_trail_name => NULL,
    purge_interval => 24,  -- 小时
    purge_name => 'Audit_Purge_Job',
    use_last_arch_timestamp => TRUE
  );
END;
/

-- 启用
EXEC DBMS_AUDIT_MGMT.SET_PURGE_JOB_STATUS('Audit_Purge_Job', DBMS_AUDIT_MGMT.PURGE_JOB_ENABLE);

6.3 归档

-- 创建归档时间戳作业
BEGIN
  DBMS_AUDIT_MGMT.INIT_CLEANUP(
    audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
    default_cleanup_interval => 24
  );
END;
/

7. 审计与多租户

7.1 CDB 级审计

-- Common 审计策略
CREATE AUDIT POLICY c##admin_pol
  ACTIONS SELECT
  CONTAINER=ALL;

AUDIT POLICY c##admin_pol CONTAINER=ALL;

7.2 PDB 级审计

-- 切换到 PDB
ALTER SESSION SET CONTAINER = hrpdb;

-- 创建 Local 审计策略
CREATE AUDIT POLICY hr_audit_pol
  ACTIONS SELECT ON hr.employees;

AUDIT POLICY hr_audit_pol;

8. 常见审计场景

8.1 审计 SYSDBA 操作

-- 统一审计
CREATE AUDIT POLICY sysdba_pol
  PRIVILEGES SYSDBA;

AUDIT POLICY sysdba_pol BY SYS;

-- 查看审计
SELECT event_timestamp, dbusername, action_name, sql_text
FROM unified_audit_trail
WHERE unified_audit_policies='SYSDBA_POL'
ORDER BY event_timestamp DESC;

8.2 审计 DDL 操作

CREATE AUDIT POLICY ddl_pol
  ACTIONS CREATE TABLE, DROP TABLE, ALTER TABLE, CREATE USER, DROP USER;

AUDIT POLICY ddl_pol;

8.3 审计敏感数据访问

-- 审计员工薪资查询
CREATE AUDIT POLICY salary_access_pol
  ACTIONS SELECT ON hr.employees(salary);

AUDIT POLICY salary_access_pol;

9. 常见坑与排错

9.1 审计未生效

修复：

-- 1. 检查 audit_trail
SHOW PARAMETER audit_trail;

-- 2. 启用审计
ALTER SYSTEM SET audit_trail='DB,EXTENDED' SCOPE=SPFILE;
-- 重启

-- 3. 验证策略
SELECT * FROM dba_stmt_audit_opts;
SELECT * FROM audit_unified_enabled_policies;

9.2 审计表过大

修复：

-- 1. 检查大小
SELECT segment_name, bytes/1024/1024 AS mb
FROM dba_segments
WHERE segment_name IN ('AUD$', 'FGA_LOG$', 'AUDIT$');

-- 2. 清理
EXEC DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
  audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,
  use_last_arch_timestamp => FALSE
);

-- 3. 配置自动清理

9.3 统一审计未启用

修复：

# 1. 检查
sqlplus / as sysdba
SELECT value FROM v$option WHERE parameter='Unified Auditing';

# 2. 重新链接
cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk uniaud_on ioracle

9.4 审计性能影响

修复：

-- 1. 减少审计范围
NOAUDIT POLICY select_all_pol;

-- 2. 使用条件审计
CREATE AUDIT POLICY cond_pol
  ACTIONS SELECT ON scott.employees
  WHEN 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') != ''APP_USER'''
  EVALUATE PER SESSION;

-- 3. 定期清理

9.5 FGA 审计不生效

修复：

-- 1. 检查策略
SELECT * FROM dba_audit_policies;

-- 2. 启用
BEGIN
  DBMS_FGA.ENABLE_POLICY(
    object_schema => 'scott',
    object_name => 'employees',
    policy_name => 'salary_audit',
    enable => TRUE
  );
END;
/

10. 最佳实践

生产启用审计：合规要求
使用统一审计（12c+）：性能更好
审计 SYSDBA 操作：安全合规
审计敏感数据访问：FGA 细粒度
定期清理审计记录：避免表过大
设置自动清理：DBMS_AUDIT_MGMT
审计归档：长期保存
监控审计：发现异常
PDB 独立审计策略：多租户隔离
避免过度审计：性能影响

11. 参考资料

[1] Oracle Database Security Guide 19c, “Auditing Database Activity” https://docs.oracle.com/en/database/oracle/oracle-database/19/dbseg/auditing-database-activity.html

[2] Oracle Database Security Guide 19c, “Unified Auditing” https://docs.oracle.com/en/database/oracle/oracle-database/19/dbseg/unified-auditing.html