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?