SQL Server 2016 - 时态表 - 如何识别用户

是否可以获取有关修改历史表中数据的用户/连接的信息？我读到了可以使用时态表的审计场景,并且可以检测谁更改了数据.但是我该怎么做呢？

一个看似无懈可击的审计解决方案,它提供了进行每项更改的登录用户的名称(以及对此页面上我之前答案的重大改进):

SELECT 
  e.EmployeeID, e.FirstName, e.Score, 
  COALESCE (eh.LoggedInUser, o.CreatedBy, e.CreatedBy) AS CreatedOrModifiedBy, 
  e.ValidFromUTC, e.ValidToUTC

FROM dbo.Employees FOR SYSTEM_TIME ALL AS e
  LEFT JOIN dbo.EmployeeHistory AS eh    -- history table
    ON e.EmployeeID = eh.EmployeeID AND e.ValidFromUTC = eh.ValidToUTC
    AND e.ValidFromUTC <> eh.ValidFromUTC

OUTER APPLY
  (SELECT TOP 1 CreatedBy 
   FROM dbo.EmployeeHistory 
   WHERE EmployeeID = e.EmployeeID 
   ORDER BY ValidFromUTC ASC) AS o     -- oldest history record

--WHERE e.EmployeeID = 1
ORDER BY e.ValidFromUTC

不使用触发器或用户定义的函数

需要对表格进行细微更改

注意:请注意,SQL Server始终使用UTC而不是本地时间来处理时态表中的时间戳.

编辑:(2018/12/03)当同一记录同时发生多次更新时(例如在交易中),只返回最新的更改(见下文)

说明:

主表和历史表中添加了两个字段:

要记录创建记录的用户的名称 - 正常的SQL默认值:
CreatedBy NVARCHAR(128) NOT NULL DEFAULT (SUSER_SNAME())

要随时记录当前登录用户的名称.计算列:
LoggedInUser AS (SUSER_SNAME())

将记录插入主表时,SQL Server不会在历史记录表中插入任何内容.但CreatedBy由于默认约束,字段记录了创建记录的人员.但是,如果/当记录更新时,SQL Server会将记录插入关联的历史记录表中.这里的关键思想是,进行更改的登录用户的名称被记录到历史表中,即LoggedInUser主表中的字段内容(始终包含登录到连接的人员的名称)是保存到LoggedInUser历史表中的字段中.

这几乎是我们想要的,但并不完全 - 这是一个落后的变化.例如,如果用户Dave插入了记录,但是用户Andrew进行了第一次更新,则"Andrew"被记录为历史表中的用户名,紧邻Dave插入的记录的原始内容.但是,所有信息都在那里 - 它只需要解开.加入系统为ROW START和ROW END生成的字段,我们得到了进行更改的用户(来自历史表中的上一个记录).但是,最初插入的记录版本的历史记录表中没有记录.在那种情况下,我们检索CreatedBy字段.

这似乎提供了一个不漏水的审计解决方案.即使用户编辑该字段CreatedBy,编辑也将记录在历史记录表中.因此,我们CreatedBy从历史表中恢复最旧的值,而不是从主表中恢复当前值.

删除记录

上面的查询没有显示谁从主表中删除了记录.这可以使用以下内容检索(可以简化？):

SELECT 
  d.EmployeeID, d.LoggedInUser AS DeletedBy, 
  d.CreatedBy, d.ValidFromUTC, d.ValidToUTC AS DeletedAtUTC
FROM
  (SELECT EmployeeID FROM dbo.EmployeeHistory GROUP BY EmployeeID) AS eh   -- list of IDs
OUTER APPLY
  (SELECT TOP 1 * FROM dbo.EmployeeHistory 
   WHERE EmployeeID = eh.EmployeeID 
   ORDER BY ValidToUTC DESC) AS d -- last history record, which may be for DELETE
LEFT JOIN
  dbo.Employees AS e
    ON eh.EmployeeID = e.EmployeeID
WHERE e.EmployeeID IS NULL          -- record is no longer in main table

示例表脚本

以上示例基于表脚本(历史表由SQL Server创建):

CREATE TABLE dbo.Employees(
  EmployeeID INT /*IDENTITY(1,1)*/ NOT NULL,
  FirstName NVARCHAR(40) NOT NULL,
  Score INTEGER NULL,
  LoggedInUser AS (SUSER_SNAME()),
  CreatedBy NVARCHAR(128) NOT NULL DEFAULT (SUSER_SNAME()),
  ValidFromUTC DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL DEFAULT SYSUTCDATETIME(),
  ValidToUTC DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL DEFAULT CAST('9999-12-31 23:59:59.9999999' AS DATETIME2),
  CONSTRAINT PK_Employees PRIMARY KEY CLUSTERED (EmployeeID ASC),
  PERIOD FOR SYSTEM_TIME (ValidFromUTC, ValidToUTC)
) 
WITH (SYSTEM_VERSIONING = ON ( HISTORY_TABLE = dbo.EmployeeHistory ))

编辑:(2018年11月19日)增加了默认的限制对SYSTEM_TIME领域,这被一些人认为是最佳实践,并帮助,如果你要添加的系统版本到现有的表.

编辑:(2018/12/03)根据@ JussiKosunen的评论更新(感谢Jussi!).请注意,当多个更改具有相同的时间戳时,查询将仅返回当时的最后一次更改.以前,它为每个更改返回一行,但每个更改都包含最后一个值.查看一种方法,使其返回所有更改,即使它们具有相同的时间戳.(请注意,这是一个真实世界的时间戳,而不是为了避免破坏物理世界而弃用的" Microsoft时间戳 " .)

编辑：在此页面上的其他地方查看我的更好的答案

我的解决方案不需要触发器。我在主表中有一个计算列，该列始终包含已登录的用户，例如

CREATE TABLE dbo.Employees(
    EmployeeID INT NOT NULL,
    FirstName sysname NOT NULL,
    ValidFrom DATETIME2(7) GENERATED ALWAYS AS ROW START NOT NULL,
    ValidTo DATETIME2(7) GENERATED ALWAYS AS ROW END NOT NULL,
    LoggedInUser AS (SUSER_SNAME()),  --<<-- computed column

...等

该字段LoggedInUser始终在每个记录中包含当前登录用户的名称，因此在对任何记录进行任何更改时，该字段都会保存到历史记录表中。

当然，这在主表中不是很有用，因为它没有显示谁对每个记录进行了最后更改。但是在历史记录表中，它在进行更改时就冻结了，这非常有用（尽管它在时间段末而不是开始时记录了用户）。

请注意，作为计算列，该列LoggedInUser必须可以为空，因此历史记录表中的相应列也必须为空。

主（当前）表：

历史记录表：

当然，在历史记录表中，它记录谁将记录从该状态更改为该状态，而不是更改为该状态，即在有效期结束时已登录的用户。它也适用于删除，但是SQL Server时态表系统不会在历史记录表中插入用于插入的记录。

任何有关如何改善此问题的想法都将受到欢迎，例如，如何在历史记录表的每个有效期开始时记录谁进行了更改。我有一个主表中涉及另一个计算字段的想法，该字段使用UDF来获取在历史表中进行了最后更改的用户。

编辑：我发现从@Aaron Bertrand的优秀文章很多灵感在这里，它采用了扳机。