SQL服务器模拟和连接池

I've been given the task of writing an web interface for a legacy database we have where all users have database accounts and are assigned roles accordingly (we have triggers all over the place recording when users do certain things, all based on user_name()).

我的任务是为一个遗留数据库编写一个web界面，在这个数据库中，所有用户都有数据库帐户，并相应地分配角色(当用户做某些事情时，我们在各处都有触发器，所有这些都基于user_name())))。

In order to use anything remotely modern and to avoid storing the user's password in plain text I'm connecting w/ an App-level account that has impersonation privileges for each user, and I'm trying to run Execute As User=@username and Revert to set and reset the execution context before and after running any SQL.

进行任何现代为了使用,避免将用户的密码存储在纯文本我连接w / App-level扮演特权为每个用户帐户,我试图运行执行用户= @用户名和恢复设置和重置执行上下文之前和之后运行任何SQL。

Unfortunately, the connection pooling's reset_connection call is mucking w/ my Connection, and it winds up throwing some nasty errors about Physical connection being invalid...

不幸的是，连接池的reset_connection调用会破坏w/我的连接，并且它会抛出一些关于物理连接无效的严重错误……

I can get around this error by not using the connection pool. But then my application user needs an insane amount of privileges to actually perform the impersonation. Furthermore, killing connection pooling is a bummer...

我可以通过不使用连接池来避免这个错误。但是，我的应用程序用户需要大量的特权来实际执行模拟。此外，终止连接池是一个大问题……

How can I do this without sacrificing security or performance? Keep in mind I can't change the fact that my users have database logins, and I'm really not excited about storing user passwords in a retrievable manner. Is my only option bypassing the connection pool so I can impersonate (and using an sa user so I have sufficient permissions to actually impersonate someone)?

我如何在不牺牲安全性或性能的情况下做到这一点呢?请记住，我无法改变我的用户拥有数据库登录的事实，而且我真的不希望以可检索的方式存储用户密码。我唯一的选择是绕过连接池，以便我可以模拟(并使用sa用户，以便我有足够的权限实际模拟某人)吗?

2 个解决方案

#1

To implement a kind of "fake" delegation without huge changes in application/database code I propose using context_info() to transport the current user to the database and replace the calls to user_name() with calls to dbo.fn_user_name().

为了实现一种不需要对应用程序/数据库代码进行重大更改的“伪”委托，我建议使用context_info()将当前用户传输到数据库，并将对user_name()的调用替换为对dbo.fn_user_name()的调用。

An example on how to build up this solution

关于如何构建这个解决方案的示例

Create fn_user_name() function

I would create a function fn_user_name which will extract the user name from the context_info() on the connection or return user_name() when there is no context info available. note that the connection context is a 128 byte binary. Anything you put on there will be padded with zero characters, to work around this I stuff the values with whitespace.

我将创建一个函数fn_user_name，它将从连接上的context_info()提取用户名，或者在没有上下文信息时返回user_name()。注意，连接上下文是一个128字节的二进制文件。你在那里放的任何东西都会被填充为零字符，为了解决这个问题，我用空格填充值。

create function dbo.fn_user_name()
returns sysname
as
begin
    declare @user sysname = rtrim(convert(nvarchar(64), context_info()))
    if @user is null 
        return user_name()
    return @user
end
go

Now you find replace all calls to user_name() in your code and replace them with this function.

现在找到替换代码中对user_name()的所有调用，并用这个函数替换它们。

Embed the context in your db calls in .net

There are 2 options here. Or you create your own SqlConnection class, or you create a factory method which will return an open sqlconnection like shown below. The factory method has as problem that every query you run will be 2 db calls. It is the least code to write though.

