Capture the user who deleted the row in Temporal table

Question

I understand that temporal tables are intended to give you a point in time view of the data. I am using temporal tables for auditing purpose. I have the following Temporal table.

Lets assume this is the current state of the Temporal table:

ID  RoleID  UserID      ModifiedBy
------------------------------------------
1   11      1001        [email protected]
2   22      1001        [email protected]
3   33      1002        [email protected]
4   11      1003        [email protected]

I have a web application using EF Core. My EF code always sets the ModifiedBy to currently logged in user. I logged into the application as [email protected] and deleted a record with ID 2. SQL Server will automatically insert the deleted record into the history table as expected and keep ModifiedBy as [email protected] because that was the point in time value of ModifiedBy column.

However now the system does not know who deleted the row. In this scenario [email protected] is the one who actually deleted the row. How do I capture the user who deleted the record? What are my options here?

Answer 1

As you correctly mentioned, the system was behaving as expected, i.e., the history table storing the most recent row (complete row) (by transferring) from the temporal table based on the system time (Ref.: https://learn.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables?view=sql-server-ver15). However, it seems you wanted to overwrite the value in ModifiedBy Column before such a transfer transaction takes place. As this is against the core principle (what, when, and who - for each transaction) of auditing, the system was correct in not allowing it to happen.

A suggestion is to alter the original table in the context to have one more column to map the current userId (Ref.: Default logged in user to SQL Server column). This can be achieved by altering the table structure either in the database itself or by modifying your EF code to add a new column and store the current user.

Answer 2

Auditing deletes is problematic on its own, but so too is tracking and storing the Current User at all in EF or disconnected environments where the user does not have their own secure connection to the database, so we cannot use the database user to determine who the Current user is.

Whilst implementing a "Soft" delete is an option to avoid this, it introduces a new structural dependency (row state flag) that ALL queries against affected tables to take the row state flag into account. Not a bad thing, but its a substantial change imposed on the entire application runtime, including any ad-hoc maintenance, reports and data integrations that might not use the EF context.

Have a look at CONTEXT_INFO, there is a good writeup in this answer to a DBA post: Passing info on who deleted record onto a Delete trigger

utilising CONTEXT_INFO allows us to move the user audit management from the EF code into the database if you want to, the benefit to this is that your auditing will now pickup modifications performed by all interactions with the database, not just the EF application process.

There is an old post about setting CONTEXT_INFO in EF that still mostly applies: Entity Framework and User Context

1. Create a SP to set the CONTEXT_INFO, this is a 128 byte value.
   Add this to your migration scripts

    Create Procedure [dbo].[SetEFUserContext]
    (
        @UserID int,
        @UserName Varchar(100)
    )
    AS
    Begin
    Begin Try
        Declare @CONTEXT_INFO Varbinary(max)
   
        SET @CONTEXT_INFO =cast('UserId='+CONVERT(varchar(10),@UserID)
        +',Name=' + RTrim(@UserName)
        +REPLICATE(' ',128) as varbinary(128))
   
        SET CONTEXT_INFO @CONTEXT_INFO
   
    End Try
    Begin Catch
        Declare @Errmsg Varchar(max),@ErrSeverity int
        Set @Errmsg=ERROR_MESSAGE()
        Set @ErrSeverity=ERROR_SEVERITY()
        Raiserror(@Errmsg,@ErrSeverity,1)
    End Catch
    End
   

2. Override the SaveChanges() method in your DbContext to execute the above SP before each database model change:

    public override int SaveChanges()
    {           
        SetUserContext();
   
        return base.SaveChanges();
    }
   
    public int UserId
    {
        // Implement your own logic to resolve the current user id
        get; set;
    }
   
    public int UserName
    {
        // Implement your own logic to resolve the current user name
        get; set;
    }
   
    private void SetUserContext ()
    {
        if (String.IsNullOrWhiteSpace(UserName))
            return;
   
        //Open a connection to the database so the session is set up
        this.Database.Connection.Open();
   
        //Set the user context
        //Cannot use ExecuteSqlCommand here as it will close the connection
        using (var cmd = this.Database.Connection.CreateCommand())
        {
            var userNameParam = cmd.CreateParameter();
            userNameParam.ParameterName = "@userName";
            userNameParam.Value = UserName;
   
            var userIdParam = cmd.CreateParameter();
            userIdParam.ParameterName = "@userId";
            userIdParam.Value = UserId;
   
            cmd.CommandText = "SetEFUserContext";
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.Parameters.Add(userIdParam);
            cmd.Parameters.Add(userNameParam);
   
            cmd.ExecuteNonQuery();
        };
    }
   

3. Use a trigger on the tables that you to modify the row before deleting it. This way the final user is updated into the row and your existing temporal logic should retain the user.

   NOTE: This has to be configured for each table, you can script it from EF, or write an SP in SQL to generate them, the following is just to demonstrate the usage on a single table called table1

    CREATE TRIGGER auditTemporalDeleteTrigger
        ON database1.dbo.table1
        FOR DELETE
    AS
        DECLARE @user VARCHAR(100), @userId int;
        SELECT @user = SYSTEM_USER
   
        -- support for domain credentials, omit the domain name
        IF(CHARINDEX('\', @user) > 0)
            SELECT @user = SUBSTRING(@user, CHARINDEX('\', @user) + 1, 25);
        SELECT @user = SUBSTRING(@user, 1, 100);
   
        --To support EF or web apps with single shared connection, use Context_info
        DECLARE @sCONTEXT_INFO varchar(128) = (SELECT CAST(CONTEXT_INFO() AS VARCHAR) FROM sys.SYSPROCESSES WHERE SPID =@@SPID )
        IF @sCONTEXT_INFO like '%UserId%'
        BEGIN
            SELECT @userId = Substring(@sCONTEXT_INFO, CHARINDEX('UserId=', @sCONTEXT_INFO) + 7, CHARINDEX(',', @sCONTEXT_INFO, CHARINDEX('UserId=', @sCONTEXT_INFO)) - CHARINDEX('UserId=', @sCONTEXT_INFO) - 7)
            SELECT @User = RIGHT(RTRIM(@sCONTEXT_INFO), LEN(RTRIM(@sCONTEXT_INFO)) - CHARINDEX('Name=', @sCONTEXT_INFO) - 5 + 1)-- + 1 due to RIGHT function and CHARINDEX
        END
   
        -- Update the record before the delete, to affect the logs
        UPDATE table1
        SET ModifiedBy = @User, UserID = @userId     
        WHERE ID IN (SELECT ID FROM deleted);
   
        -- Actually perform the delete now
        DELETE FROM table1
        WHERE ID IN (SELECT ID FROM deleted);
   
    GO
   

If you go down this route, it's not much more effort to implement AFTER triggers for inserts and updates so you can maintain the ModifiedBy and UserID columns without having to pollute your EF runtime any more than just setting the CONTEXT_INFO.

You can write this into your Migration script generation logic or, as I do now, you can write an SP to generate and maintain the triggers for all the tables that you want to track audits on.

This advice works just the same for custom audit logging as well, except you can have a single trigger that covers AFTER insert,update and delete without having to intercept and manually perform the DELETE as we have done here.

I tried to mock this up in SqlFiddle, but it ran out of memory. I didn't trust it at first, but the FOR DELETE trigger like this works great for temporal tables!