[SQL] 稽核資料表 DML 動作

稽核資料表 DML 動作實作練習。

• 建立 Demo 資料

USE AdventureWorks2012
GO 

IF OBJECT_ID('Customer') IS NOT NULL
  DROP TABLE Customer 

CREATE TABLE Customer 
(
    CustNO char(3) ,
    CustName nchar(8) ,
    HomeAddress nvarchar(100) 
)
INSERT INTO Customer VALUES
    ('001',N'王一明',N'新北市XX路XX號') ,
    ('002',N'張二龍',N'台中市OO路OO號') ,
    ('003',N'趙三虎',N'高雄市AA路AA號')

-- 建立測試 Login 和 User
CREATE LOGIN AuditDemo WITH Password = '1234'
CREATE USER AuditDemo FOR LOGIN AuditDemo

-- 授予 SELECT、INSERT 和 UPDATE 權限，沒有 DELETE 權限 
GRANT SELECT ON dbo.Customer TO AuditDemo
GRANT INSERT ON dbo.Customer TO AuditDemo
GRANT UPDATE ON dbo.Customer TO AuditDemo

• 建立稽核（Audit）

Step1：Instance => 安全性 => 新增稽核
Step2：選擇稽核目的地為［檔案］且設定檔案路徑

Step3：Instance => 安全性 => 稽核 => AdutiDML => 啟用稽核

也可以利用 T-SQL 語法建立 Audit

CREATE SERVER AUDIT [AuditDML]
TO FILE 
( 
    FILEPATH = N'C:\'
    ,MAXSIZE = 0 MB
    ,MAX_ROLLOVER_FILES = 2147483647
    ,RESERVE_DISK_SPACE = OFF
)
WITH
( 
    QUEUE_DELAY = 1000
    ,ON_FAILURE = CONTINUE
    ,AUDIT_GUID = '40fad792-b734-4983-8c74-b1ac959d2ea0'
)
GO

ALTER SERVER AUDIT [AuditDML] WITH (STATE = ON) -- 啟動 Audit
GO

• 建立資料庫層級稽核規格

Step1：AdventureWorks2012 => 安全性 => 資料庫稽核規格 => 新增資料庫稽核規格
Step2：選擇稽核動作［SELECT、INSERT、UPDTE 和 DELETE］、物件類別為［object］、物件名稱為［Customer］和主體名稱為［AuditDemo］

Step3：AdventureWorks2012 => 安全性 => 資料庫稽核規格 => AuditSpecDML => 啟用資料庫稽核規格

也可以利用 T-SQL 語法建立 Audit Spec

CREATE DATABASE AUDIT SPECIFICATION [AuditSpecDML]
FOR SERVER AUDIT [AuditDML]
ADD (DELETE ON OBJECT::[dbo].[AuditDML] BY [AuditDemo]),
ADD (INSERT ON OBJECT::[dbo].[AuditDML] BY [AuditDemo]),
ADD (SELECT ON OBJECT::[dbo].[AuditDML] BY [AuditDemo]),
ADD (UPDATE ON OBJECT::[dbo].[AuditDML] BY [AuditDemo])
WITH (STATE = ON)
GO

• 執行 DML 動作來觀察 Audit 記錄

-- 利用 SUSER_Name() 和 USER_NAME() 來確認目前的使用者 
SELECT SUSER_Name() AS LoginName , USER_NAME() AS UserName
-- 切換身分至 AuditDemo
EXECUTE AS USER = 'AuditDemo'
-- 利用 SUSER_Name() 和 USER_NAME() 來確認目前的使用者
SELECT SUSER_Name() AS LoginName , USER_NAME() AS UserName

-- 每隔兩秒執行一個 T-SQL 語法
-- 1. SELECT
SELECT * FROM Customer
WAITFOR DELAY '00:00:02'
 
-- 2. INSERT
INSERT INTO Customer VALUES('004',N'李四賜',N'嘉義市CC路')
WAITFOR DELAY '00:00:02'

-- 3. UPDATE 
DECLARE @CustNO char(3) = '001'
UPDATE Customer SET HomeAddress = N'台東市BB號' WHERE CustNO = @CustNO
WAITFOR DELAY '00:00:02'
 
-- 4. DELETE
DELETE FROM Customer WHERE CustNO = '002'
WAITFOR DELAY '00:00:02'
-- 沒有權限會出現下面的錯誤
-- 訊息 229，層級 14，狀態 5，行 16
-- 結構描述 'dbo'，資料庫 'AdventureWorks2012'，物件 'Customer' 沒有 DELETE 權限。

-- 切回原使用者後再 SELECT
REVERT
SELECT * FROM Customer

• 檢視稽核記錄

Instance => 安全性 => 稽核 => AdutiDML => 檢視稽核記錄

圖一

1. 進行 UPDATE（綠色框） 和 DELETE（藍色框） 時，各動作都有兩筆記錄
2. 進行 DELETE 時，沒有權限可以執行而失敗，Audit 有記錄下來，［成功］欄位是 False（紅色框）
3. 切回使用者後再 SELECT，因為設定並沒有稽核該使用者，因此沒有該使用者被稽核的記錄

圖二：點選圖一中的資料，在下方就會出現圖二資訊

SQL Server 2008 Audit 的 WHERE 條件（本例是 002）在 Audit 中會以 @P1 參數型式來呈現，但在 SQL 2012 中實作會紀錄該數值。

參考資料
• 論壇問題出處 
• 技術論壇討論
• 記錄使用者語法