Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

What are audit tables?

How are they useful?

I came across them reading this article.

share|improve this question
1  
They're just regular tables. It's how you use them and what you put into them that make them "audit tables". –  Nick Chammas Mar 16 '12 at 19:03
1  
They aren't the same. You have an audit table, which is separate from the table you are auditing. So you'd have two tables, NormalTable and AuditNormalTable. –  Thomas Stringer Mar 16 '12 at 19:09
1  
Matt, Shark's got your answer on that. @Shark, I was stressing the point that an audit table is not a special database construct (since I got the impression the OP may believe that). It's just another table the developer builds and uses to track certain information. –  Nick Chammas Mar 16 '12 at 19:21
    
@NickChammas Oh absolutely, I completely understood what you meant. But by the OP's comment I didn't think he did. –  Thomas Stringer Mar 16 '12 at 19:33
    
If you consider the dictionary meaning of audit you'll probably come to the conclusion that "audit table" is a misnomer. An audit should be external to the database: if it is coded by the application business itself, how can it be an audit? –  onedaywhen Mar 19 '12 at 8:38

4 Answers 4

up vote 10 down vote accepted

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.

share|improve this answer
    
Is the Action field the UPDATE or INSERT or DELETE? –  leeand00 Dec 9 '13 at 15:12

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.

share|improve this answer
    
You should add that nothing can be deleted from the audit tables, except under specific situations. –  mrdenny Mar 18 '12 at 7:30
    
Thanks @mrdenny for the great suggestion. Answer edited. –  Joel Brown Mar 18 '12 at 10:59

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.

share|improve this answer
1  
Sadly Change data capture is terrible for auditing because you can't capture the user who made the change (unless you are already storing that in the db). –  HLGEM Mar 16 '12 at 21:34

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.