4

I am currently tasked with developing a action plan for monitoring DB activity, encompassing general actions (failed logons etc) with some finer grained monitoring(e.g. who selected from table x, what was the SQL statement etc)

During the course of investigating what could be done, simply turning on and tuning Oracle DB auditing seemed to hit most (if not all) of the requirements, albeit with the caveat that performance would need to be considered carefully)

But ideally I would like to go back with a few proposals to discuss, my other thoughts would be to produce triggers for the tables and send them off to some other 'Audit' schema but this smacks of re-inventing the wheel.

So I was wondering if anyone had any experience around this area and could point in the direction of further reading/suggestions for any other viable alternatives

Dean
  • 163
  • 1
  • 8

2 Answers2

3

Your caveat that performance would need to be monitored carefully would be true regardless of the solution that is finally settled upon. Bottom line is that the process of capturing and saving audit data is going to cost you resources (I/O, CPU, storage, etc.).

You state that the built-in Oracle auditing hit most, if not all your requirements. Why does an alternative need to be considered? Think about the fact that Oracle has spent considerable effort in optimizing auditing, by people who are familiar with Oracle internals. I seriously doubt most organizations have the resources to do it better. If you roll your own, or even go with some 3rd party solution, be VERY prepared to find your organization supporting auditing, rather than auditing supporting your organization.

If it were me, I would recommend using built-in technology and if necessary, building something in-house that dealt with the requirements you have that Oracle Auditing does not satisfy. You might also consider how important such requirements really are.

That said, there are a number of 3rd party tools out there. A quick Google search yields several possibilities. I noticed one (DB Tools for Oracle) works by generating triggers for you.

DCookie
  • 2,098
  • 1
  • 17
  • 18
  • +1 Thanks for the advice. It helped reinforce my thoughts. If I could I would accept both answers! – Dean Apr 15 '11 at 09:51
3

If you can do it with the AUDIT command, I would stick to it. It is unlikely that you will be able to achieve performance improvements with triggers.

If you need more AUDIT features however, you could look into Fine Grained Auditing. It offers a wider set of rules and features for auditing. As of Oracle 11g, this is an Entreprise Edition feature.

Vincent
  • 395
  • 1
  • 9