防止审计表被篡改 [英] Prevent audit table tampering

问题描述

我们的数据库中有审核表. 对该表的记录是使用触发器完成的.

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

当前，没有什么可以阻止用户登录数据库服务器，从Management Studio打开表以及更改审核表中的数据.

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.

我还不够清楚.应用程序用户无权访问数据库.我指的是像DB admin这样的用户，它对数据库具有适当的权限.不过，如果该数据库管理员登录并有权使用审计表进行调整，那么我希望有某种机制至少可以检测到这种篡改.

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.

推荐答案

没有什么可以阻止通过SQL管理器访问您的数据库的人更改内容.不过，您可以将其篡改.

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

基本上，您需要使用作为键哈希的 HMAC .不幸的是，这导致您需要密钥管理以确保密钥保持机密，而这在触发器中是不可能的.我们使用加密服务来提供密钥管理，但这可以通过代码进行访问.

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.

您还需要考虑用户删除记录而不是更改其内容的能力.我们以两个HMAC结尾，一个HMAC使用记录的内容进行计算(以使对记录的更改显而易见)，第二个HAMP使用当前的记录HMAC和前一行的HMAC来使任何行删除篡改均显而易见.

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.

然后，您需要担心删除第一个或最后一个x记录.为此，我们使用始终具有相同内容的预告片和标头记录，如果不存在这些内容，则表的顶部或底部将被删除.标头的组合HMAC使用它之后的记录，而不是之前的记录(因为之前没有记录).

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.

这篇关于防止审计表被篡改的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！