Preventing spoofing audit tables

Question

We have an audit table in our database. Entries in this table are performed using triggers.

Currently, nothing prevents the user from logging on to the database server, opening the table from the management studio, and changing the data in the audit table.

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

I am going to add one column to the audit table, which should contain some hash calculated based on the values ​​entered on this row. However, since the audit is performed using a trigger, an attacker can open any trigger and see the logic by which this hash is calculated.

EDIT:

I was not clear enough. The application user does not have access to the database. I was referring to some user, such as a database administrator, with the appropriate database permissions. However, if this DBA is logged in and has the right to work with the audit table, I would like to have a mechanism to detect this unauthorized interference.

Answer 1

Nothing can stop anyone from accessing your database through SQL Manager due to content changes. You can make it obvious.

Basically you need to use HMAC , which are key hashes. Unfortunately, this leads to the need to manage keys so that the key remains secret, which may not be possible in triggers. We use a cryptographic service to provide key management, but it is accessed from code.

You also need to think about the ability of users to delete an entry, rather than modify its contents. We ended up with two HMACs, one of which was calculated using the contents of the record (to make the changes to the record obvious), the second - using the current HMAC and HMAC records from the previous line to make the deletion visible.

Then you need to worry about deleting the first or last record x. To do this, we use a trailer and a header record, which always have the same content, if they are missing, the top or bottom of the table has been deleted. The combined HMAC header uses the record after it, not the record before (since there was no record before).

And of course, if you are going to delete old records to control the amount of data that you store, you will need a mechanism to add a new header record after deletion.

Answer 2

Here are a few possibilities:

• You cannot prevent or detect falsification by anyone with sysadmin (sa) permissions. If you do not trust your system administrator, you probably have more serious problems than this particular one.
• It is difficult to prevent or detect fraud using a domain or local administrator. Such a person can restart SQL Server in single user mode and access as sysadmin using SQL.
• To detect falsification of the owner database (dbo), you can use Server Audit in SQL Server 2008 or SQL Server Trace in earlier versions of SQL Server.
• You can prevent other users from interfering by restricting their permissions to appropriate triggers and audit tables.

Answer 3

You can enable change tracking so that you have the "Audit in the audit table" view.

if your infrastructure is properly managed. I think that users do not have sa rights, and they use Management Studio to view the database entry with their Windows account, in this case you can set security in this audit table, only sa and other administrative accounts can change the content but not ordinary user / developer accounts.

Hope this helps.

Answer 4

The problem you are describing may indicate a more serious problem in your system architecture. Typically, users should not have direct access to machines running the database.

You might want to consider an architecture where the database machine is separate from your business logical machines and is accessible only to them.

If your users decide not to try to access your servers through your clients, then all they can do is reach the well-defined web services that you decide to open.

There is no reason why the user should have access to the database machine or have account credentials that are allowed to write to the database. You seem to be worried about forging audit information. What to stop the malicious user from deleting tables or falsification of functional data?

Answer 5

• Separate the audit data in your own schema and then set permissions so that the users you are concerned do not have access to this schema.

• Use a completely separate database, which may even be on another machine.

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

  Perhaps you could force triggers to write audit data to the queue. You can then complete a scheduled task that runs every few minutes to receive audit data from the queue and write it to the audit repository.