Question

As it currently stands, this question is not a good fit for our Q&A format. We expect answers to be supported by facts, references, or expertise, but this question will likely solicit debate, arguments, polling, or extended discussion. If you feel that this question can be improved and possibly reopened, visit the help center for guidance.

Closed 10 years ago.

对于我的ASP.NET Web应用程序，针对SQL Server（至少需要登录才能访问的应用程序），我通常按照以下方式实施安全性：

我通常自己製作用戶註冊、用戶登錄界面，並在SQL服務器中保存用戶ID和加密的密碼，在該表格中驗證登錄 - 我同時也提供忘記密碼、發送我的密碼、通過自定義代碼進行電子郵件驗證以啟用帳戶等功能。

一旦应用程序验证了用户（假设所有用户都具有相同的权限），我通常使用实用程序登录标识让asp.net与sql server交互，因此，换句话说，我只需要为每个应用程序创建一个单一的登录ID，由于100％的所有数据访问都是通过存储过程完成的，因此我仅需要授予单个用户访问执行存储过程的权限，而且在sql服务器上不需要进行任何其他工作。每个应用程序都有自己的数据库，而该应用程序的登录标识只能访问该数据库。

所有这些都对我非常有效，唯一的负面影响是很想在SQL Server中设置跟踪，以查看正在调用的用户ID和过程，但由于所有用户都通过单个数据库登录对数据库进行通信，所以这是不可能的。

所以，问题分为两部分：

你们使用了哪些安全模式，我需要考虑吗？总是做同样的事情很容易——特别是它有效——但是否有其他更好的模式可供选择或值得考虑？从 asp.net 应用程序访问数据库是否建议所有数据库访问都共享一个登录名？或者这是否被认为是不好的实践？如果是，为什么？

假设我坚持我自己的模型，是否有一种方法可以在SQL跟踪窗口中看到应用程序登录ID？能够看到被调用的存储过程以及登录到系统中的用户ID（而不是数据库登录）。

Answer 1

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

是的，主要用于连接池。

关于2)，我通常通过登录ASP.NET端来完成。

Answer 2

1) It is recommended that your web application use a single login to the database typically. If you don t you are going to be forced to impersonate your caller, which is typcially not recommended, and it doesn t scale very well. You should not use a different connection string for each user. For example using SQL Authentication for each user is a bad idea. It will make connection polling ineffective.

2) You could do this by modifying the connection string but that would make connection pollign ineffective.

Answer 3

From a .NET best practices point of view, you may want to consider taking a look at Microsoft Enterprise Library. It contains a set of practices, patterns, and features that assist with issues such as Security and Data Access.

Answer 4

1)As long as you are using stored procedures for access one login may be fine. Some peolple like to use one for admin as well.

2)You could modify your stored procedures to accept a user id as a parameter.

Answer 5

I would generally share a single user for connection pooling.

In the case where you may need to trace a particular user. I write in admin functionality where you can make the application use a second database login. You can then enable this for a specific user and trace that user individually.

It just means you get the ability to trace as a one off, yet keeping the single user connection pooling for the rest of the application.

Answer 6

I ve been using the same approach as well. More recently, I wonder if this is affecting the application s scaleability. My DB servers have multi-core processors and are quite capable of parallel operations, but I think SQL Server serialized queries running with the same userID. I think this means that stored procedures from different actual users are beeing queued up because SQL Server sees them all as coming from the same the same userID.

If this is the case, I d think it would be severely limiting the scaleability of my app, no?

友情链接