Re: How do you meet your audit requirement?

  • From: "Stefan Knecht" <knecht.stefan@xxxxxxxxx>
  • To: jkstill@xxxxxxxxx
  • Date: Fri, 27 Jun 2008 09:37:00 +0200

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



On Fri, Jun 27, 2008 at 2:10 AM, Jared Still <jkstill@xxxxxxxxx> 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@xxxxxxxxxxxxx> 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
>> --
> --
> 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


Other related posts: