Can you see what I see?

If you’ve got SQL access to your database servers, I want you to tell me the results of the following query (if you’re allowed to) J

Select value from v$parameter where name = ‘audit_trail’;

Go ahead, I’ll wait.

If it’s anything other than DB; DB, EXTENDED; XML or XML, EXTENDED you’re doing yourself and your organization a disservice.

Lately I’ve been amazed at the number of customers I’ve been at who are flabbergasted by “random” changes to their production databases. They’ll say things like “someone logged in and added an index” or “someone changed a stored procedure”. When I ask who did these things – no one can say. Reactions to the resulting production issues usually range from witch-hunts to draconian password lockup procedures.

After the fire cools, the first question I ask is – “Have you turned on database auditing?”

Usually I get an answer from the DBAs saying that auditing isn’t their role – it’s the job of Security. When I ask Security about it they say those kind of issues are an application problems – as long as no data was compromised.

(I love separation of duties – in this case I think there’s a way to combine the peanut butter and chocolate though – maybe have the DBAs and Security leads combine their knowledge / roles to add additional value to the organization)

In every database I can, I ask the DBAs to turn on auditing and run the following commands:

AUDIT TABLE
AUDIT CLUSTER
AUDIT CONTEXT
AUDIT DIMENSION
AUDIT DATABASE LINK
AUDIT DIRECTORY
AUDIT INDEX
AUDIT MATERIALIZED VIEW
AUDIT OUTLINE
AUDIT PROCEDURE
AUDIT PROFILE
AUDIT PUBLIC DATABASE LINK
AUDIT PUBLIC SYNONYM
AUDIT ROLE
AUDIT ROLLBACK SEGMENT
AUDIT SEQUENCE
AUDIT SESSION
AUDIT SYNONYM
AUDIT SYSTEM AUDIT
AUDIT SYSTEM GRANT
AUDIT TABLE
AUDIT TABLESPACE
AUDIT TRIGGER
AUDIT TYPE
AUDIT USER
AUDIT VIEW
AUDIT ALTER, GRANT ON DEFAULT

This way, just about every DDL command run against the database is logged to the audit trail. If it’s set to DB or DB, EXTENDED each command is written to a table in the database. If it’s set to XML or XML, EXTENDED commands are written to XML files in the audit_file_dest directory and also viewable via the DBA_COMMON_AUDIT_TRAIL view.

I LOVE having this on – in development it allows me to track database changes that may need to be promoted to QA. In QA it lets me verify that what I sent from development actually got installed. And in production it gives me an accurate record of what changes were introduced into the production database – by who, when and from where.

I often get some resistance – saying that this will negatively affect performance if every DDL command causes this kind of write activity.

I usually laugh and say – I sure as heck hope so! I want your program that creates “work tables” every second to feel some pain. It’s all part of my plan to Make Bad Practices Painful – I’ve decided to spend less time arguing about Best Practices and more time on hindering the use of Bad Practices…

Seriously though – this is a great built-in feature – much easier than writing DDL triggers…

BTW, Oracle recommends setting this parameter to OS; XML or XML, EXTENDED….

From the 11.2 Security Guide (http://download.oracle.com/docs/cd/E11882_01/network.112/e16543/auditing.htm#BCGBCFAD)

Advantages of the Operating System Audit Trail

Using the operating system audit trail offers these advantages:

Leave a Reply

Posting code can be a pain. To make sure your code doesn't get eaten, you may want to pre-format it first by using HTML Encoder