Re: audit statement for tracking alter table?
From: DA Morgan <damorgan_at_psoug.org>
Date: Sat, 31 May 2008 10:13:24 -0700
Message-ID: <1212254015.454095@bubbleator.drizzle.com>
>
> I suggest you also do (at least in production) an "audit table" as
> this will cause an audit record to be written for every create, drop,
> and truncate table performed on your system. You can write a query to
> filter out tables that are truncated as a part of normal production
> batch processing and then quickly look for any unexpected actiivity.
>
> In fact I suggest auditing all Oracle object creation in production:
> index, procedure, view, and any other object types used on your
> system.
>
> HTH -- Mark D Powell --
Date: Sat, 31 May 2008 10:13:24 -0700
Message-ID: <1212254015.454095@bubbleator.drizzle.com>
Mark D Powell wrote:
> On May 30, 5:42 pm, DA Morgan <damor..._at_psoug.org> wrote:
>> rgvguplb wrote: >>> Hi >>> What is the appropriate AUDIT command if you want to track ALTER TABLE >>> statements? >> AUDIT ALTER ANY TABLE BY ACCESS; >> -- >> Daniel A. Morgan >> Oracle Ace Director & Instructor >> University of Washington >> damor..._at_x.washington.edu (replace x with u to respond) >> Puget Sound Oracle Users Groupwww.psoug.org
>
> I suggest you also do (at least in production) an "audit table" as
> this will cause an audit record to be written for every create, drop,
> and truncate table performed on your system. You can write a query to
> filter out tables that are truncated as a part of normal production
> batch processing and then quickly look for any unexpected actiivity.
>
> In fact I suggest auditing all Oracle object creation in production:
> index, procedure, view, and any other object types used on your
> system.
>
> HTH -- Mark D Powell --
I agree. And to go one step further ... once a production schema is built remove CREATE TABLE, CREATE PROCEDURE, etc. privileges so that no one can create anything. Then in any upgrade scripts supply privs, do the work, and promptly revoke them again.
A production schema that contains CREATE privs is a problem waiting to happen.
-- Daniel A. Morgan Oracle Ace Director & Instructor University of Washington damorgan_at_x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.orgReceived on Sat May 31 2008 - 12:13:24 CDT