主页 / dba / 问题 / 134890
Accepted
Alexei
Asked: 2016-04-11 05:23:05 +0800 CST

使用持久表注入连接上下文信息

  • 772

我正在开发一个应用程序，该应用程序具有几个严重依赖存储过程的遗留模块（没有 ORM，因此所有获取和数据持久性都是通过存储过程完成的）。

遗留模块的安全性依赖于SUSER_NAME()获取当前用户并应用安全规则。

我正在迁移它以使用 ORM（实体框架），SQL 连接器将使用通用用户连接到数据库（SQL Server），因此我必须为许多程序提供当前用户名。

为了避免更改 .NET 代码，我想到了在建立新连接时以某种方式在上下文中“注入”当前用户：

CREATE TABLE dbo.ConnectionContextInfo 
(
    ConnectionContextInfoId INT NOT NULL IDENTITY(1, 1) CONSTRAINT PK_ConnectionContextInfo PRIMARY KEY,
    Created DATETIME2 NOT NULL CONSTRAINT DF_ConnectionContextInfo DEFAULT(GETDATE()),
    SPID INT NOT NULL,
    AttributeName VARCHAR(32) NOT NULL, 
    AttributeValue VARCHAR(250) NULL,
    CONSTRAINT UQ_ConnectionContextInfo_Info UNIQUE(SPID, AttributeName)
)
GO

当打开连接（或重用，因为使用了连接池）时，使用以下命令：

exec sp_executesql N'
    DELETE FROM dbo.ConnectionContextInfo WHERE SPID = @@SPID AND AttributeName = @UsernameAttribute;
    INSERT INTO dbo.ConnectionContextInfo (SPID, AttributeName, AttributeValue) VALUES (@@SPID, @UsernameAttribute, @Username);
',N'@UsernameAttribute nvarchar(8),@Username nvarchar(16)',@UsernameAttribute=N'Username',@Username=N'domain\username'
go

（0 CPU，约 15 次读取，<6 毫秒）

标量函数允许轻松获取当前用户：

alter FUNCTION dbo.getCurrentUser()
RETURNS VARCHAR(250)
AS
BEGIN
    DECLARE @ret VARCHAR(250) = (SELECT AttributeValue FROM ConnectionContextInfo where SPID = @@SPID AND AttributeName = 'Username')
    -- fallback to session current, if no data is found on current SPID (i.e. call outside of the actual application)
    RETURN ISNULL(@ret, SUSER_NAME())
END
GO

从数据层的角度来看，这种方法是否有任何警告（稳健性、性能等）？

谢谢。

sql-server connections

3 个回答

  1. Best Answer

    在性能方面，每次打开连接时都会产生 和 的DELETE开销INSERT。或者，您可以为此目的使用内置连接 CONTEXT_INFO。下面的示例将信息存储在一个固定长度的 48 字节结构中。

    EXEC sp_executesql N'
    DECLARE @ContextInfo binary(48);
    SET @ContextInfo = CAST(CAST(@UsernameAttribute AS nchar(8)) + CAST(@Username AS nchar(16)) AS binary(48));
',N'@UsernameAttribute nvarchar(8),@Username nvarchar(16)',@UsernameAttribute=N'Username',@Username=N'domain\username'
GO


CREATE FUNCTION dbo.getCurrentUser()
RETURNS VARCHAR(250)
AS
BEGIN
DECLARE
      @ContextInfo binary(48) = CONTEXT_INFO()
    , @Username nvarchar(16);
    SET @Username = RTRIM(CAST(SUBSTRING(@ContextInfo, 17, 32) AS nvarchar(16)));

    RETURN ISNULL(@Username, SUSER_NAME());

END
GO

    此外，sp_set_session_context和SESSION_CONTEXT()在 SQL Server 2016 和 Azure SQL 数据库中可用。如果您可以使用，那将是一种更清洁的方法。

    • 4

  2. 我建议以有助于最小化页面争用的方式预先分配上下文表中的行。

    这是我实际推荐使用随机生成的 GUID 作为表集群键的极少数情况之一。此密钥将充当任何给定 SPID 的页面位置的随机发生器，以减少页面争用。

    CREATE TABLE dbo.ConnectionContextInfo 
(
    SlotID UNIQUEIDENTIFIER 
        CONSTRAINT PK_ConnectionContextInfo 
        PRIMARY KEY CLUSTERED
        DEFAULT (NEWID())
    , SPID INT NOT NULL
    , AttributeName VARCHAR(128) NOT NULL 
    , AttributeValue VARCHAR(255) NULL
    , CONSTRAINT UQ_ConnectionContextInfo_Info 
           UNIQUE(SPID, AttributeName)
);
GO
CREATE INDEX IX_ConnectionContextInfo_Lookups
ON dbo.ConnectionContextInfo(SPID, AttributeName);
GO

    这将使用所需的行预先填充表格，每个 spid/属性组合一个。

    ;WITH Numbers AS 
(
    SELECT TOP(32767) 
        rn = ROW_NUMBER() OVER (ORDER BY o1.object_id)
    FROM sys.objects o1
        , sys.objects o2
        , sys.objects o3
)
, Attributes AS
(
    SELECT AttrName = N'UserName'
    UNION ALL
    SELECT AttrName = N'SomeOtherAttribute'
)
INSERT INTO dbo.ConnectionContextInfo (SPID, AttributeName) 
SELECT rn
    , AttrName
FROM Numbers
    , Attributes;

    如果你真的需要使用sp_executesql，我会这样做： 

    EXEC sys.sp_executesql N'UPDATE dbo.ConnectionContextInfo 
