Friday, March 1, 2013

0 Default Audit and DBMS_AUDIT_MGMT

 

Default Audit

Oracle 11g turn the standard audit on by default.

image

DBA_PRIV_AUDIT_OPTS describes current system privileges being audited across the system and by user.

SELECT * from dba_priv_audit_opts 

image
The standard audit information is store in the AUD$ table. The DBA_AUDIT_TRAIL view is base on the AUD$. The AUD$ by default is store on the SYSTEM table space and in time, it may grow substantially so we may need to create the job to purge it or move to the separate table space. 

SELECT table_name,
  tablespace_name
FROM dba_tables
WHERE table_name IN ('AUD$', 'FGA_LOG$')

image

DBMS_AUDIT_MGMT

From 11g, Oracle provides the new package DBMS_AUDIT_MGMT to simplify the audit trail maintained work

Move the AUX$ to the separate table space.

CREATE TABLESPACE AUX_AUDIT
  DATAFILE '/u01/app/oracle/oradata/oradb0/AUX_AUDIT01.dbf' SIZE 50M AUTOEXTEND ON NEXT 1M;
 
BEGIN
  DBMS_AUDIT_MGMT.set_audit_trail_location(
    audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
    audit_trail_location_value => 'AUX_AUDIT');
END;
/
SELECT table_name,
  tablespace_name
FROM dba_tables
WHERE table_name IN ('AUD$', 'FGA_LOG$');

image

Initiate the clean up type

The type list can be found in the oracle document. In this demo, I want to initialize the standard audit . The default_clean_up_interval parameter does not seem to be implement . We have to schedule the purge job manually.

BEGIN
  DBMS_AUDIT_MGMT.init_cleanup(
    audit_trail_type         => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
    default_cleanup_interval => 12 /* hours */);
END;
/

image

Manually Purge the log

  • last_archive_time means the purge log older than this time.
  • DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP –> Setup the last archive timestamp.
  • DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL –> manually clean up the trail

As you can see below demo, I setup the archive_time to be older than current date then run the clean _audit_trail. The AUD$ original has 23673 records. After the purge, it becomes 12259. The cleanup result can be retrieve from DBA_AUDIT_MGMT_CLEAN_EVENTS view.

 

col audit_trail FORMAT A20
col last_archive_ts FORMAT A40
SELECT * FROM dba_audit_mgmt_last_arch_ts;
 
BEGIN
  DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
    audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
    last_archive_time => SYSTIMESTAMP-7);
END;
/
SELECT * FROM dba_audit_mgmt_last_arch_ts;
select SYSTIMESTAMP from dual;
SELECT COUNT(*) FROM aud$;
 
BEGIN
  DBMS_AUDIT_MGMT.clean_audit_trail(
   audit_trail_type        => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
   use_last_arch_timestamp => TRUE);
END;
/
 
SELECT COUNT(*) FROM aud$;

image

select * from DBA_AUDIT_MGMT_CLEAN_EVENTS;

image

Auto purge the log

 

BEGIN
  DBMS_AUDIT_MGMT.CREATE_PURGE_JOb(
    audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
    audit_trail_purge_interval => 24 /* hours */,  
    audit_trail_purge_name     => 'PURGE_STD_AUDIT_TRAILS',
    use_last_arch_timestamp    => TRUE);
END;
/
 
SELECT job_action
FROM   dba_scheduler_jobs
WHERE  job_name = 'PURGE_STD_AUDIT_TRAILS';
select * from DBA_AUDIT_MGMT_CLEANUP_JOBS;

image

Disable the purge job

 
BEGIN
  DBMS_AUDIT_MGMT.set_purge_job_status(
    audit_trail_purge_name   => 'PURGE_STD_AUDIT_TRAILS',
    audit_trail_status_value => DBMS_AUDIT_MGMT.PURGE_JOB_DISABLE);
END;
/
col JOB_NAME FORMAT A30
col JOB_STATUS FORMAT A10
col JOB_FREQUENCY FORMAT A30
select JOB_NAME,JOB_STATUS,JOB_FREQUENCY from DBA_AUDIT_MGMT_CLEANUP_JOBS;

image

Enable the purge job

BEGIN
  DBMS_AUDIT_MGMT.set_purge_job_status(
    audit_trail_purge_name   => 'PURGE_STD_AUDIT_TRAILS',
    audit_trail_status_value => DBMS_AUDIT_MGMT.PURGE_JOB_ENABLE);
END;
/
select JOB_NAME,JOB_STATUS,JOB_FREQUENCY from DBA_AUDIT_MGMT_CLEANUP_JOBS;

image

Set the interval for the purge job

BEGIN
  DBMS_AUDIT_MGMT.SET_PURGE_JOB_INTERVAL(
    audit_trail_purge_name     => 'PURGE_STD_AUDIT_TRAILS',
    audit_trail_interval_value => 48);
END;
/
select JOB_NAME,JOB_STATUS,JOB_FREQUENCY from DBA_AUDIT_MGMT_CLEANUP_JOBS;

image

Remove the purge job

BEGIN
  DBMS_AUDIT_MGMT.drop_purge_job(
     audit_trail_purge_name     => 'PURGE_STD_AUDIT_TRAILS');
END;
/
 
col JOB_NAME FORMAT A30
col JOB_STATUS FORMAT A10
col JOB_FREQUENCY FORMAT A30
select JOB_NAME,JOB_STATUS,JOB_FREQUENCY from DBA_AUDIT_MGMT_CLEANUP_JOBS;

image

 

Reference

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates