Re: How do you meet your audit requirement?

From: Job Miller <>
Date: Fri, 27 Jun 2008 08:24:02 -0700 (PDT)
Message-ID: <>

A little more research led me to this:

The DBMS_AUDIT_MGMT API is currently only part of Audit Vault, documented in the Audit Vault Admin guide here:

However, it will be generally released to the DB customers in the patchset. It is important to manage your audit records properly in order to ensure efficient auditing and clean up.The DBMS_AUDIT_MGMT subprograms enable you to efficiently manage your audit trail records.

The DBMS_AUDIT_MGMT package provides a subprogram that allows you to move the database audit trail tables out of the SYSTEM tablespace. This improves overall database performance. It also allows you to dedicate an optimized tablespace for audit records.

[...] a lot of other functionality for managing audit data...

The DBMS_AUDIT_MGMT subprograms also enable you to configure jobs that periodically delete audit trail records. The frequency with which these jobs should run can be controlled by the audit administrator.


  • On Fri, 6/27/08, Job Miller <> wrote:

From: Job Miller <>
Subject: Re: How do you meet your audit requirement? To:, Cc: "oracle-l" <>
Date: Friday, June 27, 2008, 10:43 AM

While I can't find the documentation for it there was mention of a package in the db that Audit Vault leverages called DBMS_AUDIT_MANAGEMENT.  I don't have an 11g environment to check for this package either, but it wasn't documented in the 11g PL/SQL reference.

The Audit Vault slide deck though says it (AV)
 •Integrates with new DBMS_AUDIT_MANAGEMENT package to automatically delete audit trail records after they have been inserted into Audit Vault.


•Works with aud$/fga_log$, OS, and XML audit trail formats •**Moves the aud$/fga_log$ tables out of the system tablespace  (to the SYSAUX)


So if AV integrates with a new package called DBMS_AUDIT_MANAGEMENT, maybe DBMS_AUDIT_MANAGEMENT provides some useful apis to use in your environment as well.

It wasn't clear to me whether this package has an API that does this move, or that is just something that Audit Vault does for you, but either way, if an Oracle product is moving the data out of SYS to SYSAUX, it lends credibility to it.

In regards to best practices, this paper provides some of Oracle's recommendation for the audit settings in the databases audit vault is collecting from.  Take a look at page 12-13 in this document.
  • On Fri, 6/27/08, Stefan Knecht <> wrote:

From: Stefan Knecht <> Subject: Re: How do you meet your audit requirement? To:
Cc: "oracle-l" <>
Date: Friday, June 27, 2008, 3:23 AM

Oracle Support has officially called this a "grey zone" -- Oracle development won't officially label it supported, but Oracle Support does, and stated so in several SRs.

Just open a quick SR for your own CSI, so you have the statement officially in case things get hairy. And the worst that could happen is when you're running into a BUG that's related to that table, they might ask you to move it back to SYSTEM temporarily to see if the issue still occurs.

AUD$ isn't one of the truly internal tables that are protected by bootstrap$ -- technically, only those are very hard to toy with, most of the others are just plain tables.

As a side note, if you're installing Database Vault (or, more precisely, Label Security) aud$ gets moved to SYSTEM user, and SYS only gets a public synonym. And if you can even put the table in another schema, there's surely nothing technical that would prevent you from putting it into a different tablespace.



On Fri, Jun 27, 2008 at 2:00 AM, William Wagman <> wrote:


This brings up another somewhat related question. In a book on Oracle Security the suggestion was made to put the aud$ table in a separate tablespace as follows…

1)      Create a new tablespace;
2)      Create table audit_temp tablespace <new tablespace> as select * from sys.aud$;
3)      Drop table sys.aud$;
4)      Rename audit_temp to aud$


This would keep the sys.aud$ table from causing the system tablespace to grow inordinately large. Upon mentioning to another person that I was using this technique I was told that it is not a good idea and that Oracle does not support the practice of rebuilding sys tables. That makes good sense to me. The question then is how does one keep the aud$ table from making the system tablespace really huge?


Bill Wagman
Univ. of California at Davis
IET Campus Data Center
(530) 754-6208

From: [] On Behalf Of Jared Still Sent: Thursday, June 26, 2008 2:08 PM
Subject: Re: How do you meet your audit requirement?


On Thu, Jun 26, 2008 at 11:29 AM, Lyndon Tiu <> wrote: We use:

  1. sys.aud$
  2. Each table has a last_updated_date and a last_updated_by column. It gets updated by a trigger:



Those measures only work for accounts that don't have the access to change the audit data.

Quite a number of DBA's have that access.

This method may meet audit requirements, but it will not prevent someone with admin privileges from stealing data, and covering his tracks in the process.

I imagine this story could be repeated in a  number of companies.

That trigger for instance could easily be modified to:

for each row
  if user = 'SCOTT' then null;
     :new.last_updated_date := sysdate;
     :new.last_updated_by := sys_context('USERENV','OS_USER

    ') || ' ' || sys_context('USERENV','HOST') || ' ' || sys_context('USERENV','IP_ADDRESS');
  end if;


Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist


Stefan P Knecht
Senior Consultant
Infrastructure Managed Services

Trivadis AG
Europa-Strasse 5
CH-8152 Glattbrugg

Phone +41-44-808 70 20
Fax +41-808 70 12
Mobile +41-79-571 36 27


Received on Fri Jun 27 2008 - 10:24:02 CDT

Original text of this message