将SQL Server锁定到Web服务器上的特定应用程序

I'm curious, is there a way to tell the SQL Server that a specific group has access to the database only from a single location/application. I have an SQL Server and a Web Server. Our applications use stored procedures and access for each stored procedure is based on the role that is allowed to access it. Then user groups are assigned roles based on the functions they'll preform. As an added layer of security I would like to specify the web application that these users can access the database from.

我很好奇,有没有办法告诉SQL Server特定组只能从一个位置/应用程序访问数据库。我有一个SQL Server和一个Web服务器。我们的应用程序使用存储过程,每个存储过程的访问权限基于允许访问它的角色。然后根据用户组预先形成的功能为用户组分配角色。作为一个额外的安全层,我想指定这些用户可以从中访问数据库的Web应用程序。

I suppose this is overkill. The stored procedure names are hidden from users at all times (all errors are hidden, with generic "sorry this isn't working" displayed to the user). Users only have access to the stored procedures they are allowed to execute. It would just be a nice additional piece of security so should a table accidentally grant everyone full access, the database would only allow full access from one location.

我想这太过分了。存储过程名称始终对用户隐藏(所有错误都被隐藏,向用户显示通用的“抱歉这不起作用”)。用户只能访问允许执行的存储过程。它只是一个很好的额外安全性,所以如果一个表意外地授予每个人完全访问权限,数据库只允许从一个位置进行完全访问。

3 个解决方案

#1

In the connection string, you can set Application Name=MyAppName - this is not real security, but you can check this in your SPs (sysprocesses - in the program_name column) and through sp_who.

在连接字符串中,您可以设置Application Name = MyAppName - 这不是真正的安全性,但您可以在SP(sysprocesses - 在program_name列中)和sp_who中进行检查。

There's nothing you can do about tables - which is why I recommend that nobody be in any role which is allowed access to tables at all (SELECT, INSERT, UPDATE, or DELETE).

关于表,你无能为力 - 这就是为什么我建议没有人在任何允许访问表的角色(SELECT,INSERT,UPDATE或DELETE)。

You can audit this on a regular basis with some automated T-SQL to ensure that no one has done anything stupid.

您可以使用一些自动化的T-SQL定期对此进行审核,以确保没有人做过任何愚蠢的事情。

I'm not advocating this in any way, but you can do something like this for views (comparing the SPID of the current process and program_name):

我不是以任何方式提倡这一点,但你可以为视图做这样的事情(比较当前进程的SPID和program_name):

CREATE VIEW YourViewNameHere
AS
SELECT *
FROM YourTableNameHere
WHERE EXISTS (
    SELECT spid, program_name
    FROM sys.sysprocesses
    WHERE program_name = 'YourProgramNameHere'
        AND spid = @@SPID
)

#2

I suggest run the application/app pool as a service account which has permission on the procedures, but don't grant any permissions to the users themselves. This entails not implementing user security at the database level, but instead at the app level..

我建议将应用程序/应用程序池作为服务帐户运行,该帐户具有该过程的权限,但不向用户自己授予任何权限。这不需要在数据库级别实现用户安全性,而是在应用级别实现用户安全性。

#3

The easiest way is to just lock it down on the user-level. You can run your win/web application under a specific security context that you have the needed rights configured for.

最简单的方法是将其锁定在用户级别。您可以在具有所需权限的特定安全上下文中运行您的win / Web应用程序。

This gives the benefit of forcing users to run your app to interact with SQL and can't just open Enterprise Manager or whatever.

这样可以强制用户运行您的应用程序与SQL交互,而不仅仅是打开企业管理器或其他任何东西。

#1