DBMS_AUDIT_MGMT.x

Constant

Type

Description

AUDIT_TRAIL_ALL

PLS_INTEGER

All audit trail types. This includes the standard database audit trail (SYS.AUD$, SYS.FGA_LOG$ and unified audit trail tables), operating system (OS) audit trail, and XML audit trail.

AUDIT_TRAIL_AUD_STD

PLS_INTEGER

Standard database audit records in the SYS.AUD$ table

AUDIT_TRAIL_DB_STD

PLS_INTEGER

Both standard audit (SYS.AUD$) and FGA audit(SYS.FGA_LOG$) records

AUDIT_TRAIL_FGA_STD

PLS_INTEGER

Standard database fine-grained auditing (FGA) records in the SYS.FGA_LOG$ table

AUDIT_TRAIL_FILES

PLS_INTEGER

Both operating system (OS) and XML audit trails

AUDIT_TRAIL_OS

PLS_INTEGER

Operating system audit trail. This refers to the audit records stored in operating system files.

AUDIT_TRAIL_UNIFIED

PLS_INTEGER

Unified audit trail. In unified auditing, all audit records are written to the unified audit trail and are made available through the unified audit trail views, such as UNIFIED_AUDIT_TRAIL.

AUDIT_TRAIL_XML

PLS_INTEGER

XML audit trail. This refers to the audit records stored in XML files.

查看Oracle audit相关信息

--配置信息
select * from  DBA_AUDIT_MGMT_CONFIG_PARAMS ;
--清除历史
select * from DBA_AUDIT_MGMT_CLEAN_EVENTS ;
--当前配置的清除JOB
select * from DBA_AUDIT_MGMT_CLEANUP_JOBS ;
--上次归档日期
select * from DBA_AUDIT_MGMT_LAST_ARCH_TS;

--被audit的内容
/*
dba_obj_audit_opts: 
    Data dictionary view will give the details of auditing options on all objects. 
    user_obj_audit_opts view will provide the details of the auditing enabled on the objects on the particular user session connected

dba_priv_audit_opts: 
    Data dictionary view describes the current system privileges being audited across the database and by the user. 
    The column username can be used to find the details user wise. 
    The column value will be NULL for system-wide auditing

dba_stmt_audit_opts: 
    Data dictionary view describes the current system auditing options across the database and by the user. 
    The column username can be used to find the details user wise. The column value will be NULL for system-wide auditing
*/



SQL> alter system set audit_trail = 'DB,EXTENDED'; --没有EXTENDED将不会记录详细的语句
--初始化
--Before you can purge the database audit trail you must perform a one-time initialization 
--of the audit management infrastructure. 
--This is done using the INIT_CLEANUP procedure. The procedure accepts two parameters.
--查看是否已初始化
SET SERVEROUTPUT ON
BEGIN
  IF DBMS_AUDIT_MGMT.is_cleanup_initialized(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD) THEN
    DBMS_OUTPUT.put_line('AUD_STD: YES');
  ELSE
    DBMS_OUTPUT.put_line('AUD_STD: NO');
  END IF;
  IF DBMS_AUDIT_MGMT.is_cleanup_initialized(DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD) THEN
    DBMS_OUTPUT.put_line('FGA_STD: YES');
  ELSE
    DBMS_OUTPUT.put_line('FGA_STD: NO');
  END IF;
END;
/

--初始化
BEGIN
  DBMS_AUDIT_MGMT.init_cleanup(
    audit_trail_type         => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
    default_cleanup_interval => 12 /* hours */);
  DBMS_AUDIT_MGMT.init_cleanup(
    audit_trail_type         => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
    default_cleanup_interval => 12 /* hours */);
END;
/


--set_last_archive_timestamp 设置审计记录上次被归档的时间 , 可以使用clear_last_archive_timestamp来清除该标记
--这是一个确切的时间值,而不是语句。也就是说,如果想保持该值为7天前,需要周期性的设置该值。
begin
  dbms_audit_mgmt.set_last_archive_timestamp(
	audit_trail_type => dbms_audit_mgmt.AUDIT_TRAIL_AUD_STD,
	last_archive_time => systimestamp - 1 ) ;
  dbms_audit_mgmt.set_last_archive_timestamp(
	audit_trail_type => dbms_audit_mgmt.AUDIT_TRAIL_FGA_STD,
	last_archive_time => systimestamp - 1 ) ;
end;  
--自动设置last archive timestamp
--必须确保这些审计类型均被初始化过,否则会报错
BEGIN
  DBMS_SCHEDULER.create_job(job_name        => 'audit_last_archive_time',
                            job_type        => 'PLSQL_BLOCK',
                            job_action      => 'DECLARE
                                                  l_days NUMBER := 7;
                                                BEGIN
                                                  DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.audit_trail_aud_std, TRUNC(SYSTIMESTAMP)-l_days);
                                                  DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.audit_trail_fga_std, TRUNC(SYSTIMESTAMP)-l_days);
                                                  DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.audit_trail_os, TRUNC(SYSTIMESTAMP)-l_days);
                                                  DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.audit_trail_xml, TRUNC(SYSTIMESTAMP)-l_days);
                                                  -- 12cR1 Onwards
                                                  --DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.audit_trail_unified, TRUNC(SYSTIMESTAMP)-l_days);
                                                END;',
                            start_date      => SYSTIMESTAMP,
                            repeat_interval => 'freq=daily; byhour=0; byminute=0; bysecond=0;',
                            end_date        => NULL,
                            enabled         => TRUE,
                            comments        => 'Automatically set audit last archive time.');
END;
/

     

--手动清理
--clean_audit_trail 删除已经被归档的记录
begin
  dbms_audit_mgmt.clean_audit_trail(audit_trail_type => dbms_audit_mgmt.AUDIT_TRAIL_AUD_STD);
  dbms_audit_mgmt.clean_audit_trail(audit_trail_type => dbms_audit_mgmt.AUDIT_TRAIL_FGA_STD);
  dbms_audit_mgmt.clean_audit_trail(audit_trail_type => dbms_audit_mgmt.AUDIT_TRAIL_UNIFIED);
end;  
--设置自动清除任务 
begin
  dbms_audit_mgmt.create_purge_job(audit_trail_type           => dbms_audit_mgmt.AUDIT_TRAIL_AUD_STD ,
                                   audit_trail_purge_interval => 12,
                                   audit_trail_purge_name     => 'aud_purge_aud_std',
                                   use_last_arch_timestamp    => true);  --如果为False,则会purge所有的记录
  dbms_audit_mgmt.create_purge_job(audit_trail_type           => dbms_audit_mgmt.AUDIT_TRAIL_FGA_STD,
                                   audit_trail_purge_interval => 12,
                                   audit_trail_purge_name     => 'aud_purge_fga_std',
                                   use_last_arch_timestamp    => true);  --如果为False,则会purge所有的记录
  dbms_audit_mgmt.create_purge_job(audit_trail_type           => dbms_audit_mgmt.AUDIT_TRAIL_UNIFIED,
                                   audit_trail_purge_interval => 12,
                                   audit_trail_purge_name     => 'aud_purge_unified',
                                   use_last_arch_timestamp    => true);  --如果为False,则会purge所有的记录
end;

  • No labels