用于 Web 应用程序的 SQL Server 上的用户/角色的最佳实践

Best practice on users/roles on SQL Server for a web application

提问by Manuel

I searched online a bit and couldn't find anything that really nailed the spot or covered the bases how to go about setting up users/roles on a database.

我在网上搜索了一下，但找不到任何能真正解决问题或涵盖如何在数据库上设置用户/角色的基础知识。

Basically, there would be a user that would be used to access the database from the application (web application in this case) that will need access to database for the regular database operations (select, insert, update, delete) and executing stored procedures (with exec to run stored procedures within other stored procedures/UDFs).

基本上，会有一个用户用于从应用程序（在本例中为 Web 应用程序）访问数据库，该用户需要访问数据库以进行常规数据库操作（选择、插入、更新、删除）和执行存储过程（使用 exec 在其他存储过程/UDF 中运行存储过程）。

Then, we would also have a user that would be main admin (this is simple enough).

然后，我们还将有一个用户作为主管理员（这很简单）。

I currently have a development environment where we don't really manage the security too well in my opinion (application uses a user with db_owner role, though it is an intranet application). Even though it is an intranet application, we still have security in mind and would like to see what are some of the ways developers set up the users/roles for this type of environment.

我目前有一个开发环境，在我看来，我们并没有真正很好地管理安全性（应用程序使用具有 db_owner 角色的用户，尽管它是一个 Intranet 应用程序）。即使它是一个 Intranet 应用程序，我们仍然牢记安全性，并希望了解开发人员为此类环境设置用户/角色的一些方式。

EDIT: Web application and SQL Server reside on separate machines.

编辑：Web 应用程序和 SQL Server 驻留在不同的机器上。

EDIT: Forgot to mention that an ORM is used that would need direct read/write access.

编辑：忘记提及使用了需要直接读/写访问的 ORM。

Question:What are the "best practices" on setting up the user for application access? What roles would apply and what are some of the catches?

问题：设置用户以进行应用程序访问的“最佳实践”是什么？将适用哪些角色以及有哪些问题？

回答by Wyatt Barnett

First, I tend to encapsulate permissions in database roles rather than attach them to single user principals. The big win here is roles are part of your database, so you can completely script security then tell the deployment types to "add a user and add him to this role" and they aren't fighting SQL permission boogeymen. Furthermore, this keeps things clean enough that you can avoid developing in db_owner mode and feel alot better about yourself--as well as practice like you play and generally avoid any issues.

首先，我倾向于将权限封装在数据库角色中，而不是将它们附加到单个用户主体。这里最大的好处是角色是您数据库的一部分，因此您可以完全编写安全脚本，然后告诉部署类型“添加用户并将他添加到此角色”，并且他们不会与 SQL 权限问题作斗争。此外，这使事情保持足够干净，您可以避免在 db_owner 模式下进行开发，并对自己感觉更好——以及像玩游戏一样练习并通常避免任何问题。

Insofar as applying permissions for that role, I tend to cast the net wider these days, especially if one is using ORMs and handling security through the application. In T-SQL terms, it looks like this:

就为该角色应用权限而言，这些天我倾向于更广泛地撒网，尤其是在使用 ORM 并通过应用程序处理安全性的情况下。在 T-SQL 术语中，它看起来像这样：

GRANT SELECT, UPDATE, INSERT, DELETE, EXECUTE on SCHEMA::DBO to [My DB Role]

This might seem a bit scary at first, but it really isn't -- that role can't do anything other than manipulate data. No access to extended procs or system procs or granting user access, etc. The other big advantage is that changing the schema--like adding a table or a procedure--requires no further security work so long as you remain within that schema.

乍一看这可能有点吓人，但实际上并非如此——该角色除了操纵数据之外不能做任何事情。无法访问扩展过程或系统过程或授予用户访问权限等。另一个很大的优势是更改模式——比如添加表或过程——不需要进一步的安全工作，只要你保持在该模式内。

Another thing to take into consideration for SQL 2005+ is to use database schemas to secure groups of objects. Now, the big trick here is that many ORMs and migration tools don't like them, but if you render the default schema [dbo] to the app, you can use alternative schemas for special secured stuff. Eg--create an ADMIN schema for special, brutal database cleanup procedures that should be manually run by admins. Or even a separate schema for a special, highly secured part of the application that needs more granular DB permissions.

