In test environment DB users are usually given DBA role or CREATE USER
roles, wherein they can create DB schema/user. Overtime this create and delete
of DB users fill up the AUDIT Trails. We would want to purge the Audit trails
of un-existing DB schema.
The Oracle Database is 11g and AUDIT_TRAIL initialization parameter is
set to db(default).
Following is a PL/SQL package with procedure to purge DBA_AUDIT_TRAIL
for entries of deleted users. You can tweak it as per the needs. The package
also includes a procedure to schedule the purge jobs. Note that you may need to
grant appropriate privileges to the user for Data Dictionary view/tables used
in the procedures.
-- Package starts
CREATE OR REPLACE PACKAGE
audit_purge_util AUTHID CURRENT_USER AS
PROCEDURE
proc_clean_user;
PROCEDURE
proc_schedule;
END audit_purge_util;
/
CREATE OR REPLACE PACKAGE BODY
audit_purge_util AS
PROCEDURE proc_clean_user is
lnCount
NUMBER;
purge_date
date;
TYPE
gencur IS REF CURSOR;
user_cur
gencur;
user_rec
VARCHAR2(30);
BEGIN
open
user_cur for SELECT distinct(username) from dba_audit_trail;
LOOP
FETCH
user_cur INTO user_rec;
EXIT
WHEN user_cur%notfound;
SELECT
count(*) INTO lnCount
FROM
DBA_users d
WHERE upper(username)
= upper(user_rec);
IF
lnCount = 0 THEN
dbms_output.put_line(user_rec || ': user
not found, deleting.. ');
delete from sys.aud$ where userid =
user_rec;
-- uncomment below line if you want to
purge other references of the deleted user
-- delete from sys.aud$ where OBJ$NAME =
user_rec;
commit;
END
IF;
END LOOP;
close
user_cur;
END proc_clean_user;
--Procedure to schedule purge
PROCEDURE proc_schedule AS
BEGIN
FOR x IN ( SELECT job_name FROM
DBA_SCHEDULER_JOBS WHERE upper(job_name) = upper('audit_trail_purge'))
LOOP
DBMS_SCHEDULER.DROP_JOB(x.job_name);
COMMIT;
END LOOP;
DBMS_SCHEDULER.create_job (
job_name => 'audit_trail_purge',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN
AUDIT_PURGE_UTIL.proc_clean_user(); END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=WEEKLY;
BYDAY=sun; BYHOUR=0; BYMINUTE=0',
end_date => NULL,
enabled => TRUE,
comments => 'audit_trail_purging..');
END proc_schedule;
END audit_purge_util;
/
-- Package ends
- Testing the procedures:
system@ora11g> create user test123
identified by password;
User created.
system@ora11g> GRANT
connect,resource,create session,select any table,create table to test123;
Grant succeeded.
system@ora11g> conn
test123/password@ora11g
Connected.
test123@ora11g> create table
MYTABLE123 as select object_name, created from user_objects;
Table created.
test123@ora11g> conn
system/system@ora11g
Connected.
system@ora11g> select count(*)
from dba_audit_trail where username = 'TEST123';
COUNT(*)
-------
2
system@ora11g> drop user
test123 cascade;
User dropped.
system@ora11g> @auditPurge.sql
Package created.
Package body created.
system@ora11g> exec
AUDIT_PURGE_UTIL.proc_clean_user();
TEST123: user not found,
deleting..
PL/SQL procedure successfully
completed.
system@ora11g> select count(*)
from dba_audit_trail where username = 'TEST123';
COUNT(*)
-------
0
- Scheduling the purge jobs
system@PEARL11G> exec
AUDIT_PURGE_UTIL.proc_schedule();
PL/SQL procedure successfully
completed.
system@PEARL11G> SELECT
job_action, REPEAT_INTERVAL FROM DBA_SCHEDULER_JOBS WHERE upper(job_name) =
upper('audit_trail_purge');
JOB_ACTION
-------------------------------------------------
REPEAT_INTERVAL
-------------------------------------------------
BEGIN
AUDIT_PURGE_UTIL.proc_clean_user(); END;
FREQ=WEEKLY; BYDAY=sun; BYHOUR=0;
BYMINUTE=0- You can also write to Oracle alert logs by adding the following:
- If you want to delete entries like LOGON, LOGOFF from the audit trail:
No comments:
Post a Comment