Why do we need Audit Columns in Database Tables?

Question

I have seen many database designs having following audit columns on all the tables...

• Created By
• Create DateTime
• Updated By
• Upldated DateTime

From one perspective I see tables from the following view...

• Entity Tables:
  • Good candidate for Audit columns)
• Reference Tables:
  • Audit columns may or may not required. In some case last update information is not at all required because record is never going to be modified.)
• Reference Data Tables
  • Like Country Names, Entity State etc... Audit columns may not required because these information is created only during system installation time, and never going to be changed.

I have seen many designers blindly put all audit columns to all tables, is this practice good, if yes what could be the reason...

I just want to know because to me it seems illogical. It is difficult for me to figure out why do they design their db this way? I am not saying they are wrong or right, just want to know the WHY?

You can also suggest me, if there is an alternative auditing patter or solution available...

Thanks and Regards

Answer 1

Data auditing is a required internal control for many business systems (see Sarbanes Oxley for reasons why). It must be at the database level to assure that all changes are captured especially unauthorized ones.

Even with lookup tables an unauthorized change could wreak havok in your system and thus it is important to know who made the change and when. When is especially important because it helps the dbas know how far back to grab a backup to restore information accidentally or maliciously changed.

We like to think all our employees are trustworthy, but many of the thefts of personal data and the malicious changes to destroy company data come from internal sources (this is why is is dangerous to have many disgruntled employees) as does almost all of the fraud. Yet most programmers seem to think that they only have to protect against outside threats.

Of course you are still going to have a few people who can make unauthorized changes, you can't prevent system admins from doing this. But with auditing at least you can limit the potential for data damage (and be especially careful when hiring dbas and allow no one else admin rights on your database servers).