RE: How do you meet your audit requirement?

Greetings,  

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?  

Thanks.  

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

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jared Still Sent: Thursday, June 26, 2008 2:08 PM
To: ltiu_at_alumni.sfu.ca
Cc: oracle-l_at_freelists.org
Subject: Re: How do you meet your audit requirement?    

On Thu, Jun 26, 2008 at 11:29 AM, Lyndon Tiu <ltiu_at_alumni.sfu.ca> 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:

CREATE OR REPLACE TRIGGER TABLE.LAST_UPDATE_TRG before insert or update on
...  

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:

CREATE OR REPLACE TRIGGER TABLE.LAST_UPDATE_TRG before insert or update on
TABLE
for each row
begin
  if user = 'SCOTT' then null;
  else

: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;
end;
/

-- 
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist


--
http://www.freelists.org/webpage/oracle-l