SQL 2005+ 需要考虑的另一件事是使用数据库模式来保护对象组。现在，这里的大技巧是许多 ORM 和迁移工具不喜欢它们，但是如果您将默认架构 [dbo] 呈现给应用程序，您可以使用替代架构来处理特殊的安全内容。例如——为特殊的、残酷的数据库清理程序创建一个 ADMIN 模式，这些程序应该由管理员手动运行。或者甚至是应用程序中需要更细粒度的数据库权限的特殊的、高度安全的部分的单独架构。

Insofar as wiring in users where you have separate boxes, even without a domain you can use Windows authentication (in Sql Server terms integrated authentication). Just make a user with the same credentials (user/pass combo) on both boxes. Setup an app domain to run as that user on the web box and setup a Sql Server user backed by that principal on the sql box and profit. That said, using the database roles can pretty much divorce you from this decision as the deployment types should be able to handle creating sql users and modifying connection strings as required.

就在具有单独框的用户中进行布线而言，即使没有域，您也可以使用 Windows 身份验证（在 Sql Server 术语中，集成身份验证）。只需在两个盒子上创建一个具有相同凭据（用户/密码组合）的用户即可。设置一个应用程序域以在 web box 上以该用户身份运行，并在 sql box 和利润上设置由该主体支持的 Sql Server 用户。也就是说，使用数据库角色几乎可以使您远离这个决定，因为部署类型应该能够根据需要处理创建 sql 用户和修改连接字符串。

回答by Remus Rusanu

For a long time the SQL Server guidelines for application access to the database were to isolate access to data into stored procedures, group procedures into a schema and grant execute on the schema to the principal used by the application. Ownership chaining would guarantee data access to the procedure callers. The access can be reviewed by inspecting the stored procedures. This is a simple model, easy to understand, design, deploy and manage. Use of stored procedure can leverage code signing, the most granular and powerfull access control method, and the only one that is tamper evident (signature is lost if procedure is altered).

长期以来，SQL Server 应用程序访问数据库的指导方针是将数据访问隔离到存储过程中，将过程分组到架构中，并将架构上的执行授予应用程序使用的主体。所有权链接将保证对过程调用者的数据访问。可以通过检查存储过程来检查访问。这是一个简单的模型，易于理解、设计、部署和管理。使用存储过程可以利用代码签名，这是最细粒度和最强大的访问控制方法，也是唯一一种防篡改的方法（如果过程被更改，签名就会丢失）。

The problem is that every bit of technology comming out from the Visual Studio designers flies in the face of this recommendation. Developers are presented with models that are just hard to use exclusively with stored procedures. Developers love to design their class models first and generate the table structure from the logical model. The procedure based guidelines reuire the procedures to exists first, before the first line of the application is written, and this is actually problematic in development due to the iterative way of modern development. This is not unsolvable, as long as the team leadership is aware of the issue and addresses it (ie. have the procedures ready, even as mocks, when the dev cycle starts).

问题在于，Visual Studio 设计人员提出的每一项技术都与此建议背道而驰。向开发人员展示的模型很难专门用于存储过程。开发人员喜欢首先设计他们的类模型，然后从逻辑模型生成表结构。基于过程的准则要求在编写应用程序的第一行之前首先存在过程，由于现代开发的迭代方式，这在开发中实际上是有问题的。这不是无法解决的，只要团队领导意识到这个问题并解决它（即在开发周期开始时准备好程序，即使是模拟）。

回答by Kev Riley

Create a user 'webuser' that the web application uses.

创建 Web 应用程序使用的用户“webuser”。

Only grant stored proc execute permissions to this user. Do not allow direct table read/write. If you need to read something from a table, write a proc. If you need to write data, write another proc.

仅向该用户授予存储过程执行权限。不允许直接表读/写。如果您需要从表中读取某些内容，请编写一个 proc。如果需要写数据，再写一个proc。

This way everything is kept nice and simple. One app user, with only the relevant permissions. If security is compromised, then all the intruder can do is run the procs.

这样一切都保持美好和简单。一个应用程序用户，只有相关权限。如果安全性受到损害，那么入侵者所能做的就是运行 procs。