SQL Server 身份验证还是集成安全性？
我们有一些企业内部网用户使用 WinForms 应用程序在带有 SQL Server 的系统上工作。 设置集成安全性，允许所有用户更新和删除权限，其中应用程序安全性限制表更新的方式和位置。
然而，一些用户是高级用户，可以使用 SQL 查询工具，并直接访问数据库来构建报告。 然而，通过集成的安全性，他们对不应该拥有的表拥有默认的更新权限，因为应用程序将规则应用于更新。
这是一个为应用程序提供中央 SQL 身份验证登录更合适的示例，同时用户获得集成安全性的只读权限吗？
We have some corporate intranet users using a WinForms app to work on a system with SQL server behind. Integrated Security is setup, allowing all users update and delete permissions, where application security limits how and where table updates take place.
However, some users are power users with SQL query tools at their disposal, and access the DB directly for building reports. However, with integrated security, they have default update rights on tables where they should not have, as the application apply rules to the updates.
Is this an example of where it's more appropriate providing the app with a central SQL authenticated login, whilst users get read only rights for integrated security?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
正如 Jon 提到的，存储过程将为您提供对直接表修改的保护。 还有其他选择。 您可以使用 SQL Server 的“应用程序角色”（通过 sp_setapprole proc）。 这使您能够继续为每个人使用单独的 ID，但只有在应用程序连接时（通过前端）才会提升用户的权限。
使用共享 ID 的一个主要缺点是您无法跟踪谁向服务器提交 SQL，但如果它们都是内部的，您可以获取计算机名称。
但还有一些事情值得关注。 听起来好像您的用户可以连接到数据库并随意运行查询。 由于直接连接的 SQL 会话中的用户行为，您将面临应用程序停机的重大风险。 如果您能成功，您可能需要尝试创建一个报告数据库，该数据库以您的企业可以容忍的时间间隔（即每天）更新。 华泰
As Jon mentioned stored procedures would give you the protection over direct table modifications. There are other options too. You can use SQL Server's "Application Role" (via sp_setapprole proc). This enables you to continue to use a separate ID for everyone but only at application connection time (through the front-end) are the user's rights elevated.
A major downside to using a shared ID is you lose track of who is submitting SQL to the server though if they're all internal you can get to the machine name.
Something else is concerning though. It sounds as if your users can connect to the database and run queries at will. You run a major risk of downtime in the application due to user behavior in the directly connected SQL sessions. If you can pull it off you may want to try to have a reporting database created that is updated at intervals that your business can tolerate, i.e., daily. HTH
从您提出问题的方式来看，我推测您的应用程序直接执行 sql 语句。 如果您可以重构它以使其执行存储过程，则可以授予该过程的执行权限并拒绝直接更新表。 但这可能是不可能的，具体取决于您的应用程序的功能。
I presume from the way that you've worded your question that your app executes sql statements directly. If you could refactor it so that it executes stored procedures, you could grant exec rights on the procedures and deny direct updating of the tables. This might not be possible though, depending on what your app does.
sql 身份验证是一种选择。 存储过程是另一个。 但是，您真正应该关注的是构建更细粒度的角色，以便为适当的用户类型分配适当的权限。
此外，我真的会避免让这些用户直接访问数据库。 撇开安全原因不谈，不精通 SQL 的用户很容易意外执行查询，该查询会淹没您的数据库服务器并造成有效的拒绝服务。 即使是专业人士有时也可能会意外地这样做。
相反，让他们访问报告服务或分析服务类型的解决方案，或者使用复制来让他们访问数据的克隆。 这样您的生产系统就受到保护。
sql authentication is one option. Stored procedures are another. However, building more granular roles for assigning just the appropriate permissions to just the appropriate user types is where you should really be looking.
Additionally, I would really avoid giving these users direct access to the DB at all. Security reasons aside, it doesn't take much for a user who isn't proficient in SQL to accidentally execute a query that will swamp your database server and create an effective denial of service. Even pros can do this accidentally from time to time.
Instead, give them access to a reporting services or analysis services type solution, or use replication to give them access to a clone of the data. This way your production system is protected.
就我个人而言，我会通过存储过程进行所有应用程序数据访问。 我会将集成安全性设置为仅允许用户运行 SP，而不直接操作数据。
可以向数据库管理员授予高级访问权限，以便在需要时直接操作数据。
基于组的权限将为您提供更大的访问权限灵活性，并在通过集成安全性控制这些权限时减少管理负担。
Personally I would do all application data access through stored procedures. I would set Integrated security to only allow users to run the SP's and not manipulate the data directly.
Advanced access can be given to DB admins to manipulate the data directly when needed.
Group based permissions will provide you with much more flexibility for access rights, and less administrative burden when controlling these with integrated security.