oracle审计管理

适用于11g r2及以上版本，通过DBMS_AUDIT_MGMT包来管理审计记录
查看mgmt配置参数：

SELECT * FROM dba_audit_mgmt_config_params;
PARAMETER_NAME		       PARAMETER_VALUE		      AUDIT_TRAIL
------------------------------ ------------------------------ ----------------------------
DB AUDIT TABLESPACE	           TS_AUDIT 		              STANDARD AUDIT TRAIL
DB AUDIT TABLESPACE	           TS_AUDIT 		              FGA AUDIT TRAIL
AUDIT FILE MAX SIZE	           10000			       OS AUDIT TRAIL
AUDIT FILE MAX SIZE	           10000			       XML AUDIT TRAIL
AUDIT FILE MAX AGE	           5			              OS AUDIT TRAIL
AUDIT FILE MAX AGE	           5			              XML AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE      10000			       STANDARD AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE      10000			       FGA AUDIT TRAIL
OS FILE CLEAN BATCH SIZE       1000			       OS AUDIT TRAIL
OS FILE CLEAN BATCH SIZE       1000			       XML AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL      24                             STANDARD AUDIT TRAIL

1、审计表空间迁移

详见：oracle-base
审计表默认表空间是SYSTEM

SELECT * FROM dba_segments WHERE segment_name in('AUD$','FGA_LOG$');
SELECT table_name, tablespace_name FROM dba_tables WHERE table_name IN ('AUD$', 'FGA_LOG$') ORDER BY table_name;

创建审计表空间

SQL> create tablespace ts_audit datafile '/u01/app/oracle/oradata/MYCISDB/datafile/audit01.dbf' size 2g autoextend on, '/u01/app/oracle/oradata/MYCISDB/datafile/audit02.dbf' size 2g autoextend on;

迁移aud$和fga_log$至该表空间
迁移aud$
set_audit_trail_location中audit_trail_type参数包括：
DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD: Standard audit trail (AUD$).
DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD: Fine-grained audit trail (FGA_LOG$).
DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD: Both standard and fine-grained audit trails.
DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED: (12c) The unified audit tables.

BEGIN
  DBMS_AUDIT_MGMT.set_audit_trail_location(
  audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
  audit_trail_location_value => 'TS_AUDIT');
END; /

迁移FGA_LOG$表

BEGIN
  DBMS_AUDIT_MGMT.set_audit_trail_location(
  audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
  audit_trail_location_value => 'TS_AUDIT');
END;
/

迁移aud$和FGA_LOG$表

BEGIN
  DBMS_AUDIT_MGMT.set_audit_trail_location(
  audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,
  audit_trail_location_value => 'TS_AUDIT');
END;
/

注意：执行迁移时，11G需要取消对session的创建的审计(noaduit create session;)，因为执行期间会锁表，导致连接的审计记录不能插入至审计表从而使数据库连接失败

2、审计日志自动清理

DBMS_AUDIT_MGMT常数参考：constant

1）初始化设置及取消

设置

–AUD$表

BEGIN
DBMS_AUDIT_MGMT.INIT_CLEANUP(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
default_cleanup_interval => 24);
END;
/

–FGA_LOG$表

BEGIN
DBMS_AUDIT_MGMT.INIT_CLEANUP(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
default_cleanup_interval => 24);
END;
/

取消初始化配置

BEGIN
DBMS_AUDIT_MGMT.deinit_cleanup(audit_trail_type=>DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD);
END;
/

2）设置需要清理的审计记录及取消

–创建审计日志的归档job，用来设置过期的审计日志，如保留180天，则将180天以前数据会被清理，清理任务交给下面的清理job，即设置哪些数据需要清理
–DBMS_AUDIT_MGMT可以设置清理比一个时间更旧的数据，SET_LAST_ARCHIVE_TIMESTAMP用来设置这个时间，设置后可以在DBA_AUDIT_MGMT_LAST_ARCH_TS中查询

单独设置

BEGIN 
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
LAST_ARCHIVE_TIME => SYSDATE-180); 
END;
/

通过job设置

–AUD$表

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'SET_AUDIT_ARCHIVE_TIMESTAMP',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,LAST_ARCHIVE_TIME => SYSDATE-180); END;',
start_date => sysdate,
repeat_interval => 'FREQ=DAILY;BYHOUR=22', --每天22点执行
enabled => TRUE,
comments => 'Create an archive timestamp'
);
END;
/

–FGA_LOG$表

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'SET_FGA_AUDIT_ARCHIVE_TIMESTAMP',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,LAST_ARCHIVE_TIME => SYSDATE-180); END;',
start_date => sysdate,
repeat_interval => 'FREQ=DAILY;BYHOUR=22', --每天22点执行
enabled => TRUE,
comments => 'Create an archive timestamp'
);
END;
/

清除

BEGIN
  DBMS_AUDIT_MGMT.clear_last_archive_timestamp(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD);
END;
/

3）清理审计记录

手动清理

注意，如果设置了保留时间，则只清理过期的数据，如果参数use_last_arch_timestamp为FALSE，则清理所有审计数据
–AUD$表

BEGIN
  DBMS_AUDIT_MGMT.clean_audit_trail(
   audit_trail_type        => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
   use_last_arch_timestamp => TRUE);
END;
/

自动清理

–创建自动清理job 每7天执行一次清理，通过DBA_SCHEDULER_JOBS查看job情况

注意：如果下面的job使用了last_arch_timestamp，但没有手动修改这个保留时间，job会运行但没有清理数据（因为此时数据之前已被清理了），所以才有以上第2）步的job，修改这个保留时间
–AUD$表

BEGIN
DBMS_AUDIT_MGMT.CREATE_PURGE_JOB(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
audit_trail_purge_interval => 168, --7天，168小时
audit_trail_purge_name => 'AUDIT_CLEANUP_JOB',
use_last_arch_timestamp => TRUE);
END;
/

–FGA_LOG$表

BEGIN
DBMS_AUDIT_MGMT.CREATE_PURGE_JOB(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
audit_trail_purge_interval => 168, --7天，168小时
audit_trail_purge_name => 'AUDIT_FGA_CLEANUP_JOB',
use_last_arch_timestamp => TRUE);
END;
/

–禁用job

BEGIN
  DBMS_AUDIT_MGMT.set_purge_job_status(
    audit_trail_purge_name   => 'AUDIT_CLEANUP_JOB',
    audit_trail_status_value => DBMS_AUDIT_MGMT.PURGE_JOB_DISABLE);

–启用job

  DBMS_AUDIT_MGMT.set_purge_job_status(
    audit_trail_purge_name   => 'AUDIT_CLEANUP_JOB',
    audit_trail_status_value => DBMS_AUDIT_MGMT.PURGE_JOB_ENABLE);
END;
/

–修改job执行间隔为2天

BEGIN
  DBMS_AUDIT_MGMT.SET_PURGE_JOB_INTERVAL(
    audit_trail_purge_name     => 'AUDIT_CLEANUP_JOB',
    audit_trail_interval_value => 48);
END;
/

–删除自动清理job

BEGIN
  DBMS_AUDIT_MGMT.drop_purge_job(
     audit_trail_purge_name     => 'AUDIT_CLEANUP_JOB');
END;
/

–查看清理job执行情况，delete_count应该不为0

select * from dba_audit_mgmt_clean_events;