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

The problem you're describing may indicate a more serious problem in the architecture of your system. Usually, users shouldn't even have direct access to the machines running the database.

You may want to consider an architecture where the database machine is separated from your business logic machines, and are accessible only to them.

If your users decide to try to access your servers not through your clients, then all they should be able to do is reach well defined web services that you decided to expose.

There's no reason that a user should be able to access a DB machine, or to have the credentials of an account that is allowed to write to the database. You seem to be worried about tampering with audit information. What's to stop a malicious user from deleting tables or tampering with functional data?

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.

Answer 3

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 4

1. Separate the audit data into its own schema and then set permissions such that the users you're concerned about don't have access to that schema.

2. Use an entirely separate database which could even be on a different machine.

   I often see some type of publish/subscribe model used to publish audit data from a relational database and then asynchronously write that audit data to the audit store.

   Perhaps you could have your triggers write audit data to a queue. Then you could have a scheduled job that runs every few minutes to take audit data from the queue and write it to your audit store.

Answer 5

you could enable Change Tracking so you have kind of "Audit on the audit table".

if your infrastructure is properly managed I guess users do not have sa rights and they use Management Studio to see the database logging in with their windows account, in this case you can set security on that audit table, only sa and other administrative accounts will be able to change content but not normal users/developer accounts.

Hope this helps.