我们的数据库中有审计表。
该表的记录是使用触发器完成的。
目前，没有什么可以阻止用户登录数据库服务器、从 Management Studio 打开表以及更改审计表中的数据。
有哪些可能的机制可以防止（或至少检测）审计数据篡改情况？
我正在考虑在审核表中添加一列，其中应包含一些根据该行中输入的值计算的哈希值。但是，由于审核是使用触发器完成的，因此恶意用户可以打开任何触发器并查看计算此哈希的逻辑。
编辑：
我还不够清楚。应用程序用户无权访问数据库。我指的是像数据库管理员这样的用户，对数据库具有适当的权限。不过，如果这个数据库管理员登录并有权修改审计表，我希望至少有一些机制来检测这种篡改。
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.
发布评论
评论（5）
没有什么可以阻止某人通过 SQL 管理器访问您的数据库来更改内容。不过你可以让它显露出来。
基本上，您需要使用 HMAC，它们是带密钥的哈希值。不幸的是，这会导致您需要密钥管理来确保密钥保密，而这在触发器中可能是不可能的。我们使用加密服务来提供密钥管理，但这是通过代码访问的。
您还需要考虑用户删除记录而不是更改其内容的能力。我们最终得到了两个 HMAC，一个使用记录的内容计算（以使对记录的更改变得明显），第二个使用当前记录 HMAC 和前一行的 HMAC 来使任何行删除篡改变得明显。
然后你需要担心删除第一条或最后一条记录。为此，我们使用始终具有相同内容的预告片和标题记录，如果这些内容不存在，则表的顶部或底部已被删除。标头的组合 HMAC 使用其后的记录而不是之前的记录（因为之前没有记录）。
当然，如果您要删除旧记录来管理存储的数据量，您将需要一种机制来在删除后添加新的标头记录。
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.
以下是一些可能性：
Here are some possibilities:
您可以启用更改跟踪，这样您就可以进行“审计表上的审计”。
如果您的基础设施管理得当，我猜用户没有 sa 权限，他们使用 Management Studio 查看使用其 Windows 帐户登录的数据库，在这种情况下，您可以在该审核表上设置安全性，只有 sa 和其他管理帐户会被访问。能够更改内容，但不能更改普通用户/开发人员帐户。
希望这有帮助。
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.
您所描述的问题可能表明您的系统架构中存在更严重的问题。
通常，用户甚至不应该直接访问运行数据库的计算机。
您可能需要考虑一种架构，其中数据库计算机与业务逻辑计算机分开，并且只能由它们访问。
如果您的用户决定尝试不通过客户端访问您的服务器，那么他们应该能够做的就是访问您决定公开的明确定义的 Web 服务。
用户没有理由应该能够访问数据库计算机，或者拥有允许写入数据库的帐户的凭据。您似乎担心审计信息被篡改。如何阻止恶意用户删除表或篡改功能数据？
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?
将审核数据分离到其自己的架构中，然后设置权限，以便您关注的用户无权访问该架构。
使用完全独立的数据库，甚至可以位于不同的计算机上。
我经常看到某种类型的发布/订阅模型用于从关系数据库发布审计数据，然后将该审计数据异步写入审计存储。
也许您可以让触发器将审核数据写入队列。然后，您可以安排一个每隔几分钟运行一次的作业，以从队列中获取审核数据并将其写入审核存储。
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.
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.