Re: How do you meet your audit requirement?

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_at_ucdavis.edu> 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_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
>

-- 
=========================

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_at_trivadis.com
http://www.trivadis.com

OCP 9i/10g SCSA SCNA
=========================

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