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;