Last week I came across intresting challenge. I was enhancing an auditing framework for application. This application used SQL triggers for auditing. Basically all tables in database (excluding some system table), had auditing fields such as created date, created by, last modified date and last modified by. Each table where it wanted to audit, had triggers to write values of those field everytime row is updated. Audited data was written to big audit log table. Basically everytime row is updated (or created) above audit fields were updated by app, then trigger write the values of those auditting fields to audit log. But issue was, how to track who deleted a row?
When you delete a row, application cannot write to those auditing fields. I mean you can write, but that will be just stupid to update each row just before it get deleted. That will increase writes just to make it auditable (plus extra audits). So I had to find a solution.
My research showed one of the ways to tackle is SQL Server Session Context.
It is a simple concept introduced in SQL Server 2016. Session context is array of key-value pairs attached to a session.
So in your session, you can specify session specific meta data in this array and read it from SQL server to make different dicissions based on the meta data specified for connection. It is like dictionary attached to your connection.
Session context is stored on memory, therefore, it is fast. It is scoped for session and therefore, isolated from others.
So how to make it work?
First you need to set the values in the context:
EXEC sp_set_session_context
@key = N'UserId',
@value = 123;
Then you can use those values through out your session:
SELECT SESSION_CONTEXT(N'UserId');
If you don't want to make it updatable, you can set the read-only flag:
EXEC sp_set_session_context
@key = N'UserId',
@value = 123,
@read_only = 1;
So how did it help my situation:
Well, just before the delete, I could set the "UserId" or "OperatingUserId" like variable in the session context. Then on the trigger, I can read this value and create the audit log for delete with correct user id.
You will say 'Well, it is still a additional write, just before delete?'.
Yes and No. It is a update to connection, which is held in small part of the memory, so it is not going to write to disk. In fact there is a limitation on session context. Session context only allow maximum 256 key-value pairs, but total size need to be under 1MB (approx).
But be aware it is not a place to hold password like secret information, because if multiple people share the connection (like through application), can see the whole context.
In a connection, where session is shared (like through multi user application). It is vital that you set the userId like values just before where it is going to be use. Otherwise you will be using wrong values, because other users may also set it. There might be concurrency issue, but in my case, rows are deleted in-frequently, so it was ok.
In addition to auditing, you can use it to identify tenant when you application is multi tenant. And it can also be used with row level security (cuatiosly).
Don't over use it, just use it sparegly,
There are know issues, where session context provide wrong results when queries go parallel. But in my case, delete is always single threaded.
