Home » RDBMS Server » Security » how to implement audit purge ? (oracle 11gr2, linux 6.4)
how to implement audit purge ? [message #611236] Mon, 31 March 2014 01:07
kesavansundaram
Messages: 183
Registered: October 2007
Location: MUMBAI
Senior Member
Team,
I am working on Amazon RDS Oracle instance. I need to implement audit purge script. this .sql script will be called thro python code on daily bais and it should retain recent 7 days records and purge older than 7 days.

I have implemented below. I have tested. it works well.

DELETE from SYS.AUD$ where trunc(NTIMESTAMP# ) < trunc(sysdate -7);


Above is the recommended one to implement purge or should i write using DBMS pkg ?

--for e.g. below one will exeucte purge job in every 24 hrs
BEGIN
DBMS_AUDIT_MGMT.create_purge_job(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
audit_trail_purge_interval => 24 /* hours */,
audit_trail_purge_name => 'PURGE_ALL_AUDIT_TRAILS',
use_last_arch_timestamp => TRUE);
END;
/

---with retention period : 90 days
BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'audit_last_archive_time',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, TRUNC(SYSTIMESTAMP)-90);
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD, TRUNC(SYSTIMESTAMP)-90);
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS, TRUNC(SYSTIMESTAMP)-90);
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML, TRUNC(SYSTIMESTAMP)-90);
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;
/


which one is the best ?

Please guide me

Thank you

[Updated on: Mon, 31 March 2014 01:12]

Report message to a moderator

Previous Topic: How to restrict schema access to specfic IP address
Next Topic: Passwordfile authentication
Goto Forum:
  


Current Time: Thu Mar 28 10:42:54 CDT 2024