4

I am working on an application that has several legacy modules that rely heavily on stored procedure (no ORM, so all fetches and data persistence is done through stored procedures).

Security for legacy modules relies on SUSER_NAME() to get current user and apply security rules.

I am migrating it to use an ORM (Entity Framework) and SQL connector will use a generic user to connect to the database (SQL Server), so I have to provide current username to many procedures.

In order to avoid changes in .NET code, I thought of "injecting" somehow current user in the context when a new connection is made:

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

When a connection is opened (or reused, as a connection pool is used), the following command is used:

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 reads, <6 ms)

A scalar function allows to easily get current user:

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

Are there any caveats (robustness, performance etc.) in this approach from a data tier perspective?

Thanks.

4

In terms of performance, you will incur the overhead of the DELETE and INSERT every time the connection is opened. Alternatively, you could use the built-in connection CONTEXT_INFO for this purpose. The example below stores the information in a fixed-length 48-byte structure.

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

Also, sp_set_session_context and SESSION_CONTEXT() are available in SQL Server 2016 and Azure SQL Database. That's a much cleaner method if available to you.

  • Thank you for the prompt response. I know about CONTEXT_INFO, but I have avoided it because I wanted to easily be able to add other context information if needed (e.g. some culture information) and its maximum size is quite small. sp_set_session_context and SESSION_CONTEXT would be perfect if I wasn't working in SQL Server 2012 :(. – Alexei Apr 10 '16 at 15:11
  • 1
    @Alexei, in SQL 2014 and earlier, the context info table in your question is a good workaround to get around the 128 byte limitation and is less fragile. You could mitigate the performance impact by wrapping the DELETE and INSERT in a transaction. A non-durable table would be an option if you were on SQL 2014 Enterprise Edition. – Dan Guzman Apr 10 '16 at 15:25
  • "DELETE and INSERT in the same transaction" - can I replace it with a MERGE statement? It looks uglier, but seems to be a natural replacement that automatically ensures a transaction. – Alexei Apr 10 '16 at 20:38
  • @Alexei, yes, MERGE will also work. – Dan Guzman Apr 10 '16 at 21:28
1

I would suggest pre-allocating rows in the context table in a way that helps minimize page contention.

This is one of the very few times I'll actually recommend using a randomly generated GUID as the table clustering key. This key will act as a randomizer for the page location for any given SPID to reduce page contention.

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

This will pre-fill the table with the required rows, one per spid/attribute combination.

;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;

If you really need to use sp_executesql, I'd do it like this:

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

The results for my spid:

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

enter image description here

Instead of using sp_executesql, I'd recommend using a stored procedure to do the update so you can easily include some error handling and can freely update this code at the server side without impacting the client. For instance:

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

This will set @RetVal to 0 if the @AttributeName passed is invalid:

DECLARE @RetVal INT;
EXEC @RetVal = dbo.UpdateConnectionContextInfo 
    @AttributeName = 'UserName', @AttributeValue = 'SomeUser';
SELECT @RetVal;
  • Thanks for the detailed response. Pre-allocating the records sounds like a really clever idea since the maximum number of SPIDs is quite small. "This key will act as a randomizer for the page location for any given SPID to reduce page contention." - this is particularly important when having high concurrency, isn't it?. sp_executesql is automatically generated by C# parameterized SqlCommand, but I can easily replace it with a procedure call. – Alexei Apr 10 '16 at 20:47
  • Pre-allocating rows really helps concurrency, especially when combined with "random" page locations for sequential SPIDs. – Max Vernon Apr 10 '16 at 21:45
1

I can see three minor issues and one major issue with this approach:

Minor Issues:

  1. Your DELETE statement in your ad hoc query uses the following predicate:

    AttributeName = @UsernameAttribute
    

    Instead, you should only be filtering on SPID = @@SPID since you wouldn't want any stale values from the last instance of that SPID hanging around, intermixed with your current value(s).

  2. In your ConnectionContextInfo table, both Attribute% columns are defined as VARCHAR, yet in the ad hoc query you have the parameters defined as NVARCHAR and even prefix the strings with N. You should update the table so that it is also defined as NVARCHAR.

  3. Stale data from higher SPID values that get inserted during peak usage times will linger for quite some time as there is no DELETE called until the next time that SPID is used, which could be never. You could either create a SQL Server Agent job to run once per day and DELETE rows created over X days ago.

Major Issue (and a solution):

Based on the comment you made on @Dan's answer, you can't use CONTEXT_INFO due to the size limiting your ability to add more attributes in the future, especially if you are using NVARCHAR.

Fortunately, you don't need a permanent table. You can simplify this a bit by using a Global Temporary Table. This would eliminate the need to DELETE prior rows and most likely have a SQL Agent Job clean up stale records, OR pre-allocate a bunch of rows but still need to update any rows matching that SPID to empty string or NULL per each connection.

Upon establishing the connection, you just need to create the table. Then insert whatever Key/Value pairs you like. The table will automatically get dropped when either the Connection closes (non-pooled and pooled connections) or when the next Session to re-use that Connection executes its first statement and the internal sp_reset_connection process is run (pooled connections).

Now, you might be asking yourself:

  • Won't the temp table get cleaned up when the Stored Procedure (or ad hoc query) ends?

    If it were a Local Temporary Table (i.e. #Name), then yes, it would get cleaned up when the process/sub-process it was created in ends, and won't be available in the parent context. But Global Temporary Tables (i.e. ##Name) survive the ending of the process they were created in and are available in the parent context.

  • Because the Temp Tables are Global, they can't share the same name.

    Correct, using a standard table name in the CREATE TABLE statement won't work because multiple Sessions will clash with each other. We just need a way to differentiate the table name using something available to the Session, unique to the Session, but not passed in from the app because the code would only work from the app and the goal is to not change the app code. Hence, just append the @@SPID value to a known, fixed prefix and then you can infer the table name at any point in that Session.

    Something like:

    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
    
  • Unlike the UDF shown in the Question, this approach requires both Dynamic SQL and accessing temporary tables, neither of which are allowed.

    Correct, neither of those can be done in T-SQL Functions, but they can be done in two other ways:

    • Use a T-SQL Stored Procedure to pass the return value back via an OUTPUT parameter, or
    • Create a SQLCLR Scalar UDF. Use the in-process Context Connection (i.e. "Context Connection = true" and the Assembly can be marked as WITH PERMISSION_SET = SAFE. SQLCLR UDFs can do both Dynamic SQL and access local temporary tables.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.