Prevent audit table tampering

Question

We have audit table in our database. Records to this table are done using triggers.

Currently, there is nothing that prevents user to log on to database server, open table from management studio and change data in audit table.

What are possible mechanisms that can prevent (or at least detect) cases of audit data tampering?

I'm thinking of adding one column in audit table which should contain some hash calculated based on values that are entered in that row. However, since audit is done using trigger, malicious user could open any trigger and see the logic by which this hash is calculated.

EDIT:

I was not clear enough. Application user does not have access to database. I was referring to some user like DB admin, with appropriate rights on database. Still, if this DB admin logins and has rights to temper with audit table, I would like to have some mechanism to detect this tampering at least.

Answer 1

Nothing can prevent someone accessing your database via SQL manager from changing the contents. You can make it tamper evident though.

Basically you need to use HMACs which are keyed hashes. Unfortunately this leads you to requiring key management to ensure the key stays secret which may not be possible in triggers. We use a cryptographic service to provide the key management but this is accessed from code.

You also need to think about a users ability to delete a record rather than change its contents. We ended up with two HMACs, one calculated using the contents of the record (to make changes to a record evident), the second using the current records HMAC and the HMAC from the previous line to make any line deletion tamper evident.

Then you need to worry about deleting the first or last x records. For this we use a trailer and header record which always have the same contents, if those aren't present then the top or the bottom of the table has been deleted. The combined HMAC of the header uses the record after it rather than the record before (as there is no record before).

And, of course, if you are going to be deleting old records to manage the amount of data you store you'll need a mechanism to add a new header record after the deletion.

Answer 2

Here are some possibilities:

• You can't prevent or detect tampering by somebody with sysadmin (sa) permissions. If you don't trust your system administrator, you probably have worse problems than this specific one.
• It's difficult to prevent or detect tampering by a domain or local administrator. Such a person can restart SQL Server in single-user mode and gain access as a sysadmin using SQL.
• To detect tampering by the database owner (dbo), you could use Server Audit in SQL Server 2008 or a server-side SQL Trace in earlier versions of SQL Server.
• You can prevent tampering by other users by restricting their permissions to the relevant triggers and audit tables.