星期五, 7月 26, 2013

[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:選擇稽核目的地為[檔案]且設定檔案路徑
[SQL] Aduit-稽核資料表 DML 動作-1
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]
[SQL] Aduit-稽核資料表 DML 動作-2
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
[SQL] Aduit-稽核資料表 DML 動作-3
-- 每隔兩秒執行一個 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 => 檢視稽核記錄
    圖一
[SQL] Aduit-稽核資料表 DML 動作-4
  1. 進行 UPDATE(綠色框) 和 DELETE(藍色框) 時,各動作都有兩筆記錄
  2. 進行 DELETE 時,沒有權限可以執行而失敗,Audit 有記錄下來,[成功]欄位是 False(紅色框)
  3. 切回使用者後再 SELECT,因為設定並沒有稽核該使用者,因此沒有該使用者被稽核的記錄
    圖二:點選圖一中的資料,在下方就會出現圖二資訊
[SQL] Aduit-稽核資料表 DML 動作-5
SQL Server 2008 Audit 的 WHERE 條件(本例是 002)在 Audit 中會以 @P1 參數型式來呈現,但在 SQL 2012 中實作會紀錄該數值。

1 則留言:

  1. 您好!

    小弟不才..想請問sql server 2014 ctp2 似乎沒有 "資料庫稽核規格" 這功能
    是不支援的關係嗎? 如果是不支援 那麼可以運用更新的方式增加該功能嗎?

    萬分感謝!!

    回覆刪除