这里有两个选项。或者创建自己的SqlConnection类，或者创建一个工厂方法，该方法将返回一个打开的SqlConnection，如下所示。工厂方法的问题是，您运行的每个查询都是2个db调用。这是最不需要编写的代码。

    public SqlConnection CreateConnection(string connectionString, string user)
    {
        var conn = new SqlConnection(connectionString);
        using (var cmd = new SqlCommand(
            @"declare @a varbinary(128) = convert(varbinary(128), @user + replicate(N' ', 64 - len(@user)))
              set context_info @a", conn))
        {
            cmd.Parameters.Add("@user", SqlDbType.NVarChar, 64).Value = user;
            conn.Open();
            cmd.ExecuteNonQuery();
        }
        return conn;
    }

you would use this as:

你可以这样使用:

using(var conn = CreateConnection(connectionString, user))
{
   var cmd = new SqlCommand("select 1", conn);
   return conn.ExecuteScalar()
}

For the alternate version of SqlConnection you will need to overload DbConnection and implement all methods of SqlConnection. The execute methods will prepend the query below and pass in the username as extra parameter.

对于SqlConnection的替代版本，您将需要重载DbConnection并实现SqlConnection的所有方法。execute方法将在下面进行查询，并将用户名作为额外参数传递。

declare @a varbinary(128) = convert(varbinary(128), @user + replicate(N' ', 64 - len(@user)))
set context_info @a 

that class would then be used as:

该类将被用作:

using(var conn = new SqlContextInfoConnection(connectionString, user))
{
   var cmd = new SqlCommand("select 1", conn);
   conn.open;
   return conn.ExecuteScalar()
}

I would personally implement option 2 as it would be closer to the way a normal SqlConnection works.

我个人会实现选项2，因为它更接近正常SqlConnection的工作方式。

#2

I know this is old, however this post is the only useful resource I've found, and I thought I'd share our solution that builds on Filip De Vos answer.

我知道这是旧的，然而这篇文章是我发现的唯一有用的资源，我想我将分享我们的解决方案，建立在Filip De Vos的答案之上。

We also have a legacy VB6 app that utilises sp_setapprole (I appreciate this doesn't quite fit the OPs original post). Our .NET components that share the same database (and are essentially part of the application framework) are heavily based on Linq to SQL.

我们还有一个使用sp_setapprole的遗留VB6应用程序(我很感激它不太适合OPs最初的帖子)。共享相同数据库(本质上是应用程序框架的一部分)的. net组件主要基于Linq到SQL。

Setting the approle for a datacontext connection proved troublesome, considering the number of times a connection is opened and closed.

考虑到连接被打开和关闭的次数，设置datacontext连接的批准权被证明是麻烦的。

We ended up using writing a simple wrapper as suggested above. The only overridden methods are Open() and Close(), which is where the approle is set and unset.

最后，我们按照上面的建议编写了一个简单的包装器。惟一重写的方法是Open()和Close()，这是设置和未设置approle的地方。

Public Class ManagedConnection
    Inherits Common.DbConnection

    Private mCookie As Byte()
    Private mcnConnection As SqlClient.SqlConnection

    Public Sub New()
        mcnConnection = New SqlClient.SqlConnection()
    End Sub

    Public Sub New(connectionString As String)
        mcnConnection = New SqlClient.SqlConnection(connectionString)
    End Sub

    Public Sub New(connectionString As String, credential As SqlClient.SqlCredential)
        mcnConnection = New SqlClient.SqlConnection(connectionString, credential)
    End Sub

    Public Overrides Property ConnectionString As String
        Get
            Return mcnConnection.ConnectionString
        End Get
        Set(value As String)
            mcnConnection.ConnectionString = value
        End Set
    End Property

    Public Overrides ReadOnly Property Database As String
        Get
            Return mcnConnection.Database
        End Get
    End Property

    Public Overrides ReadOnly Property DataSource As String
        Get
            Return mcnConnection.DataSource
        End Get
    End Property

    Public Overrides ReadOnly Property ServerVersion As String
        Get
            Return mcnConnection.ServerVersion
        End Get
    End Property

    Public Overrides ReadOnly Property State As ConnectionState
        Get
            Return mcnConnection.State
        End Get
    End Property

    Public Overrides Sub ChangeDatabase(databaseName As String)
        mcnConnection.ChangeDatabase(databaseName)
    End Sub

    Public Overrides Sub Close()

        Using cm As New SqlClient.SqlCommand("sp_unsetapprole")
            cm.Connection = mcnConnection
            cm.CommandType = CommandType.StoredProcedure
            cm.Parameters.Add("@cookie", SqlDbType.VarBinary, 8000).Value = mCookie

            cm.ExecuteNonQuery()
        End Using

        mcnConnection.Close()
    End Sub

    Public Overrides Sub Open()
        mcnConnection.Open()

        Using cm As New SqlClient.SqlCommand("sp_setapprole")
            cm.Connection = mcnConnection
            cm.CommandType = CommandType.StoredProcedure
            cm.Parameters.Add("@rolename", SqlDbType.NVarChar, 128).Value = "UID"
            cm.Parameters.Add("@password", SqlDbType.NVarChar, 128)Value = "PWD"
            cm.Parameters.Add("@fCreateCookie", SqlDbType.Bit).Value = True
            cm.Parameters.Add("@cookie", SqlDbType.VarBinary, 8000).Direction = ParameterDirection.InputOutput
            cm.ExecuteNonQuery()

            mCookie = cm.Parameters("@cookie").Value
        End Using
    End Sub

    Protected Overrides Function BeginDbTransaction(isolationLevel As IsolationLevel) As DbTransaction
        Return mcnConnection.BeginTransaction(isolationLevel)
    End Function

    Protected Overrides Function CreateDbCommand() As DbCommand
        Return mcnConnection.CreateCommand()
    End Function
End Class

Before:

之前:

Using dc As New SystemOptionDataContext(sConnectionString)
    intOption= dc.GetIntegerValue("SomeDatabaseOption")
End Using

After:

后:

Using dc As New SystemOptionDataContext(New ManagedConnection(strConnectionString))
    intOption= dc.GetIntegerValue("SomeDatabaseOption")
End Using

Hope this helps others.

希望这可以帮助别人。