積沙成塔

論壇上有網友提到是否有辦法透過 SQL Server Audit 功能來記錄使用者所查過的資料，針對這個議題趁機會動手實作一下 SQL Server Audit 如何記錄使用者對資料庫物件的稽核功能，本文以資料表為對象，嘗試透過建立資料庫稽核規格，來記錄使用者對資料表的 CRUD 歷程。

首先請由【Object Explorer > 執行個體 > Security > Audits > New Audit】來建立新的稽核。

在 Create Audit 視窗中輸入 Audit name，接著您可以選擇 On Audit Log Failure 的動作，當稽核記錄失敗時要繼續還是要關閉伺服器，或是執行特定的動作。最後是設定 Audit destination，用來指定稽核記錄是要存放在檔案系統或是事件檢視器中的 Security Log 或 Application Log。

上述的設定您也可以使用下列的 T-SQL 來實現：

   1:  USE [master]

   2:   

   3:  GO

   4:   

   5:  CREATE SERVER AUDIT [MyAudit]

   6:  TO FILE 

   7:  (    FILEPATH = N'E:\'

   8:      ,MAXSIZE = 0 MB

   9:      ,MAX_ROLLOVER_FILES = 2147483647

  10:      ,RESERVE_DISK_SPACE = OFF

  11:  )

  12:  WITH

  13:  (    QUEUE_DELAY = 1000

  14:      ,ON_FAILURE = CONTINUE

  15:  )

  16:   

  17:  GO

  18:   

  19:   

稽核建立完成後預設是停用的狀態，您可以於【Object Explorer > 執行個體 > Security > Audits > 您的稽核 > Enable Audit】來啟用稽核。

稽核建立好之後請由【Object Explorer > Databases > 要稽核的資料庫 > Security > Database Audit Specifications > New Database Audit Specification】來新增資料庫稽核規格。

於 Create Database Audit Specification 視窗中，於 Name 的文字方塊中輸入您的資料庫稽核規格的名稱，接著選擇您建立的稽核，接著建立 Actions，下圖示範當 NWUser 對  Region 資料表的 SELECT、INSERT、UPDATE、DELETE 都要留下軌跡。

上述設定您也可以透過下列的 T-SQL 來實現：

   1:  USE [Northwind]

   2:   

   3:  GO

   4:   

   5:  CREATE DATABASE AUDIT SPECIFICATION [MyDBAuditSpec]

   6:  FOR SERVER AUDIT [MyAudit]

   7:  ADD (SELECT ON OBJECT::[dbo].[Region] BY [NWUser]),

   8:  ADD (INSERT ON OBJECT::[dbo].[Region] BY [NWUser]),

   9:  ADD (UPDATE ON OBJECT::[dbo].[Region] BY [NWUser]),

  10:  ADD (DELETE ON OBJECT::[dbo].[Region] BY [NWUser])

  11:   

  12:  GO

預設資料庫稽核規格一樣是停用的狀態，請由【Object Explorer > 執行個體 > Databases > 您的資料庫名稱 > Security > Database Audit Specification > Enable Database Audit Specification】來啟用資料庫稽核規格。

最後以下列 T-SQL 來測試本文所設定的稽核規格是否只有 NWUser 對 Region 的 CRUD 才進行記錄。

   1:  -- 切換執行身分

   2:  EXECUTE AS User = 'NWUser'

   3:  --確認身分切換正確

   4:  SELECT SUSER_NAME() AS LoginName,USER_NAME()  AS UserName

   5:  GO

   6:   

   7:  --執行 CRUD 動作

   8:  INSERT INTO region VALUES (5,'test')

   9:   

  10:  SELECT * FROM region WHERE RegionID = 5

  11:   

  12:  UPDATE region SET RegionDescription = 'bbb' WHERE RegionID = 5

  13:   

  14:  DELETE FROM region WHERE RegionID > 4

  15:   

  16:  --還原執行身分

  17:  REVERT

  18:   

  19:  --嘗試以 sa 身分進行 SELECT

  20:  select * from region

若您要查看稽核結果，可點選【Object Explorer > 執行個體 > Security > 您建立的稽核 > View Audit Logs】。

在 Log File Viewer 視窗中您可以看到所有被記錄的動作都是 NWUser，而 sa 對 Region 資料表的 SELECT 動作並沒有留下軌跡。

【參考資料】

• SQL Server Audit (Database Engine)
• View a SQL Server Audit Log