SQL Server Windows 身份验证安全性
我们有一个使用 Windows 身份验证来对数据库用户进行身份验证的应用程序，并且 SQL Server 用户帐户需要对数据库表具有一定的读/写访问权限。
问题是用户可以安装 SQL Server Management Studio，并可能以不应该使用的方式使用数据库，这不是我想要的。
我读到的所有内容都表明使用集成身份验证更安全，但目前任何用户都可以使用 Management Studio 或 Access/Excel 连接到数据库。
我已阅读问题 SQL Server 身份验证或集成安全性？，其中建议了一些解决方法，但我真的没有像重构所有存储过程等那样彻底改变应用程序的选择，所以我希望可能还有另一种选择？
谢谢你，
妮可
We have an application that uses Windows authentication to authenticate users with the database, and the SQL Server user accounts need to have certain read/write access to database tables.
The trouble is that the users can then install SQL Server Management Studio and potentially use the database in ways it's not supposed to be used, which isn't what I want.
Everything that I have read says that using integrated authentication is more secure but at the moment, any user can use Management Studio or Access/Excel to just connect to the database.
I have read question SQL Server Authentication or Integrated Security?, which suggests some workarounds, but I don't really have the option of changing the app as drastically as re-factoring all of the stored procedures etc. so I was hoping there might be another option?
Thank you,
NIco
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
-->从某种程度上来说，它更安全，因为获取密码更困难。
如果使用 SQL Server 身份验证，连接字符串包含用户和密码。如果您知道连接字符串在哪里（通常在配置文件中），则可以打开它并查看用户和密码。
另一方面，如果您使用 Windows 身份验证，连接字符串只会显示“Integrated Security=True”，并且您使用 Windows 帐户连接到服务器，而实际密码则埋藏在 Windows 内部深处的某个位置，并且更难以检索。
当然，Windows 身份验证的一大缺点是，如果您的用户需要应用程序对某个表的写入权限，这意味着他们也可以与任何其他应用程序一起写入同一个表。
有一些解决方法，但没有一个是灵丹妙药：
如果您的应用程序只需要数据库的某些表，您只需授予这些表的权限即可。因此，至少，用户无法在所有其他表中执行操作
如果根本不允许用户从应用程序外部访问任何表，那么不幸的是，您只能做两件事：
（您还可以使用代理服务像 Will Hughes 建议的那样，但是当应用程序直接访问数据库时，效果是相同的...重点是您的用户的 Windows 帐户不再具有任何权限！）
-->用户可以使用其他工具直接访问视图和 SP（即使他们对基础表没有任何权限...对视图和 SP 的权限就足够了），但他们不能做任何他们能做的事情在你的应用程序中也不要做。
--> It's more secure in a way because it's more difficult to get the password.
If you use SQL Server authentication, the connection string contains user and password. If you know where the connection string is (often in a config file), you can open it and see user and password.
On the other hand, if you use Windows authentication, the connection string just says "Integrated Security=True" and you connect to the server with your Windows account, with the actual password buried somewhere deep in Windows' guts and more difficult to retrieve.
Of course, the big downside of Windows authentication is that if your users need write permissions on a certain table for your application, this means that they can write to the same table with ANY other application as well.
There are some workarounds, but none of them is THE silver bullet:
If your app only needs certain tables of the DB, you can just give permissions on these. So at least, the users can't do stuff in all the other tables
If the users are not allowed to access any tables at all from outside your application, there are unfortunately only two things you can do:
(you can also use a proxy service like Will Hughes suggested, but the effect is the same when the app accesses the DB directly...the point is that your users' Windows accounts don't have any permissions anymore!)
--> the users can access the views and SPs directly with other tools (even if they don't have any permissions on the underlying tables...permissions on the views and SPs are enough), but they can't do anything that they can't do in your app as well.
如果您不希望用户访问您的数据库，请不要授予他们访问权限。
如果您需要控制它们可以做什么 - 那么您应该在 Web 服务（或某种其他形式的代理服务）中进行访问控制，然后该服务将执行批准的查询、返回数据等。
If you don't want users to have access to your database, don't grant them access.
If you need to control what they can do - then you should do your access control in a webservice (or some other form of proxy service), which will then execute approved queries, return data, etc.