6

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.

buhtla
  • 2,819
  • 4
  • 25
  • 38
  • 2
    How about not giving the malicious user write access to that table? Or if you're paranoid send the log to a different computer where few people have access instead of using a table in the database. – CodesInChaos Jan 21 '11 at 10:16
  • 2
    Just restrict the permissions so that only certain logins have insert/update/delete permissions on that table. – LukeH Jan 21 '11 at 10:17
  • They can only use Management Studio to bypass the permissions if they log in as a sysadmin, explicitly or implicitly (such as by logging into Windows as Administrator). Strong SQL security depends on good network security habits. – Bruce May 16 '11 at 18:24

5 Answers5

9

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.

Patrick
  • 8,175
  • 7
  • 56
  • 72
  • This looks to me like complex but almost bullet proof concept :). Are you doing audit from code or via triggers? I presume you are doing audit from code. If you do, then there is nothing that prevents user from changing data in some table which you are auditing from code, and this would leave no evidence in your audit table, right? – buhtla Jan 21 '11 at 10:44
  • We audit from code. It can't be changed from the code running on the server (i.e. the code we write) although if an attacker was able to run arbitrary code on the server and understood how to access the crypto service etc they could write what they liked to the audit table. Of course if an attacker had that level of access to the server and spent their time messing around with audit tables they'd be wasting a valuable opportunity to steal data from us... – Patrick Jan 21 '11 at 11:14
  • 1
    "Nothing can prevent someone accessing your database via SQL manager from changing the contents." This is only true if they log into SQL Management Studio as a sysadmin. While this is typical, it isn't required. If their login denies them access to the object, connecting via SQL Management Studio won't give them extra access. – Bruce May 16 '11 at 18:21
  • Great answer, thanks. Can anyone clarify how to calculate the hash for the first row? – Can Bud Jul 06 '15 at 09:22
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.
HTTP 410
  • 17,300
  • 12
  • 76
  • 127
1

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.

Davide Piras
  • 43,984
  • 10
  • 98
  • 147
  • Thanks for reply. I edited my question to be more precise. Ordinary application user of course does not have DB access. – buhtla Jan 21 '11 at 10:35
0

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?

Ran
  • 5,989
  • 1
  • 24
  • 26
  • Hey, I edited my question, I was not clear enough with terminology. We do have separate machines for DB and business logic. – buhtla Jan 21 '11 at 10:29
0

  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.

Daniel Rehner
  • 1,771
  • 12
  • 8