sql-server - 捕获删除临时表中行的用户

Question

我了解时态表旨在为您提供数据的时间点视图。我正在使用临时表进行审计。我有以下时态表。

让我们假设这是临时表的当前状态：

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

我有一个使用 EF Core 的 Web 应用程序。我的 EF 代码总是将 设置ModifiedBy为当前登录的用户。我以身份登录应用程序[e-mail]并删除了一条 ID 为 2 的记录。SQL Server 将按预期自动将删除的记录插入历史记录表并保持 ModifiedBy 为，[e-mail]因为那是 ModifiedBy 列的时间点值。

但是现在系统不知道是谁删除了该行。在这种情况下[e-mail]，是实际删除该行的人。如何捕获删除记录的用户？我在这里有什么选择？

标签： sql-servertsqlef-core-2.2temporal-tables

Answer 1

审计删除本身是有问题的，但在 EF 或断开连接的环境中跟踪和存储当前用户也是如此，在这些环境中用户没有自己与数据库的安全连接，因此我们无法使用数据库用户来确定谁是当前用户是。

虽然实现“软”删除是避免这种情况的一种选择，但它引入了一种新的结构依赖性（行状态标志），所有查询受影响的表以考虑行状态标志。这不是一件坏事，但它对整个应用程序运行时产生了重大变化，包括可能不使用 EF 上下文的任何临时维护、报告和数据集成。

看看CONTEXT_INFO，在 DBA 帖子的这个答案中有一篇很好的文章：将有关谁删除记录的信息传递到删除触发器

如果您愿意，利用CONTEXT_INFO允许我们将用户审核管理从 EF 代码移动到数据库中，这样做的好处是您的审核现在将拾取与数据库的所有交互执行的修改，而不仅仅是 EF 应用程序进程。

有一篇关于CONTEXT_INFO在 EF 中设置的旧帖子仍然主要适用：实体框架和用户上下文

1. 创建一个 SP 来设置CONTEXT_INFO，这是一个 128 字节的值。
   将此添加到您的迁移脚本中

    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. 覆盖SaveChanges()DbContext 中的方法以在每次数据库模型更改之前执行上述 SP：

    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. 在删除行之前在表上使用触发器来修改行。这样，最终用户将更新到该行中，并且您现有的时间逻辑应该保留该用户。

   注意：这必须为每个表配置，您可以从 EF 编写脚本，或者在 SQL 中编写一个 SP 来生成它们，以下只是为了演示单个表的用法，称为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
   

如果你沿着这条路线走，那么为插入和更新实现AFTER触发器并不费力，这样你就可以维护ModifiedBy和UserID列，而不必污染你的 EF 运行时，而不仅仅是设置CONTEXT_INFO.

您可以将其写入您的迁移脚本生成逻辑，或者像我现在所做的那样，您可以编写一个 SP 来为您要跟踪审计的所有表生成和维护触发器。

这个建议同样适用于自定义审计日志，除了你可以有一个覆盖AFTER insert的触发器，update而delete不必DELETE像我们在这里所做的那样拦截和手动执行。

我试图在 SqlFiddle 中模拟它，但内存不足。一开始我不相信它，但是FOR DELETE像这样的触发器非常适合临时表！