Re: How do you meet your audit requirement?

I wrote a tool for a customer that is now being redistributed officially by our company (hence I can't share the sources anymore), that does just that.

Audit_trail is set to DB, and we're logging these into aud$. Then, there's a bg process constantly polling that table and sending the data to syslog.

For the SYS auditing:

• I constantly poll the audit_file_dest directory for new files being created
• As soon as the file is no longer opened by a process, it gets parsed and its contents sent to syslog
• All machines are configured to forward all syslog messages from Oracle to a central loghost, courtesy of the security team

There's a couple things about this approach though:

1. If syslog dies, or the network connection goes down, audit entries are lost (the data is sent using UDP, stateless, and in default syslog implementation, no confirmation of any sort is sent back to confirm the message has arrived)
2. You've got an external process running, which gives you some additional "work". It needs to be monitored, and taken into account whenever you start/stop servers, etc.
3. On some platforms, some files are kept open for the duration of the instance's lifetime. You may never really get them in a consistent state until the instance has been shut down.

The big advantage though, is that the audit data is outside the responsibility of the DBAs, and they quite like that.

In 10gR2, Oracle has also implemented audit_syslog_level. A feature that lets the database send audit messages directly to syslog. However, Oracle has forgotten to include the SID in the audit entry. You only get the hostname. That, in most environments renders the feature useless, as many clients run more than one instance on a server. And you just could not tell anymore which instance the message originated from. All you get is the hostname. Funny thing was, after filing an SR and asking for an enhancement request, the answer was along the lines of "Noone is using that feature anyway, log to the database or to the filesystem, not to syslog". Ho-hum!

Cheers

Stefan

On Fri, Jun 27, 2008 at 2:10 AM, Jared Still <jkstill_at_gmail.com> wrote:

> Since you brought it up, I have considered trying to use some external
> software to track the sysdba audit logs.
>
> This could be done with standard object auditing as well, just set
> audit_trail=db.
>
> No one has ever asked me to do this, so I have not attempted this.
>
> Anyone here done auditing this way?
>
>
> On Thu, Jun 26, 2008 at 4:40 PM, Lyndon Tiu <ltiu_at_alumni.sfu.ca> wrote:
>
>> In the OS world, you would log to a syslog running on a different machine.
>> This separate machine is supposedly harder to break into and alter the logs.
>>
>> In the network world, you would monitor network traffic using a sniffer, a
>> machine connected to the network hub (not a switch) without an ip address.
>> The hub would relay all network traffic to this one sniffer box. But since
>> the sniffer box does not have an ip address, it is harder (not impossible)
>> to find and get to.
>>
>> I wonder if there are such features in the DB world?
>>
>> One way would be to store redo logs on a separate hardened machine. This
>> way, all transactions are kept and auditable.
>>
>> Also, have Oracle log to it's *.log and *.trc files on a separate machine.
>>
>> Any other suggestions?
>>
>>
>> --
>> Lyndon Tiu
>> --
>> http://www.freelists.org/webpage/oracle-l
>>
>>
>>
>
>
> --
> 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_at_trivadis.com
http://www.trivadis.com

OCP 9i/10g SCSA SCNA
=========================

--
http://www.freelists.org/webpage/oracle-l