Re: How do you meet your audit requirement?

From: Jared Still <jkstill_at_gmail.com>
Date: Thu, 26 Jun 2008 14:07:44 -0700
Message-ID: <bf46380806261407j1139f95ci9ed2ca78be4e2e8@mail.gmail.com>

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

Received on Thu Jun 26 2008 - 16:07:44 CDT