RE: How do you meet your audit requirement?
- From: "William Wagman" <wjwagman@xxxxxxxxxxx>
- To: "William Wagman" <wjwagman@xxxxxxxxxxx>, "Andrew Kerber" <andrew.kerber@xxxxxxxxx>
- Date: Tue, 1 Jul 2008 10:49:43 -0700
Greetings,
I did find three notes on Metalink addressing this problem, 1019377.6, 72460.1
& 166301.1 with somewhat indefinite answers. I opened an SR and this is the
answer I received from Oracle.
You are correct there is a patch being worked on but until that patch is
released Oracle does not officially support moving the aud$ table into a
tablespace other than system. It does seem a little contradicting and I do
apologize for that, when the patch is
released then you would be able to move the table and still get support on it.
As for the other note 1019377.6 it does state as a disclaimer at the bottom of
the note that this is for educational purposes only and is not supported by
Oracle. As a recap Oracle does not support moving the aud$ table into a
tablespace other than system. Oracle suggests to wait for the patch.
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 William Wagman
Sent: Friday, June 27, 2008 4:35 PM
To: Andrew Kerber
Cc: Stefan Knecht; oracle-l
Subject: RE: How do you meet your audit requirement?
Andrew,
Thanks for the info. The question arose because at one point I was told that
Oracle didn't support that. Several folks suggested opening an SR and asking
them directly. I will do that and report back.
Thanks.
Bill Wagman
Univ. of California at Davis
IET Campus Data Center
wjwagman@xxxxxxxxxxx
(530) 754-6208
From: Andrew Kerber [mailto:andrew.kerber@xxxxxxxxx]
Sent: Friday, June 27, 2008 4:30 PM
To: William Wagman
Cc: Stefan Knecht; oracle-l
Subject: 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 don't need to rename it or anything.
On Fri, Jun 27, 2008 at 6:10 PM, William Wagman <wjwagman@xxxxxxxxxxx> wrote:
Stefan,
I will open an SR and post the results.
Thanks.
Bill Wagman
Univ. of California at Davis
IET Campus Data Center
wjwagman@xxxxxxxxxxx
(530) 754-6208
From: Stefan Knecht [mailto:knecht.stefan@xxxxxxxxx]
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@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
=========================
--
Andrew W. Kerber
'If at first you dont succeed, dont take up skydiving.'
--
//www.freelists.org/webpage/oracle-l
Other related posts:
