Home > Oracle MiddleWare > Purge Audit Logs Oracle Data base 11g

Purge Audit Logs Oracle Data base 11g


To comply to SOX and other compliance standards, one must ensure auditing enabled on the oracle database.

I used following commands to enable audit logs on the Oracle 11g R2 data base:

-- Database schema or structure changes
AUDIT ALTER  ANY PROCEDURE BY ACCESS;
AUDIT ALTER  ANY TABLE BY ACCESS;
AUDIT ALTER  ANY DATABASE BY ACCESS;
AUDIT ALTER  ANY SYSTEM BY ACCESS;
AUDIT CREATE ANY JOB BY ACCESS;
AUDIT CREATE ANY LIBRARY BY ACCESS;
AUDIT CREATE ANY PROCEDURE BY ACCESS;
AUDIT CREATE ANY TABLE BY ACCESS;
AUDIT CREATE EXTERNAL JOB BY ACCESS;
AUDIT DROP ANY PROCEDURE BY ACCESS;
AUDIT DROP ANY TABLE BY ACCESS;

-- Database access and privileges
AUDIT ALTER PROFILE BY ACCESS;
AUDIT ALTER USER BY ACCESS;
AUDIT AUDIT SYSTEM BY ACCESS;
AUDIT CREATE PUBLIC DATABASE LINK BY ACCESS;
AUDIT CREATE SESSION BY ACCESS;
AUDIT CREATE USER BY ACCESS;
AUDIT DROP PROFILE BY ACCESS;
AUDIT DROP USER BY ACCESS;
AUDIT EXEMPT ACCESS POLICY BY ACCESS;
AUDIT GRANT ANY OBJECT PRIVILEGE BY ACCESS;
AUDIT GRANT ANY PRIVILEGE BY ACCESS;
AUDIT GRANT ANY ROLE BY ACCESS;
AUDIT ROLE BY ACCESS;

Now after enabling the logs, this is going to create way too many logs and may end up filling the SYSTEM table space.

To ensure you don’t get in to this issue, you need to write a logic to purge the logs.

Here is an interesting article on the net http://www.pythian.com/news/1106/oracle-11g-audit-enabled-by-default-but-what-about-purging/

I am copy pasting the logic for quick reference.

-- Logic for purging
create or replace procedure purge_audit_trail (days in number) as
purge_date date;
begin
purge_date := trunc(sysdate-days);
dbms_system.ksdwrt(2,'AUDIT: Purging Audit Trail until ' ||
purge_date || ' started');
delete from aud$ where ntimestamp# < purge_date;
commit;
dbms_system.ksdwrt(2,'AUDIT: Purging Audit Trail until ' ||
purge_date || ' has completed');
end;
/

-- Job that runs periodically to purge the logs

BEGIN
sys.dbms_scheduler.create_job(
job_name => 'AUDIT_PURGE',
job_type => 'PLSQL_BLOCK',
job_action => 'begin purge_audit_trail(10); end;',
schedule_name => 'MAINTENANCE_WINDOW_GROUP',
job_class => '"DEFAULT_JOB_CLASS"',
comments => 'Audit Trail Purge',
auto_drop => FALSE,
enabled => TRUE);
END;
/

Hope that helps.

About these ads
  1. No comments yet.
  1. September 25, 2014 at 12:39 PM

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: