SQL Server身份验证还是集成安全性? [英] SQL Server Authentication or Integrated Security?

问题描述

我们有一些企业Intranet用户使用WinForms应用程序在后面装有SQL Server的系统上工作.设置了集成安全性，允许所有用户更新和删除权限，其中应用程序安全性限制了表更新的方式和位置.

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.

但是，有些用户是高级用户，他们可以使用SQL查询工具，并直接访问数据库以生成报告.但是，由于具有集成的安全性，由于应用程序将规则应用于更新，因此它们对不应该具有的表具有默认的更新权限.

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.

这是一个更合适的示例，它为应用程序提供中央SQL身份验证登录，同时用户获得集成安全性的只读权限吗?

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?

推荐答案

正如乔恩(Jon)所述，存储过程将为您提供直接表修改的保护.也有其他选择.您可以使用SQL Server的应用程序角色"(通过sp_setapprole proc).这样一来，您就可以继续为每个人使用单独的ID，但只有在应用程序连接时(通过前端)，用户的权限才会提高.

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.

使用共享ID的主要缺点是，您无法跟踪谁向服务器提交SQL，尽管如果它们都是内部的，则可以获取计算机名称.

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.

尽管有其他问题.听起来您的用户好像可以连接到数据库并随意运行查询.由于用户在直接连接的SQL会话中的行为，因此在应用程序中会造成重大的停机风险.如果可以实现，则可能要尝试创建一个报告数据库，该数据库会按您的业务可以承受的时间间隔(即每天)进行更新. HTH

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 Server身份验证还是集成安全性?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！