Re: Auditting requirements

From: DA Morgan <damorgan_at_psoug.org>
Date: Wed, 21 Jun 2006 14:20:23 -0700
Message-ID: <1150924829.474705@bubbleator.drizzle.com>

Anoop wrote:
> 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

You are correct that it may be a corporate standard in which case do it rather than lose your job. But it seems like a waste to me.

You can use DDL and SYSTEM Event triggers to:

1. Prevent actions
2. Audit actions
3. Write to the ALERT_LOG (using DBMS_SYSTEM.KSDWRT)
4. Send email notifications (using UTL_MAIL)

It always seems better to me to prevent something bad from happening rather than audit that it did.

-- 
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

Received on Wed Jun 21 2006 - 16:20:23 CDT