Re: DDL auditing - *Extremely* detailed

From: Tim Gorman <tim_at_sagelogix.com>
Date: Tue, 04 May 2004 11:24:59 -0600
Message-ID: <BCBD2E0B.14509%tim@sagelogix.com>

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_at_dazasoftware.com 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_at_freelists.org
> 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_at_freelists.org 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
-----------------------------------------------------------------

Received on Tue May 04 2004 - 12:25:12 CDT