RE: Oracle Auditing

Oh yeah, I supposed this would help, too:

SELECT *
FROM dba_stmt_audit_opts;

AUDIT_OPTION            SUCCESS FAILURE
ALTER SYSTEM            BY ACCESS       BY ACCESS
TABLE                   BY ACCESS       BY ACCESS
CLUSTER         BY ACCESS       BY ACCESS
TABLESPACE              BY ACCESS       BY ACCESS
USER                    BY ACCESS       BY ACCESS
ROLLBACK SEGMENT        BY ACCESS       BY ACCESS
CREATE USER             BY ACCESS       BY ACCESS
ALTER USER              BY ACCESS       BY ACCESS
DROP USER               BY ACCESS       BY ACCESS
SYNONYM         BY ACCESS       BY ACCESS
VIEW                    BY ACCESS       BY ACCESS
SEQUENCE                BY ACCESS       BY ACCESS
DATABASE LINK           BY ACCESS       BY ACCESS
ROLE                    BY ACCESS       BY ACCESS
PROCEDURE               BY ACCESS       BY ACCESS
ALTER TABLE             BY ACCESS       BY ACCESS
GRANT TABLE             BY ACCESS       BY ACCESS
INDEX                   BY ACCESS       BY ACCESS
SYSTEM AUDIT            BY ACCESS       BY ACCESS
SYSTEM GRANT            BY ACCESS       BY ACCESS
PUBLIC DATABASE LINK    BY ACCESS       BY ACCESS
PUBLIC SYNONYM  BY ACCESS       BY ACCESS
CREATE SESSION  NOT SET BY ACCESS
TRIGGER         BY ACCESS       BY ACCESS

Our dba_obj_audit_opts are negligible...

Rich

Rich Jesse                           System/Database Administrator
[EMAIL PROTECTED]                  Quad/Tech Inc, Sussex, WI USA

> -----Original Message-----
> From: Jesse, Rich
> Sent: Wednesday, June 18, 2003 10:19 AM
> To: '[EMAIL PROTECTED]'
> Subject: RE: Oracle Auditing
>
>
> From our production ERP DB:
>
> SELECT MIN(timestamp), MAX(timestamp), AVG(rowsperday),
> SUM(rowsperday)
> FROM
> (
> SELECT TRUNC(timestamp) "TIMESTAMP", COUNT(*) "ROWSPERDAY"
> FROM dba_audit_trail
> GROUP BY TRUNC(timestamp)
> ) UNION
> SELECT SYSDATE, SYSDATE, 0, SUM(bytes)
> FROM dba_extents de
> WHERE de.segment_name = 'AUD$';
>
>
> 07/28/2001 06/17/2003
> 409.336231884058 282442
> 06/18/2003 06/18/2003 0
> 59039744
>
> (This'll probably need formatting after e-mail mangling.)
>
> So, we have an average of about 410 audit rows per day for
> the last two years, giving us a total of about 280K rows
> which take up a total of about 59MB of storage. Of course,
> we're getting close to 3K rows/day of login failures due to a
> bug with Perl 5.006 and DBD::Oracle with WE8ISO8859P1 NLS_LANGs. :)
>
> HTH! :)
>
> Rich
>
> Rich Jesse System/Database Administrator
> [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA
>
>
> > -----Original Message-----
> > From: Kevin Toepke [mailto:[EMAIL PROTECTED]
> > Sent: Wednesday, June 18, 2003 8:50 AM
> > To: Multiple recipients of list ORACLE-L
> > Subject: Oracle Auditing
> >
> >
> > Greetings and Salutations!
> >
> > At the direction of the external auditors I have been instructed to
> > install/configure oracle auditing in our Oracle Applications
> > databases.
> > (sweet!) The next step is to estimate how much space all of
> > the auditing
> > information will take. (I plan on moving the data from AUD$
> > to another table
> > on a daily basis)
> >
> > Can anyone out there in oracle-l land help me give a
> > reasonable estimate?
> >
> > TIA
> >
> > Kevin Toepke
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jesse, Rich
  INET: [EMAIL PROTECTED]

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).