Re: Auditting requirements

Anoop wrote:
> DA Morgan wrote:
> > Anoop wrote:
> > > We have a project where we randomly create oracle accounts based on
> > > user requests. The requirement is that as soon as a user is created we
> > > need to enable auditting for the newly created user.
> > >
> > > To enable auditting we need to run commands like this:
> > >
> > > audit all by <acct> by access;
> > > audit alter sequence by <indiv acct> by access;
> > > audit alter table by <acct> by access;
> > > audit comment table by <cct> by access;
> > > audit grant procedure by <acct> by access;
> > > audit grant sequence by <acct> by access;
> > > audit grant table by <acct> by access;
> > > audit grant type by <acct> by access;
> > > audit lock table by <acct> by access;
> > >
> > > What we are thinking of doing is to program this into our code where
> > > the user gets created.
> > > But we have a lot of databases to which we will hav eto connect to and
> > > run these commands as a procedure.
> > > Hence, I want to explore if that can be done using oracle auditting
> > > commands. Like can iI enable auditting for all users by default (or a
> > > subset) so that a new user created will have auditting enabled whenever
> > > his account gets created? Or can I have the above commands run as soon
> > > as (after) a user-account is created? Is that possible?
> > > It wd be great if someone can help me as I am a n00b in this field.
> > >
> > > Thanks in advance,
> > > Anoop
> >
> > And your Oracle version is?
> >
> > If not some Paleolithic unsupported version this is all unnecessary.
> >
> > You can audit DDL with DDL event triggers.
> > www.psoug.org / click on Morgan's Library / click on DDL Event Triggers
> > Nobody ever comments tables so that's a waste ;-)
> > Why are you auditing table locks?
> >
> > Seems like a lot of work for something that could be done with a single
> > trigger.
> > --
> > Daniel A. Morgan
> > University of Washington
> > damorgan_at_x.washington.edu
> > (replace x with u to respond)
> > Puget Sound Oracle Users Group
> > www.psoug.org
>
>
>
> Ohh Yes - my Oracle version is 9i.
>
> This is fantastic, I never knew that there are DDL event triggers -
> only DML triggers. I think this is most useful - thank you.
>
> -Anoop

Hmm - about your other comment that it is a waste.. I do not have much of an idea, they might be corporate DBA standards.. Actually that was a pruned down version. here is the entire procedure of queries to run:

audit all by <acct> by access;
audit alter sequence by <acct> by access; audit alter table by <acct> by access;
audit comment table by <acct> by access; audit grant procedure by <acct> by access; audit grant sequence by <acct> by access; audit grant table by <acct> by access;
audit grant type by <acct> by access;
audit lock table by <acct> by access;
audit debug procedure by <acct> by access; audit network by <acct> by access;
audit on commit refresh by <acct> by access; audit sysdba by <acct> by access;
audit sysoper by <acct> by access;
audit all privileges by <acct> by access; audit alter any operator by <acct> by access; audit exempt access policy by <acct> by access;

Do you see any else as a big red - i mean did you mean it as a performance overhead, serious?

Thanks,
Anoop Received on Wed Jun 21 2006 - 15:18:02 CDT