使用 SQL Server 用户和角色作为 Intranet Web 应用程序的授权数据库？
我有一个问题，我真的觉得我应该有一个简单的答案，但由于某种原因，我无法完全推理它。
我正在着手开发 ASP.NET MVC3 Intranet 应用程序，目前正在设计身份验证和身份验证。授权。我们被迫在我们的环境中使用基本身份验证，并且我们使用 Active Directory，因此通常会处理授权部分。不幸的是，活动目录中的角色/用户层次结构并不反映我对应用程序中的角色的需求，因此我必须定义自己的角色/用户层次结构。
我使用的是SQL Server，所以我最初考虑的是所有DML都使用存储过程，然后在SQL Server中创建角色并在角色中添加用户，然后通过这些角色控制对存储过程的访问。我还想我可以查询那些 SQL Server 数据库级用户和数据库级别的用户。角色，以便将其用作应用程序本身的授权信息源。最初这似乎是一个好主意，但它似乎并不受欢迎（首先，对于它们产生的结果来说，对此的查询似乎有点长且混乱）。或者，让 Web 应用程序模拟用户对服务器进行所有查询，然后使用我自己的架构实现用户/角色数据库，并且仅在应用程序端进行授权会更好吗？
最初看来，在应用程序和数据库端进行授权对于安全来说是一件好事，并且使用 SQL Server 用户/角色对象意味着用户和角色数据不需要存储在两个地方。
我确实在 看到了一些可能相关的讨论SQL Server 上用于 Web 应用程序的用户/角色的最佳实践，但我认为这总体上是一个不同的问题。
谢谢！
I have a question that really feels like I should have an easy answer to, but for one reason or another I haven't been able to totally reason around it.
I'm embarking on development of an ASP.NET MVC3 intranet application, and I'm currently working on designing authentication & authorization. We're forced to use basic authentication in our environment, and we use Active Directory, so the authorization part is generally taken care of. Unfortunately our role/user hierarchy in active directory doesn't mirror what I need for the roles in the application, so I'm going to have to define my own.
I'm using SQL Server, so I was originally thinking of using stored procedures for all DML, and then creating roles and adding users in roles in SQL Server, and then controlling access to the stored procedures via those roles. I was also thinking I could query for those SQL Server database-level users & roles in order to use that as the source of authorization info in the application itself. That originally seemed like a great idea, but it doesn't seem like a popular one (for one, it seems the queries for that are a little long and messy for what they produce). Alternatively, would it be better to have the web app impersonate a user for all queries to the server, and then implement a user/role database with my own schema, and only authorize on the application side?
It originally seemed that authorizing on both the application and database side would be a good thing for security, and using the SQL Server user/role objects means that the user and role data wouldn't need to be stored in two places.
I did see some potentially relevant discussion at Best practice on users/roles on SQL Server for a web application, but I think this is a different question overall.
Thanks!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我建议创建一个 sql 登录名，Web 应用程序将使用它来连接到 sql server。这样您就不会冒充任何将来可能被删除、禁用的特定 AD 帐户，并且可以在 SQL Server 中严格控制用户。
然后，我建议在您的应用程序中实施基于角色的身份验证。这将使您能够创建针对您的应用程序自定义的用户和角色，然后将用户分配给他们。这样，如果用户尝试访问其角色不允许的资源，它将不会执行任何操作。这是一个基于此原则的演示应用程序 http://www.codeproject.com/ KB/web-security/rolesbasedauthentication.aspx。
I recommend creating a sql login that the web application will use to connect to sql server. This way you are not impersonating any specific AD account which may get deleted, disabled in the future and can control the user strickly in SQL Server.
I would then recommend implementing roles based authentication in your application. This will enable you to create users and roles that are custom to your application and then assign users to them. This way if a user tries to access a resource that their role is not allowed it will not do any work. Here is a demo app based on this principle http://www.codeproject.com/KB/web-security/rolesbasedauthentication.aspx.