SQL Server数据库保护聪明的管理员？ [英] SQL Server Database securing against clever admins?

问题描述

我想保护存储在一个表中的事件，这与其他表有关系。
事件通过Windows服务插入，即连接到硬件和从硬件读取。

I want to secure events stored in one table, which has relations to others. Events are inserted through windows service, that is connecting to hardware and reading from the hardware.

在事件表中是PK，日期和时间以及3个不同的值。

In events table is PK, date and time, and 3 different values.

问题是，管理员可以登录并在此表中插入/更新/删除数据使用sql管理工作室。我创建触发器以防止更新和删除，所以如果管理员不知道触发器，他无法更改数据，但如果他知道触发器，他可以轻松禁用触发器，并做任何他想要的。

The problem is that every admin can log in and insert/update/delete data in this table e.g. using sql management studio. I create triggers to prevent update and delete, so if admin doesn't know triggers, he fail to change data, but if he knows trigger, he can easily disable trigger and do whatever he wants.

所以经过长时间的思考，我有一个想法，添加新的列（字段）到表和存储类似校验和在这个字段，这个校验和将基于其他值计算。此校验和将在insert / update语句中生成。
如果有人手动插入/更新某人，我会知道，因为如果我检查数据与校验和，将会有不匹配。

So after long thinking I have one idea, to add new column (field) to table and store something like checksum in this field, this checksum will be calculated based on other values. This checksum will be generated in insert/update statements. If someone insert/update something manually I will know it, because if I check data with checksum, there will be mismatches.

我的问题是，如果你有类似的问题，你怎么解决呢？
什么算法用于校验和？

My question is, if you have similar problem, how do you solve it? What algorithm use for checksum? How to secure against delete statement (I know about empty numbers in PK, but it is not enough) ?

我使用的是SQL Server 2005.

I'm using SQL Server 2005.

推荐答案

在服务器级别执行的任何操作，管理员都可以撤消。

Anything you do at the server level the admin can undo. That's the very definition of its role and there's nothing you can do to prevent it.

在SQL 2008中，您可以使用X事件请求审核所述SQL服务器，请参阅< a href =http://msdn.microsoft.com/en-us/library/cc280386.aspx =noreferrer> http://msdn.microsoft.com/en-us/library/cc280386.aspx 。这是CC兼容的解决方案，显示防篡改。

In SQL 2008 you can request auditing of the said SQL server with X events, see http://msdn.microsoft.com/en-us/library/cc280386.aspx. This is CC compliant solution that is tamper evident. That means the admin can stop the audit and do its mischievous actions, but the stopping of the audit is recorded.

在SQL 2005中，推荐的审核解决方案是使用分析器基础架构。当正确部署时，这可以使得显窃启。您将防止使用触发器和约束进行数据更改，并审核DDL更改。如果管理员更改了触发器，这在审计中是可见的。如果管理员停止审核，则审核中也会显示此信息。

In SQL 2005 the auditing solution recommended is using the profiler infrastructure. This can be made tamper evident when correctly deployed. You would prevent data changes with triggers and constraints and audit DDL changes. If the admin changes the triggers, this is visible in the audit. If the admin stops the audit, this is also visible in the audit.

您是否计划将此作为对欺诈管理员的一次性操作或作为要添加的功能到您的产品？在应用程序周期中使用数字签名来签署所有应用程序数据可能非常昂贵。您还必须设计一个安全的方案来显示记录未被删除，包括最后的记录（即，不是标识列中的简单缺口）。例如。您可以通过 CHECSUM_AGG .microsoft.com / en-us / library / ms173784.aspxrel =noreferrer> BINARY_CHECKSUM（*），在应用程序中签名结果，并在每次更新后存储每个表的带符号值。 Needles说，这将减慢你的应用程序，因为基本上你序列化每个操作。对于个别行cheksums / hashes，您必须计算应用程序中的整个签名，这将需要您的应用程序尚未具有的值（即，标识列值 分配给您的插入） ）。你想去多远？一个简单的哈希可以打破如果管理员握住你的应用程序，并监控你的哈希，以什么顺序（这是微不足道的实现）。然后他可以重新计算相同的散列。 HMAC要求您在应用程序中存储一个秘密，这对于一个确定的黑客基本上是不可能的。这些问题可能看起来太过分了，但如果这是您销售的应用程序，那么所有需要的是一个黑客打破你的​​哈希序列或hmac秘密。 Google会确保所有人都能找到它，最终。

Do you plan this as a one time action against a rogue admin or as a feature to be added to your product? Using digital signatures to sign all your application data can be very costly in app cycles. You also have to design a secure scheme to show that records were not deleted, including last records (ie. not a simple gap in an identity column). Eg. you could compute CHECSUM_AGG over BINARY_CHECKSUM(*), sign the result in the app and store the signed value for each table after each update. Needles to say, this will slow down your application as basically you serialize every operation. For individual rows cheksums/hashes you would have to compute the entire signature in your app, and that would require possibly values your app does not yet have (ie. the identity column value to be assigned to your insert). And how far do you want to go? A simple hash can be broken if the admin gets hold of your app and monitors what you hash, in what order (this is trivial to achieve). He then can recompute the same hash. An HMAC requires you to store a secret in the application which is basically impossible against a a determined hacker. These concerns may seem overkill, but if this is an application you sell for instance then all it takes is for one hacker to break your hash sequence or hmac secret. Google will make sure everyone else finds out about it, eventually.

我的观点是，如果你想通过技术阻止管理员 管理员是您信任的人，如果在您的情况下这是破碎的，问题是信任，而不是技术。

My point is that you're up the hill facing a loosing battle if you're trying to deter the admin via technology. The admin is a person you trust and if this is broken in your case, the problem is trust, not technology.

这篇关于SQL Server数据库保护聪明的管理员？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！