还是AUD$的问题,昨晚又测了dbms_audit_mgmt过程包给用户设置定时PURGE一段时间前AUD$和FGA_LOG$中的审计记录,该包是专门针对oracle 审计记录进行管理的过程包.该包发布于10203之后的版本,然后在在10203版本需要打上patch 6996030才可以使用该包,在10204版本中需要打上patch 6989148才可以使用.这里使用的是dbms_audit_mgmt的purge以及archived功能
The DBMS_AUDIT_MGMT subprograms enable you to perform cleanup operations on all audit trail types. Audit trail records can be deleted based on their last archive timestamp. The last archive timestamp indicates when the audit records were last archived.
关于该包的使用我就不科普了,到这里dbms_audit_mgmt查找到相关使用信息,做事情需要未雨绸缪,使用前除了了解该包的语法功能点外,还需要注意该包的一些可能问题,该包可能碰到的一些问题可以登陆到OTN查阅 Known Issues When Using: DBMS_AUDIT_MGMT (Doc ID 804624.1)
在下面的步骤前,先了解下这些选项的含义,由于我设置额db_extended所以在过程中我需要选择清除的对象为AUDIT_TRAIL_AUD_STD.
| Constant | Type | Value | Description |
|---|---|---|---|
AUDIT_TRAIL_ALL |
PLS_INTEGER |
15 | All audit trail types. This includes the standard database audit trail (SYS.AUD$ and SYS.FGA_LOG$ tables), operating system (OS) audit trail, and XML audit trail. |
AUDIT_TRAIL_AUD_STD |
PLS_INTEGER |
1 |
Standard database audit records in the SYS.AUD$ table |
AUDIT_TRAIL_DB_STD |
PLS_INTEGER |
3 |
Both standard audit (SYS.AUD$) and FGA audit(SYS.FGA_LOG$) records |
AUDIT_TRAIL_FGA_STD |
PLS_INTEGER |
2 |
Standard database fine-grained auditing (FGA) records in the SYS.FGA_LOG$ table |
AUDIT_TRAIL_FILES |
PLS_INTEGER |
12 | Both operating system (OS) and XML audit trails |
AUDIT_TRAIL_OS |
PLS_INTEGER |
4 | Operating system audit trail. This refers to the audit records stored in operating system files. |
AUDIT_TRAIL_XML |
PLS_INTEGER |
8 | XML audit trail. This refers to the audit records stored in XML files. |
我的测试是在11gR2上执行.我的审计设置为db_extended,AUD$在表空间LUDATOU上.前期准备工作如下:
SQL> alter system set audit_trail=db_extended scope=spfile;
System altered.
SQL> create tablespace ludatou datafile '/u01/oracle/luda01.dbf' size 50m;
Tablespace created.
SQL> BEGIN
2 DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
3 audit_trail_location_value => 'LUDATOU');
4 END;
5 /
PL/SQL procedure successfully completed.
SQL> select segment_name,tablespace_name from dba_segments where segment_name='AUD$';
SEGMENT_NAME TABLESPACE_NAME
-------------------- ------------------------------
AUD$ LUDATOU
update dam_config_param$ set string_value='LUDATOU' where audit_trail_type#=1 and param_id=22;
commit;
SQL> select * from dam_config_param$;
PARAM_ID AUDIT_TRAIL_TYPE# NUMBER_VALUE STRING_VALUE
---------- ----------------- ------------ ----------------------------------------
22 1 LUDATOU
22 2 SYSAUX
21 1 12
第一步针对AUDIT_TRAIL_AUD_STD设置init_cleanup每24小时执行一次.
SQL> BEGIN
2 IF NOT DBMS_AUDIT_MGMT.IS_CLEANUP_INITIALIZED
3 (DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD)
4 THEN
5 dbms_output.put_line('Calling DBMS_AUDIT_MGMT.INIT_CLEANUP');
6 DBMS_AUDIT_MGMT.INIT_CLEANUP(
7 audit_trail_type => dbms_audit_mgmt.AUDIT_TRAIL_AUD_STD,
8 default_cleanup_interval => 24);
9 else
10 dbms_output.put_line('Cleanup for STD was already initialized');
11 end if;
12 end;
13 /
PL/SQL procedure successfully completed.
SQL>
第二步 设置为超过14天审计记录标记时间戳.
这里的时间戳需要注意,如果 AUDIT_TRAIL_TYPE为OS或者XML格式,则需要使用本地系统时间的格式,其他2种格式为UTC.
SQL> begin 2 DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP( 3 audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, 4 last_archive_time => sysdate - 14); 5 end; 6 / PL/SQL procedure successfully completed.
第三步 设置DBMS_AUDIT_MGMT过程中的purge_job
SQL> BEGIN 2 DBMS_AUDIT_MGMT.CREATE_PURGE_JOB ( 3 AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, 4 AUDIT_TRAIL_PURGE_INTERVAL => 24, 5 AUDIT_TRAIL_PURGE_NAME => 'AUD_PURGE', 6 USE_LAST_ARCH_TIMESTAMP => TRUE ); 7 END; 8 / PL/SQL procedure successfully completed.
第四步创建DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP定期推进审计记录的归档时间戳的存储过程.
create or replace procedure set_archive_timestamp (retention in number default 14) as begin DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,last_archive_time => sysdate – retention); end; /
第五步 创建schedul,设置每6小时执行一次存储过程set_archive_timestamp
SQL> BEGIN
2 DBMS_SCHEDULER.create_job (
3 job_name => 'PUSH_AUD_ARCH_TSTAMP',
4 job_type => 'STORED_PROCEDURE',
5 job_action => 'SET_ARCHIVE_TIMESTAMP',
6 number_of_arguments => 1,
7 start_date => SYSDATE,
8 repeat_interval => 'freq=hourly;interval=6',
9 enabled => false,
10 auto_drop => FALSE);
11 End;
12 /
PL/SQL procedure successfully completed.
SQL> BEGIN
2 dbms_scheduler.set_job_argument_value
3 (job_name =>'PUSH_AUD_ARCH_TSTAMP',
4 argument_position =>1,
5 argument_value => 7);
6 DBMS_SCHEDULER.ENABLE('PUSH_AUD_ARCH_TSTAMP');
7 End;
8 /
PL/SQL procedure successfully completed.
设置完成,执行一次.
SQL> BEGIN 2 DBMS_SCHEDULER.run_job (job_name => 'PUSH_AUD_ARCH_TSTAMP', 3 use_current_session => FALSE); 4 END; 5 / PL/SQL procedure successfully completed.
到此为止一个定期purge清除aud$下的审计记录的job就设置好了.中间涉及到DBMS_AUDIT_MGMT过程包的各个知识,在做之前参考前面写的,可能会碰到和引起的问题.该过程主要的作用还是(标准以及细粒度审计)管理审计记录的,不管审计记录是哪种类型,存放在何处,为管理审计提供了便捷,但是在10g同时也带来了一些问题,11gr2版本后审计记录的记录方式发生变化后,各方面功能相对比较稳定完善.
