We know that most certifications require some form of auditing in your database applications. We take that for granted. Some certifications are more stringent. PCI certification, for example, requires that you not only audit access through your application, but, you must also audit ad-hoc activity. There are a few different ways you perform this kind of auditing.
Why is this important? I have read frequently from security folks that most attacks on your system come from internal users. I don’t know how true that is. However, when external users attack your system, I would guess that they are able to appear as an internal user; so what really is the difference.
Here are a few I have used. I am sure there are more; perhaps you’ll be helpful and share your knowledge in our comments.
Database Triggers are a great tool for auditing the modification of database objects through keywords such as CREATE, ALTER or DROP. I created a database logging these events, and have found it useful for debugging release issues, or the untimely change by a well meaning DBA.
You can create a Database Trace that starts up when the SQL Server processes is started. Even if a user turns off the trace, you have a log of someone turning it off. You’re probably thinking that a trace of all database access would be huge. I found that you can filter all application access, since that is logged elsewhere, thus only logging ad-hoc access, which is the real activity you’re trying to capture anyway. Using a rolling log file keeps any single file from becoming too large. Especially on a production database where ad-hoc queries ought to be more limited.
There are always third party tools available.
Why not share your auditing expertise with your comments?
Cheers,
Ben