SQL Server和asp.net应用程序的安全模式/最佳实践 [英] SQL Server and asp.net application security models / best practice

问题描述

有关针对SQL Server我的asp.net web应用程序（至少需要登录访问的那些）我一般实现安全性如下：

我一般推出自己的用户注册，用户登录页面，并保持在SQL Server中的用户名和加密的密码和验证针对该表中的登录 - 我也为遗忘的密码，密码发送给我'，电子邮件验证激活账号等所有直通定制code。

一旦用户已通过验证的应用程序（并让我们假设所有用户都具有相同的权限），我一般使用工具LogonID的让asp.net谈话到SQL Server，所以换句话说，我只需要创建每个应用程序的单一登录ID，并且由于所有的数据访问100％的直通存储过程做的，我只需要授权给单个用户执行存储过程，并没有其他的工作需要SQL Server上。每个应用程序都有自己的数据库，并且该应用的登录只能访问数据库。

这一切都完美地工作很适合我，这样做的唯一的缺点是，这将是很好能够设置SQL Server中的跟踪，看到了用户ID和正在调用特效，但由于所有用户都'说话'到数据库直通单个数据库的登录，这是不可能发生的。

所以，两个部分的问题：

有

1）安全模型，你使用的乡亲，我应该考虑的？它很容易总是做同样的事情一遍又一遍， - 尤其是因为它的作品 - 但有没有其他的车型，将更好地工作，或者我应该考虑？它是推荐的做法，从一个asp.net应用中的所有数据库访问都将共享同一个数据库登录？或者这被认为是不好的做法？如果是这样，为什么？

2）假设我坚持我的模型，有没有办法，让该应用程序登录ID在SQL跟踪窗口待观察？这将是很好看SP的被称为和人员的用户ID登录到系统（而不是数据库登录）。

解决方案

  

它是推荐的做法，所有的
  从一个asp.net应用程序的数据库访问
  都会共享一个数据库
  登录？

是的，主要用于连接池。

在关于2），我通常做，通过在ASP.NET端日志。

For my asp.net web applications against sql server(at least the ones that require a logon to access) I generally implement security as follows:

I generally roll my own user signup, user login pages and keep a userid and an encrypted password in an sql server and validate the login against that table - I also provide for forgotten passwords, 'send me my password', email verification to activate accounts etc all thru custom code.

Once the user has been validated by the app (and lets assume that all users have the same privileges), I generally use a utility logonid to let asp.net talk to sql server, so in other words I only need to create a single logon id per application, and since 100% of all data access is done thru stored procedures, I need only grant access to a single user to execute stored procedures, and no other work is required on sql server. Each application has its own database, and the logon for that app can only access that database.

All this works perfectly well for me, the only negative of this is that it would be nice to be able to setup a trace in sql server and see the userid and the procs that are being called, but since all users are 'talking' to the database thru a single database logon, this can't happen.

So, two part question:

1) Are there security models are you folks using that I should consider? Its easy to always do the same thing over and over, - especially since it works - but are there other models that would work better or that I should consider? Is it recommended practice that all database access from an asp.net app would all share a single database login? or is this considered bad practice? and if so, why?

2) Assuming I stick with my model, is there a way to allow for the application login id to be seen in the sql trace window? It would be nice to see the sp's being called and the user id of the person logged into the system (not the database login).

解决方案

Is it recommended practice that all database access from an asp.net app would all share a single database login?

Yes, primarily for connection pooling.

In regards to 2), I normally do that by logging at the ASP.NET side.

这篇关于SQL Server和asp.net应用程序的安全模式/最佳实践的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！