What is an audit table?

Question

What are audit tables?

How are they useful?

I came across them reading this article.

Answer 1

Audit Tables are used to track transactions against a particular table or tables. They allow you to see an ongoing "log" (for lack of a better word). For instance, say you have a table like this:

create table SensitiveInformation
(
    SensitiveNumber int not null,
    SensitiveData varchar(100) not null
)
go

There may be users and/or applications that have access to insert, update, and delete out of that table. But due to the sensitive nature of that data, you may want to have a quick and easy way to track who is doing what on that table.

So you have an Audit Table to track what is being done on that table. Typically that'll include the basic Who, What, When.

An audit table could look like this:

create table SensitiveInformationAudit
(
    SensitiveNumberNew int null,
    SensitiveNumberOld int null,
    SensitiveDataNew varchar(100) null,
    SensitiveDataOld varchar(100) null,
    Action varchar(50) not null,
    AuditDate datetime not null,
    LastUpdatedUser varchar(100) not null
)
go

Audit Tables are typically filled through the use of Database Triggers. In other words, when X action happens on SensitiveInformation, insert the details of it in SensitiveInformationAudit.

Answer 2

One other important aspect of audit tables that hasn't been highlighted thus far, is that in addition to keeping track of who did what to which record (often including before and after snapshots) audit tables are write-once.

Records in an audit table may not be updated or deleted (see note), only inserted. This is sometimes imposed using triggers or maybe just application logic, but it's important in practice because it gives you "proof" that nothing's been tampered with in a way which is difficult to detect.

Note: Cleaning out old records from an audit table requires special processes which often have to be approved by management or auditors.

Answer 3

Audit tables are generally used when you want to track changes in sensitive/confidential tables. If there is a table that is used for pay rate and bonus percentage, and the HR application remunerates salaries based on this data, then a user with write access to this table can make unauthorized payment modifications.

At the same time, some users should be allowed to work on these tables. This is where audit tables come in. Audit tables can be used to track the before and after value of the changed data. Usually they also save extra information like the person who did the change and the time at which the change was done.

Hence these audit tables deter empowered users from performing unauthorized activities. They also provide a means to revert back to the correct values.

SQL 2008 and above have a built in feature called Change Data Capture that can be used for this.

Answer 4

Audit tables are used by native or 3rd party auditing tools that capture data changes that have occurred on a database, usually including the information on who made the change, which objects were affected by it, when it was made as well as the information on the SQL login, application and host used to make the change. All captured information is stored in the auditing tables and should be available in user friendly formats via exporting or querying.