在表而不是 SQL 中定義使用者時創建審計策略

作為這篇文章的序言，我對數據庫設計還很陌生，所以如果這篇文章已經有一個簡單或直接的答案，請原諒。

在我的數據庫中，我有一個名為 t_employees 的表，它充當“使用者”表。換句話說，跟踪“modifiedBy”、“createdBy”等內容的表使用此表。

該表如下所示：

CREATE TABLE t_employees (
   employeeId INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
   employeeExternalId VARCHAR(50) NOT NULL,
   email NVARCHAR(128) NOT NULL,
   passwordHash NVARCHAR(128),
   firstName NVARCHAR(60),
   lastName NVARCHAR(60),
   dateHired DATE,
   gender INT,
   DOB DATE,
   ...
   --And a bunch of other random fields that an employee can use
);

這就是我的問題所在。

我正在嘗試提出一個審計解決方案，當我的應用程序中發生更新、插入或刪除語句時，該解決方案將擷取“誰修改了此數據”。我已經研究過這樣的執行緒，人們可以通過觸發器來審計表，這些觸發器跟踪正在進行更改的系統使用者。但是，這種方法對我不起作用，因為我試圖從 t_employees 表中跟踪進行更改的employeeId，並且我的數據庫中只有1個系統使用者。

我曾考慮創建一個 SP，在每次更新、插入或刪除語句之前呼叫它，該語句記錄正在插入、更新或刪除的數據以及向審計表中提出請求的使用者的員工 ID。但是，我覺得必須有更好的解決方案，因為這似乎有點多餘。

任何幫助或建議將不勝感激。謝謝你。

您可以採用多種方法來處理審核更改，但這裡有一些更常見的方法：

1. 應用層負責為您希望跟踪的系統中執行的每個操作將新記錄插入到審計表中。這是您能夠從範例中擷取employeeId 的唯一方法，因為數據庫引擎不知道它們，因為它們不是執行DML 操作的使用者。
2. 在要審計的表上創建插入/更新/刪除觸發器，並將新記錄插入到具有所需欄位的審計表中。請記住，觸發器可能會對性能產生影響
3. 在您的表格中添加一個RevisionNumber和IsDeleted標誌。要點是您總是在發生更改時向表中插入一條新記錄，並IsDeleted在應用程序邏輯刪除該行時將該標誌設置為 1。為了獲得表的“活動”視圖，您需要使用該row_number()函式按唯一鍵分區並按行號降序排列，並在視窗函式中取前 1。這涉及更多，但它是一個優雅的解決方案，可以避免多個表並提供所有內聯列的整個更改歷史。
4. 使用 SQL Server 更改跟踪來確定從標記為更改跟踪的表中插入、更新或刪除了行。您可以選擇跟踪 DML 中更改了哪些列，但無法跟踪實際更改了哪些值。很高興知道某些事情發生了變化，但它無法告訴您數據發生了什麼變化。
5. 使用 SQL Server 更改數據擷取來確定插入、更新或刪除的行。與更改跟踪不同，CDC 可以告訴您 DML 操作前後的值是什麼，因為它從事務日誌而不是系統表中讀取更改。

選項 3 到 5 通常涉及更多，CDC 需要企業版。最簡單的解決方案是 1 和 2，但隨著您希望審計的表數量的增加，它們往往會失控。最後，由您決定什麼最適合您的應用程序和環境。

引用自：https://dba.stackexchange.com/questions/215735