I've been a SQL server developer thus far, but I'm now having to make an ASP.net MVC5 application to allow users to access the data. Previously, all access was via (SSL encrypted) ODBC connections which were secured using SQL Server logins. I'm comfortable with SQL Server security management; I have a bunch of defined roles and access is usually granted or denied at the schema level. This also allows me to easily monitor/audit access and data changes.
到目前为止,我一直是SQL服务器开发人员,但我现在必须制作一个ASP.net MVC5应用程序,以允许用户访问数据。以前,所有访问都是通过(SSL加密)ODBC连接进行的,这些连接使用SQL Server登录进行保护。我对SQL Server安全管理感到满意;我有一堆已定义的角色,通常在架构级别授予或拒绝访问权限。这也使我能够轻松监控/审核访问和数据更改。
Everything I read about ASP.net seems to want me to make a single user for the application, with very expansive read/write permissions, and then to lock these down piecemeal at the application level. I'd really rather not do that, if possible - I'd like to have users provide their SQL server uid/password at runtime and determine their rights based on that. I suppose I have two questions:
我读到的关于ASP.net的一切似乎都希望我为应用程序创建一个单独的用户,具有非常广泛的读/写权限,然后在应用程序级别将这些内容零碎地锁定。如果可能的话,我真的不愿意这样做 - 我希望让用户在运行时提供他们的SQL服务器uid /密码,并根据它确定他们的权利。我想我有两个问题:
-
Is this even possible? It doesn't seem to be documented anywhere as an option.
这有可能吗?它似乎没有作为选项记录在任何地方。
-
Is there a compelling reason why I shouldn't do this, even if it is possible? I'm not totally averse to learning a new security paradigm if it's what I need to do to keep things really safe.
有没有令人信服的理由为什么我不应该这样做,即使有可能?我不是完全不愿意学习新的安全范例,如果这是我需要做的事情来保证事情真的安全。
Note that for various reasons, using Windows authentication isn't going to be possible here.
请注意,由于各种原因,此处无法使用Windows身份验证。
3 个解决方案
#1
1
Don't use end-user credentials directly for your database.
不要将最终用户凭据直接用于数据库。
The fundamental problem with this approach is that you'd need to be able to retrieve the end-user's password to connect to the database.
这种方法的基本问题是您需要能够检索最终用户的密码才能连接到数据库。
This means that if your systems are ever compromised, you'll leak passwords for other sites (for users who reuse passwords).
这意味着如果您的系统遭到入侵,您将泄露其他站点的密码(对于重用密码的用户)。
As a compromise, you can make the DB password an iterated hash of the end-user's password.
However, you'd still need to store that (perhaps encrypted) in the login cookie, which is generally also a bad idea.
作为折衷方案,您可以将DB密码作为最终用户密码的迭代哈希值。但是,您仍然需要将其(可能是加密的)存储在登录cookie中,这通常也是一个坏主意。
#2
0
I'd like to have users provide their SQL server uid/password at runtime and determine their rights based on that. .. Is this even possible? It doesn't seem to be documented anywhere as an option.
我想让用户在运行时提供他们的SQL服务器uid /密码,并根据它确定他们的权限。 ..这甚至可能吗?它似乎没有作为选项记录在任何地方。
Definitely possible. You could use OWIN Cookie Authentication Middleware, and store both username and password inside claims. Then construct connection string on-the-fly every times application needs to access database.
绝对有可能。您可以使用OWIN Cookie身份验证中间件,并在声明中存储用户名和密码。然后,每次应用程序需要访问数据库时,即时构建连接字符串。
Although it is not easy to decrypt the authentication cookie on SSL as of today, you will have to keep in mind that both username and plain password will be sending to server on every page request. It might be compliance issue, if you are required to meet.
虽然截至今天在SSL上解密身份验证cookie并不容易,但您必须记住,用户名和普通密码都将在每个页面请求时发送到服务器。如果您需要见面,可能是合规问题。
Although you could use Session State, but it is not a good practice to store plain password in server memory either.
虽然您可以使用会话状态,但在服务器内存中存储普通密码也不是一个好习惯。
Is there a compelling reason why I shouldn't do this, even if it is possible? I'm not totally averse to learning a new security paradigm if it's what I need to do to keep things really safe.
有没有令人信服的理由为什么我不应该这样做,即使有可能?我不是完全不愿意学习新的安全范例,如果这是我需要做的事情来保证事情真的安全。
Although the approach is very common for windows applications which access straight to database (without web service), it is not common in web applications.
虽然这种方法对于直接访问数据库(没有Web服务)的Windows应用程序非常常见,但在Web应用程序中并不常见。
It is ok, if you have a couple of users. However, if you have a lot of users come and go, it becomes full time jobs to configure security settings in SQL Server for every user. In addition, you cannot restrict who can view what pages.
没关系,如果你有几个用户。但是,如果您有很多用户来来去去,那么在SQL Server中为每个用户配置安全设置都会成为全职工作。此外,您不能限制谁可以查看哪些页面。
It is why web application normally keeps the authorization logic inside the application itself. So, we can restrict page access using AuthroizeAttribute. If you want to go that route, then I highly recommend you to look at ASP.NET Identity.
这就是Web应用程序通常将授权逻辑保留在应用程序本身内部的原因。因此,我们可以使用AuthroizeAttribute限制页面访问。如果你想走这条路,那么我强烈建议你看看ASP.NET身份。
#3
-1
;TLDR
The best naive solution is to use a specialized user for your application only, and the credentials would be stored in a configuration file on your webserver, outside of public access (accessible to the application only).
最好的天真解决方案是仅为您的应用程序使用专用用户,并且凭据将存储在Web服务器上的配置文件中,不在公共访问范围内(仅对应用程序可访问)。
In this manner, no user credentials are compromised, and if your application is hacked, you have only granted it limited functionality (and you can easily change the credentials to new ones once the threat has been eliminated).
通过这种方式,没有用户凭据被泄露,并且如果您的应用程序被黑客攻击,您只能授予它有限的功能(并且一旦消除了威胁,您就可以轻松地将凭据更改为新凭据)。
Here are the things you don't want to do and why:
以下是您不想做的事情以及原因:
-
If you have an ODBC connection created in whatever middle tier you want (it doesn't matter if it's .NET, Java, Python, PHP, whatever), you are supplying credentials to obtain a secure connection - either by connection parameters, or a connection string like this:
如果您在所需的任何中间层创建了ODBC连接(无论是.NET,Java,Python,PHP都无关紧要),您提供凭据以获取安全连接 - 通过连接参数或像这样的连接字符串:
Driver={SQL Server};Server=myServerAddress;Database=myDataBase;Uid=myUsername; Pwd=myPassword;
Driver = {SQL Server}; Server = myServerAddress; Database = myDataBase; Uid = myUsername; PWD = MYPASSWORD;
Or you are setting properties on an object in memory, which accomplishes the same thing.
或者你在内存中的对象上设置属性,这完成了同样的事情。
So, you could choose to use an individual user's authentication against that database in place of a special database user that has all permissions, to limit the permissions of that ODBC connection.
因此,您可以选择对该数据库使用单个用户的身份验证来代替具有所有权限的特殊数据库用户,以限制该ODBC连接的权限。
-
Reasons not to do this:
不这样做的原因:
-
You will be unable to distinguish whether your application is making a change in the database or whether that user logged in to your database and made the change.
您将无法区分您的应用程序是在数据库中进行更改,还是该用户是否登录到您的数据库并进行了更改。
-
You expose your database login within your application. Depending on where it's being deployed, this could be a security risk if you don't prevent against brute force attacks (or any of kind of attack that could compromise a user's credentials).
您在应用程序中公开数据库登录。根据部署的位置,如果您不防止暴力攻击(或任何可能危及用户凭据的攻击),这可能会带来安全风险。
-
You are not able to define precisely what your application is allowed to do. Someone could create a super user in your database, then log in through your application, and take complete control of your database.
您无法准确定义允许应用程序执行的操作。有人可以在您的数据库中创建一个超级用户,然后通过您的应用程序登录,并完全控制您的数据库。
-
Do not create a single user for your application and give it all permissions. If there is a bug in your application, it may result in the database being left in an inconsistent state (bad data, missing entries, etc). Only give it the permissions it needs for specific operations - lock it down as much as possible.
不要为您的应用程序创建单个用户并为其授予所有权限。如果您的应用程序中存在错误,则可能导致数据库处于不一致状态(错误数据,缺少条目等)。只为其提供特定操作所需的权限 - 尽可能将其锁定。
-
#1
1
Don't use end-user credentials directly for your database.
不要将最终用户凭据直接用于数据库。
The fundamental problem with this approach is that you'd need to be able to retrieve the end-user's password to connect to the database.
这种方法的基本问题是您需要能够检索最终用户的密码才能连接到数据库。
This means that if your systems are ever compromised, you'll leak passwords for other sites (for users who reuse passwords).
这意味着如果您的系统遭到入侵,您将泄露其他站点的密码(对于重用密码的用户)。
As a compromise, you can make the DB password an iterated hash of the end-user's password.
However, you'd still need to store that (perhaps encrypted) in the login cookie, which is generally also a bad idea.
作为折衷方案,您可以将DB密码作为最终用户密码的迭代哈希值。但是,您仍然需要将其(可能是加密的)存储在登录cookie中,这通常也是一个坏主意。
#2
0
I'd like to have users provide their SQL server uid/password at runtime and determine their rights based on that. .. Is this even possible? It doesn't seem to be documented anywhere as an option.
我想让用户在运行时提供他们的SQL服务器uid /密码,并根据它确定他们的权限。 ..这甚至可能吗?它似乎没有作为选项记录在任何地方。
Definitely possible. You could use OWIN Cookie Authentication Middleware, and store both username and password inside claims. Then construct connection string on-the-fly every times application needs to access database.
绝对有可能。您可以使用OWIN Cookie身份验证中间件,并在声明中存储用户名和密码。然后,每次应用程序需要访问数据库时,即时构建连接字符串。
Although it is not easy to decrypt the authentication cookie on SSL as of today, you will have to keep in mind that both username and plain password will be sending to server on every page request. It might be compliance issue, if you are required to meet.
虽然截至今天在SSL上解密身份验证cookie并不容易,但您必须记住,用户名和普通密码都将在每个页面请求时发送到服务器。如果您需要见面,可能是合规问题。
Although you could use Session State, but it is not a good practice to store plain password in server memory either.
虽然您可以使用会话状态,但在服务器内存中存储普通密码也不是一个好习惯。
Is there a compelling reason why I shouldn't do this, even if it is possible? I'm not totally averse to learning a new security paradigm if it's what I need to do to keep things really safe.
有没有令人信服的理由为什么我不应该这样做,即使有可能?我不是完全不愿意学习新的安全范例,如果这是我需要做的事情来保证事情真的安全。
Although the approach is very common for windows applications which access straight to database (without web service), it is not common in web applications.
虽然这种方法对于直接访问数据库(没有Web服务)的Windows应用程序非常常见,但在Web应用程序中并不常见。
It is ok, if you have a couple of users. However, if you have a lot of users come and go, it becomes full time jobs to configure security settings in SQL Server for every user. In addition, you cannot restrict who can view what pages.
没关系,如果你有几个用户。但是,如果您有很多用户来来去去,那么在SQL Server中为每个用户配置安全设置都会成为全职工作。此外,您不能限制谁可以查看哪些页面。
It is why web application normally keeps the authorization logic inside the application itself. So, we can restrict page access using AuthroizeAttribute. If you want to go that route, then I highly recommend you to look at ASP.NET Identity.
这就是Web应用程序通常将授权逻辑保留在应用程序本身内部的原因。因此,我们可以使用AuthroizeAttribute限制页面访问。如果你想走这条路,那么我强烈建议你看看ASP.NET身份。
#3
-1
;TLDR
The best naive solution is to use a specialized user for your application only, and the credentials would be stored in a configuration file on your webserver, outside of public access (accessible to the application only).
最好的天真解决方案是仅为您的应用程序使用专用用户,并且凭据将存储在Web服务器上的配置文件中,不在公共访问范围内(仅对应用程序可访问)。
In this manner, no user credentials are compromised, and if your application is hacked, you have only granted it limited functionality (and you can easily change the credentials to new ones once the threat has been eliminated).
通过这种方式,没有用户凭据被泄露,并且如果您的应用程序被黑客攻击,您只能授予它有限的功能(并且一旦消除了威胁,您就可以轻松地将凭据更改为新凭据)。
Here are the things you don't want to do and why:
以下是您不想做的事情以及原因:
-
If you have an ODBC connection created in whatever middle tier you want (it doesn't matter if it's .NET, Java, Python, PHP, whatever), you are supplying credentials to obtain a secure connection - either by connection parameters, or a connection string like this:
如果您在所需的任何中间层创建了ODBC连接(无论是.NET,Java,Python,PHP都无关紧要),您提供凭据以获取安全连接 - 通过连接参数或像这样的连接字符串:
Driver={SQL Server};Server=myServerAddress;Database=myDataBase;Uid=myUsername; Pwd=myPassword;
Driver = {SQL Server}; Server = myServerAddress; Database = myDataBase; Uid = myUsername; PWD = MYPASSWORD;
Or you are setting properties on an object in memory, which accomplishes the same thing.
或者你在内存中的对象上设置属性,这完成了同样的事情。
So, you could choose to use an individual user's authentication against that database in place of a special database user that has all permissions, to limit the permissions of that ODBC connection.
因此,您可以选择对该数据库使用单个用户的身份验证来代替具有所有权限的特殊数据库用户,以限制该ODBC连接的权限。
-
Reasons not to do this:
不这样做的原因:
-
You will be unable to distinguish whether your application is making a change in the database or whether that user logged in to your database and made the change.
您将无法区分您的应用程序是在数据库中进行更改,还是该用户是否登录到您的数据库并进行了更改。
-
You expose your database login within your application. Depending on where it's being deployed, this could be a security risk if you don't prevent against brute force attacks (or any of kind of attack that could compromise a user's credentials).
您在应用程序中公开数据库登录。根据部署的位置,如果您不防止暴力攻击(或任何可能危及用户凭据的攻击),这可能会带来安全风险。
-
You are not able to define precisely what your application is allowed to do. Someone could create a super user in your database, then log in through your application, and take complete control of your database.
您无法准确定义允许应用程序执行的操作。有人可以在您的数据库中创建一个超级用户,然后通过您的应用程序登录,并完全控制您的数据库。
-
Do not create a single user for your application and give it all permissions. If there is a bug in your application, it may result in the database being left in an inconsistent state (bad data, missing entries, etc). Only give it the permissions it needs for specific operations - lock it down as much as possible.
不要为您的应用程序创建单个用户并为其授予所有权限。如果您的应用程序中存在错误,则可能导致数据库处于不一致状态(错误数据,缺少条目等)。只为其提供特定操作所需的权限 - 尽可能将其锁定。
-