How to Audit Database Activity without Performance and Scalability Issues?

Question

I have a need to do auditing all database activity regardless of whether it came from application or someone issuing some sql via other means. So the auditing must be done at the database level. The database in question is Oracle. I looked at doing it via Triggers and also via something called Fine Grained Auditing that Oracle provides. In both cases, we turned on auditing on specific tables and specific columns. However, we found that Performance really sucks when we use either of these methods.

Since auditing is an absolute must due to regulations placed around data privacy, I am wondering what is best way to do this without significant performance degradations. If someone has Oracle specific experience with this, it will be helpful but if not just general practices around database activity auditing will be okay as well.

Answer 1

I'm not sure if it's a mature enough approach for a production system, but I had quite a lot of success with monitoring database traffic using a network traffic sniffer.

Send the raw data between the application and database off to another machine and decode and analyse it there.

I used PostgreSQL, and decoding the traffic and turning it into a stream of database operations that could be logged was relatively straightforward. I imagine it'd work on any database where the packet format is documented though.

The main point was that it put no extra load on the database itself.

Also, it was passive monitoring, it recorded all activity, but couldn't block any operations, so might not be quite what you're looking for.

Answer 2

There is no need to "roll your own". Just turn on auditing:

1. Set the database parameter AUDIT_TRAIL = DB.
2. Start the instance.
3. Login with SQLPlus.
4. Enter the statement

   audit all;

   This turns on auditing for many critical DDL operations, but DML and some other DDL statements are still not audited.
5. To enable auditing on these other activities, try statements like these:

   audit alter table; -- DDL audit
   audit select table, update table, insert table, delete table; -- DML audit

Note: All "as sysdba" activity is ALWAYS audited to the O/S. In Windows, this means the Windows event log. In UNIX, this is usually $ORACLE_HOME/rdbms/audit.

Check out the Oracle 10g R2 Audit Chapter of the Database SQL Reference.

The database audit trail can be viewed in the SYS.DBA_AUDIT_TRAIL view.

It should be pointed out that the internal Oracle auditing will be high-performance by definition. It is designed to be exactly that, and it is very hard to imagine anything else rivaling it for performance. Also, there is a high degree of "fine-grained" control of Oracle auditing. You can get it just as precise as you want it. Finally, the SYS.AUD$ table along with its indexes can be moved to a separate tablespace to prevent filling up the SYSTEM tablespace.

Kind regards, Opus