Re: audit statement for tracking alter table?

On May 31, 1:13 pm, DA Morgan <damor..._at_psoug.org> wrote:
> 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
> damor..._at_x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>
> - Show quoted text -

Daniel has a good point. If adding and removing privileges is vetoed set up all in-house written applications to run with an username other than the owner. I do not believe DDL needs to be done as part of any production job except for truncate of work/reporting tables and for that you can provide a stored procedure that runs as the object owner (or a DBA) that performs the truncate on specific tables controled via an OK_to_truncate table list. You can provide the necessary functionality while controlling who can perform the task and on what objects using methods like this.

Never grant any privilege to anyone who absolutely does not have to have it to perform their assigned job.

• Mark D Powell --