SET AttributeValue = @AttributeValue
WHERE SPID = @@SPID
    AND AttributeName = @AttributeName;'
    , N'@AttributeName nvarchar(128), @AttributeValue nvarchar(128)'
    , @AttributeName = N'Username'
    , @AttributeValue = N'domain\username';
GO

    我的 spid 的结果：

    SELECT * 
    , plc.*
FROM dbo.ConnectionContextInfo
CROSS APPLY sys.fn_PhysLocCracker(%%PhysLoc%%) plc 
WHERE SPID = @@SPID;

    在此处输入图像描述

    sp_executesql我建议不要使用，而是使用存储过程来进行更新，这样您就可以轻松地包含一些错误处理，并且可以在服务器端自由更新此代码而不会影响客户端。例如：

    IF OBJECT_ID('dbo.UpdateConnectionContextInfo') IS NOT NULL
DROP PROCEDURE UpdateConnectionContextInfo;
GO
CREATE PROCEDURE dbo.UpdateConnectionContextInfo
(
    @AttributeName NVARCHAR(128)
    , @AttributeValue NVARCHAR(255)
)
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE dbo.ConnectionContextInfo 
    SET AttributeValue = @AttributeValue
    WHERE SPID = @@SPID
        AND AttributeName = @AttributeName
    RETURN @@ROWCOUNT;
END
GO

    @AttributeName如果传递无效，这会将 @RetVal 设置为 0 ：

    DECLARE @RetVal INT;
EXEC @RetVal = dbo.UpdateConnectionContextInfo 
    @AttributeName = 'UserName', @AttributeValue = 'SomeUser';
SELECT @RetVal;
    • 1

  3. 我可以看到这种方法的三个小问题和一个主要问题：

    小问题：

    1. 您DELETE在即席查询中的语句使用以下谓词：

      AttributeName = @UsernameAttribute

      相反，您应该只进行过滤，SPID = @@SPID因为您不希望该 SPID 的最后一个实例中的任何陈旧值与您当前的值混合在一起。

    2. 在您的ConnectionContextInfo表中，两Attribute%列都定义为VARCHAR，但在临时查询中，您将参数定义为NVARCHAR，甚至在字符串前面加上N。您应该更新表，使其也被定义为NVARCHAR.

    3. 在高峰使用时间插入的来自较高 SPID 值的陈旧数据将持续相当长的一段时间，因为DELETE直到下一次使用 SPID 时才会调用，这可能永远不会。您可以创建每天运行一次的 SQL Server 代理作业，也可以创建DELETEX 天前创建的行。

    主要问题（和解决方案）：

    根据您对@Dan 的回答所做的评论CONTEXT_INFO，由于大小限制了您将来添加更多属性的能力，您无法使用，尤其是在您使用NVARCHAR.

    幸运的是，您不需要永久表。您可以通过使用全局临时表来简化这一点。这将消除对DELETE先前行的需要，并且很可能有一个 SQL 代理作业清理过时的记录，或者预分配一堆行，但仍需要将任何与该 SPID 匹配的行更新为空字符串或NULL每个连接。

    建立连接后，您只需要创建表。然后插入您喜欢的任何键/值对。当 Connection 关闭（非池化和池化连接）或下一个要重用该 Connection 的 Session 执行其第一条语句并sp_reset_connection运行内部进程（池化连接）时，该表将自动删除。

    现在，您可能会问自己：

    • 存储过程（或临时查询）结束时临时表不会被清理吗？

      如果它是一个本地临时表（即#Name），那么是的，它会在它创建的进程/子进程结束时被清理，并且在父上下文中不可用。但是全局临时表（即##Name）在创建它们的过程结束后仍然存在，并且在父上下文中可用。

    • 因为临时表是全局的，所以它们不能共享相同的名称。

      正确，在语句中使用标准表名将CREATE TABLE不起作用，因为多个会话会相互冲突。我们只需要一种方法来使用 Session 可用的东西来区分表名，这是 Session 独有的，但不是从应用程序传入的，因为代码只能在应用程序中工作，目标是不更改应用程序代码。因此，只需将该@@SPID值附加到一个已知的固定前缀，然后您就可以在该会话中的任何位置推断表名。

      就像是：

      CREATE PROCEDURE dbo.InitializeSessionContext
AS
SET NOCOUNT ON;

DECLARE @Query NVARCHAR(MAX),
        @Template NVARCHAR(MAX) = N'

CREATE TABLE ##SessionContext{{SPID}}
(
  AttributeName NVARCHAR(32) NOT NULL, 
  AttributeValue NVARCHAR(250) NULL,
  Created DATETIME2 NOT NULL CONSTRAINT DF_SessionContext{{SPID}} DEFAULT(GETDATE())
);';

SET @Query = REPLACE(@Template, N'{{SPID}}', @@SPID);

EXEC(@Query);
GO

    • 与问题中显示的 UDF 不同，这种方法需要动态 SQL 和访问临时表，这两者都不被允许。

      正确，这些都不能在 T-SQL 函数中完成，但它们可以通过其他两种方式完成：

      • 使用 T-SQL 存储过程通过OUTPUT参数将返回值传回，或者
      • 创建 SQLCLR 标量 UDF。使用进程内上下文连接（即"Context Connection = true"，可以将程序集标记为WITH PERMISSION_SET = SAFE.SQLCLR UDF 既可以执行动态 SQL，也可以访问本地临时表。
    • 1

相关问题