SQL Server：保护数据库不被直接更改
我们的程序附带 SQL Server 2005 数据库和 SQL Server 2005 Express。通常它会在客户端计算机上安装自己的 SQL Server 2005 实例。
现在我必须添加一些表，其内容只能从程序内更新。我需要防止这些表被直接更改，例如使用 Management Studio。
我怎样才能实现这个目标？我应该设置用户权限吗？我可以使用加密吗？我考虑设置自己的“sa”密码来访问 SQL Server 实例，并仅在程序内使用它，但这并不会使通过 Windows 身份验证进行的访问无效。
[编辑]对我正在尝试做的事情的一些澄清。该计划是一个时间和出勤计划。员工的打卡时间通过考勤钟收集并保存在数据库中；一旦收集，这些时钟就无法删除，并且它们的日期和时间值也无法更改。所以我需要一种方法来防止用户直接在数据库中弄乱这些值。
请记住，我们的大多数客户没有任何 SQL 经验，因此我需要在程序安装时设置这些权限。
[编辑2]感谢您的回答，我想再提出两个与此主题相关的问题：
1 - 我可以仅向通过 Windows 身份验证访问数据库的用户授予 SELECT 权限吗？
2 - 是否可能/可行通过哈希系统保护表免受更改？就像添加一个哈希列并计算每行的哈希值，然后将行数据与哈希值进行比较以检查更改？
Our program ships with an SQL Server 2005 database and SQL Server 2005 Express. Usually it installs its own instance of SQL Server 2005 in the client's computer.
Now I have to add some tables whose content should only be updated from within the program. I need to prevent these tables from being changed directly, by using Management Studio for instance.
How can I achieve this? Should I set user permissions? Can I use encryption? I thought of setting my own 'sa' password for accessing the SQL Server instance and use it only from within the program, but that does not invalidate its access through Windows Authentication.
[Edit] Some clarification of what I'm trying to do. The program is a time and attendance program. The employees' clockings are collected from time clocks and saved in the database; once collected, these clockings cannot be deleted and their date and time values cannot be changed. So I need a way to prevent users from messing with these values directly in the database.
Bear in mind that the majority of our customers does not have any experience in SQL, so I need to have these permissions set upon program installation.
[Edit 2] Thank you for your answers, I would like to make two more questions related to this subject:
1 - Can I grant only SELECT permissions to those that access the DB through Windows Authentication?
2 - Is it possible/viable to protect a table against changes through a hash system? Like adding a hash column and calculate a hash for each row, then comparing the row data with the hash to check for changes?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
您无法阻止本地管理员组的成员对您的数据库进行任何他们喜欢的事情。任何相反的说法都是万金油。在当今时代，只需一个勇敢的用户找到一种方法来更改数据（例如，添加自己的时钟时间），从那时起，Google 将确保每个 > 感兴趣的用户会找到破解方法。
绝大多数用户都是他们使用的计算机上的管理员组的成员。即使在最严格、最严格的公司执行组策略环境中，当涉及到用户的薪水时，人们也不会梦想将敏感数据留在用户的计算机上。
在用户计算机上设置防篡改数据存储，在用户有篡改数据动机的情况下，只是自找麻烦。集中存储数据，使用Web服务收集数据。
You cannot prevent members of the local Administrators group to do anything they like with your database. Any claim to the contrary is snake oil. In this day and age, all it take is one intrepid user to find a way to alter the data (eg. adding himself clock time) and from then on Google will ensure every user interested will find the hack.
The vast majority of users are members of the Administrators group on the machine they use. Even the in the most tight and strict company enforced group policy environment one would not dream of leaving sensitive data on the users computers when the very paycheck of that user is involved.
Setting up a tamper evident data store on a user computer, in a case when the user has an incentive to tamper the data, is just asking for trouble. Store the data centrally, use a web service to collect the data.
您是否可以使用 SQL Server Compact Edition 而不是表达？
它嵌入到应用程序中，专为此类场景而设计。
Are you able to use SQL Server Compact Edition instead of the Express?
It is embedded into the app and is designed for this kind of scenario.
您可以在行级别向数据添加哈希检查，以便在数据被修改时，每晚运行的篡改检查可以检测到它并发出警报。
You can add a hash check to your data at a row level so that if it is modified, a tamper check that runs nightly could detect it and raise an alert.
如果任何人拥有 sa 级别的访问权限，您就无法阻止这种情况。不过，普通用户不应该具有 sa 访问权限。
您可以通过授予帮助将其与普通用户隔离，例如，仅授予您的应用程序用户对 INSERT、UPDATE、DELETE（或 EXECUTE，如果您使用存储过程而不是直接 SQL）的访问权限，并且仅授予其他用户 SELECT（或不授予）访问权限。
您可以做一些其他事情来分散临时用户的注意力，也许可以通过对插入/更新/删除进行触发器检查来强制这些操作仅由您的应用程序用户执行。我不会推荐它，但你可以这样做。
If anyone has sa-level access, you can't prevent this. Regular users should not have sa access though.
You can help insulate it from regular users via grants, e.g. only giving your application user access to INSERT, UPDATE, DELETE (or EXECUTE, if you are using stored procedures instead of direct SQL) and only giving other users SELECT (or no) access.
You could do some other things to detract the casual user, perhaps with trigger checks on insert/update/delete to enforce that those actions are only being done by your application user. I wouldn't recommend it, but you could do it.