Default Audit
Oracle 11g turn the standard audit on by default.
DBA_PRIV_AUDIT_OPTS describes current system privileges being audited across the system and by user.
SELECT * from dba_priv_audit_opts
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$')
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$');
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;
/
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$;
select * from DBA_AUDIT_MGMT_CLEAN_EVENTS;
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;
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;
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;
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;
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;
0 comments:
Post a Comment