用于 Web 应用程序的 SQL Server 上用户/角色的最佳实践 [英] Best practice on users/roles on SQL Server for a web application

问题描述

我在网上搜索了一下，没有找到任何能真正抓住重点或涵盖如何在数据库上设置用户/角色的基础知识.

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.

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

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).

然后，我们还会有一个用户作为主管理员(这很简单).

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

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

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.

Web 应用程序和 SQL Server 驻留在不同的机器上.

Web application and SQL Server reside on separate machines.

忘记提及使用了需要直接读/写访问的 ORM.

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

问题:为应用程序访问设置用户的最佳实践"是什么?哪些角色适用?有哪些问题?

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?

推荐答案

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

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.

就为该角色应用权限而言，这些天我倾向于更广泛地撒网，特别是如果有人使用 ORM 并通过应用程序处理安全性.在 T-SQL 术语中，它看起来像这样:

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:

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

这乍一看似乎有点吓人，但实际上并非如此——该角色除了操纵数据之外什么也做不了.无法访问扩展 procs 或系统 procs 或授予用户访问权限等.另一个很大的优势是更改架构 - 就像添加表或过程 - 只要您保持在该架构内，就不需要进一步的安全工作.

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.

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

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.

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

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.

这篇关于用于 Web 应用程序的 SQL Server 上用户/角色的最佳实践的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！