Re: How do you meet your audit requirement?

Oracle recommends doing an alter table move on the aud$ table to a non-system tablespace. That recommendation has been around for some time (at least 3 years). You dont need to rename it or anything.

On Fri, Jun 27, 2008 at 6:10 PM, William Wagman <wjwagman_at_ucdavis.edu> wrote:

> Stefan,
>
>
>
> I will open an SR and post the results.
>
>
>
> Thanks.
>
>
>
> Bill Wagman
> Univ. of California at Davis
> IET Campus Data Center
> wjwagman_at_ucdavis.edu
> (530) 754-6208
>
> *From:* Stefan Knecht [mailto:knecht.stefan_at_gmail.com]
> *Sent:* Friday, June 27, 2008 12:23 AM
> *To:* William Wagman
> *Cc:* oracle-l
> *Subject:* 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
> =========================
>

-- 
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'

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