Re: How do you meet your audit requirement?
- From: "Stefan Knecht" <knecht.stefan@xxxxxxxxx>
- To: wjwagman@xxxxxxxxxxx
- Date: Fri, 27 Jun 2008 09:23:23 +0200
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.
Cheers
Stefan
On Fri, Jun 27, 2008 at 2:00 AM, William Wagman <wjwagman@xxxxxxxxxxx>
wrote:
> 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@xxxxxxxxxxx
> (530) 754-6208
>
> *From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto:
> oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Jared Still
> *Sent:* Thursday, June 26, 2008 2:08 PM
> *To:* ltiu@xxxxxxxxxxxxx
> *Cc:* oracle-l@xxxxxxxxxxxxx
> *Subject:* Re: How do you meet your audit requirement?
>
>
>
>
>
> On Thu, Jun 26, 2008 at 11:29 AM, Lyndon Tiu <ltiu@xxxxxxxxxxxxx> 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
>
--
=========================
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
stefan.knecht@xxxxxxxxxxxx
http://www.trivadis.com
OCP 9i/10g SCSA SCNA
=========================
Other related posts:
