Re: DDL auditing - *Extremely* detailed

Juan Carlos,

This is from a SQL*Plus script that I use to audit DDL commands only.  I
tend to enable DDL auditing in production databases, because you shouldn't
see DDL in production except during change-management windows (ideally).
Anything that violates that rule should be considered an anomaly worth
investigating.

This SQL*Plus script, named "audit_ddl.sql", is available online at
"http://www.EvDBT.com/tools.htm";.

At the bottom of the script, you'll see the creation of a job in the
DBMS_JOB facility where audit-trail records older than 45 days are deleted.
Of course, that particular setting should be seasoned to taste, but I chose
45 days because it shows two complete month-ends at any one time.  Other
places choose to wait longer (i.e. 180 days or so) to purge audit-trail
data, while some places don't purge at all.  Implicitly, this job is
intended for the setting AUDIT_TRAIL=DB only.

Hope this helps...

-Tim

============== begin text of SQL*Plus script ================
whenever oserror exit failure
whenever sqlerror exit failure
column schema_user format a12
column what format a50
set pagesize 100 linesize 130 trimout on trimspool on
set echo on feedback on timing on

spool audit_ddl

connect / as sysdba

show user
show release

show parameter audit_trail

audit CLUSTER;
audit CONTEXT;
audit DATABASE LINK;
audit DIMENSION;
audit DIRECTORY;
audit INDEX;
audit PROCEDURE;
audit PROFILE;
audit PUBLIC DATABASE LINK;
audit PUBLIC SYNONYM;
audit ROLE;
audit ROLLBACK SEGMENT;
audit SEQUENCE;
REM audit SESSION; -- enable session auditing only if desired
audit SYNONYM;
audit SYSTEM AUDIT;
audit SYSTEM GRANT;
audit TABLE;
audit TABLESPACE;
audit TRIGGER;
audit TYPE;
audit USER;
audit VIEW;
audit ALTER SEQUENCE;
audit ALTER TABLE;
audit COMMENT TABLE;
audit GRANT DIRECTORY;
audit GRANT PROCEDURE;
audit GRANT SEQUENCE;
audit GRANT TABLE;
audit GRANT TYPE;

REM
REM Please set the number of days to retain audit-trail information
REM as desired.  Currently, this script will submit a DBMS_JOB job
REM to purge data from the sys.aud$ table older than 45 days...
REM
REM Job is scheduled to run once per day...
REM
variable jobno number
exec dbms_job.submit(:jobno, 'begin delete from aud$ where timestamp# <
sysdate - 45; end;', sysdate+(1/1440), 'sysdate+1', TRUE)
select  job,
        schema_user,
        what,
        next_date,
        next_sec,
        broken,
        failures
from    user_jobs
where   job = :jobno;

exit success
============== end text of SQL*Plus script ================

on 5/4/04 10:22 AM, Juan Cachito Reyes Pacheco at jreyes@xxxxxxxxxxxxxxxx
wrote:

> Sorry Jesse could you giveme the step by step guide
> to enable ddl auditing if possible, using oracle audit features please.
> I lost in some point when trying to use it.
> 
> 
> Juan Carlos Reyes Pacheco
> OCP
> Database 9.2 Standard Edition
> 
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------

----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: