SQL Server 審核（Audit）-- 建立資料庫層級的審核

標籤：audit   審核   審核組件   審核層級   伺服器審核規範   

SQL Server 審核（Audit）-- 建立資料庫層級的審核

 

任務1：建立審核

步驟1：開啟SSMS，登入到指定的執行個體，展開“Security”，“Audits”節點。

 

步驟2：在“Audits”節點上，右鍵選擇“New Audit…”選項。

 

步驟3：在“Create Audit”視窗，輸入以下的參數。

? 在“Audit name”輸入Audit-AdventureWorks2012-Create/Alter/Drop。

? 在“Audit destination”選擇“File”

? 在“File path”輸入D:\MSSQL\DATA\Audit_logs。

 

步驟4：單擊“OK”完成設定。

 

650) this.width=650;" title="clip_image001" style="border-top:0px;border-right:0px;border-bottom:0px;border-left:0px;" alt="clip_image001" src="http://s3.51cto.com/wyfs02/M00/57/84/wKiom1ScuxiCmJT9AAPztowP4GY469.jpg" border="0" height="680" />

 

步驟5：右鍵點擊剛剛建立的審核“Audit-AdventureWorks2012-Create/Alter/Drop”，選擇“Enable Audit”選項。

 

650) this.width=650;" title="clip_image002" style="border-top:0px;border-right:0px;border-bottom:0px;border-left:0px;" alt="clip_image002" src="http://s3.51cto.com/wyfs02/M01/57/84/wKiom1ScuxmyqAgxAAC2NzWrG_c744.jpg" border="0" height="253" />

 

 

任務2 ：建立資料庫層級的審核規範 – 監控資料庫物件的建立、刪除與修改。

步驟1：在“Object Explorer”視窗，展開“Databases”，“AdventureWorks2012”，“Security”，“Database Audit Specifications”節點。

 

650) this.width=650;" title="clip_image003" style="border-top:0px;border-right:0px;border-bottom:0px;border-left:0px;" alt="clip_image003" src="http://s3.51cto.com/wyfs02/M01/57/84/wKiom1ScuxnyDJ8NAADfJrBfTig035.jpg" border="0" height="307" />

 

步驟2：在“Database Audit Specifications”節點，單擊右鍵選擇“Create Database Audit Specification”選項。

 

步驟3：在“Create Database Audit Specification”視窗，輸入以下參數。

l 在“Name”輸入DatabaseAuditSpecification-Object-Create/Alter/Drop。

l 在“Audit”選擇之前建立的審核對象“Audit-AdventureWorks2012-Create/Alter/Drop”。

l 在“Actions”地區中“Audit Action Type”下的第一行，選擇“SCHEMA_OBJECT_CHANGE_GROUP”。

 

650) this.width=650;" title="clip_image004" style="border-top:0px;border-right:0px;border-bottom:0px;border-left:0px;" alt="clip_image004" src="http://s3.51cto.com/wyfs02/M02/57/84/wKiom1ScuxqzMCgFAAP09cOOs2A510.jpg" border="0" height="679" />

 

“SCHEMA_OBJECT_CHANGE_GROUP”是屬於資料庫層級的審核操作組，在架構上執行CREATE、ALTER和DROP操作時，就會引發這個事件。

 

步驟4：單擊“OK”按鈕，完成設定。

 

步驟5：按右鍵剛才建立的資料庫審核規範“DatabaseAuditSpecification-Object-Create/Alter/Drop”，選擇“Enable Database Audit Specification”選項。

 

650) this.width=650;" title="clip_image005" style="border-top:0px;border-right:0px;border-bottom:0px;border-left:0px;" alt="clip_image005" src="http://s3.51cto.com/wyfs02/M00/57/84/wKiom1Scux6yYYXsAACzuTSshGA481.jpg" border="0" height="253" />

 

 

任務3 ：測試審核功能 – 建立、修改與刪除資料庫物件。

步驟1：開啟SSMS，單擊“New Query”執行以下代碼，在資料庫AdventureWorks2012建立、修改與刪除資料庫的對象。

 

USE AdventureWorks2012GO--Create Database ObjectCREATE TABLE T1(Col1 int not null primary key)GOCREATE VIEW V1 AS SELECT * FROM T1GOCREATE SYNONYM SYN1 FOR T1GOCREATE FUNCTION FUN1(@InParm int) RETURNS int AS BEGIN RETURN @InParm ENDGOCREATE PROCEDURE PROC1(@InParm int) AS BEGIN SET @InParm = 0 ENDGOCREATE TRIGGER TRIG1 ON T1 AFTER INSERT AS RAISERROR(‘This is a dummy table‘,16,10)GO--Alter Database ObjectALTER TABLE T1 ADD Col2 varchar(32)GOALTER PROC PROC1 AS SELECT 1GO--Drop Database ObjectDROP TRIGGER TRIG1GODROP PROCEDURE PROC1GODROP FUNCTION FUN1GODROP SYNONYM SYN1GODROP VIEW V1GODROP TABLE T1GO

 

 

任務4 ：使用記錄檔查看器閱讀稽核線索。

步驟1：展開“Security”，“Audits”節點。

 

步驟2：在剛才建立的“Audit-AdventureWorks2012-Create/Alter/Drop”對象上，右鍵選擇“View Audit Logs”。

 

步驟3：在“Log File Viewer”視窗左上方的“Select logs”地區中，確認有勾選“Audit Collection”，“Audit-AdventureWorks2012-Create/Alter/Drop”。並用以下方式來閱讀所記錄的審核心數據。

 

650) this.width=650;" title="clip_image006" style="border-top:0px;border-right:0px;border-bottom:0px;border-left:0px;" alt="clip_image006" src="http://s3.51cto.com/wyfs02/M02/57/82/wKioL1Scu86Bsh8SAAccZDsMlHA393.jpg" border="0" height="681" />

 

 

l 在右邊“Log file summary”地區內，在“Action ID”地區中，分別點擊“CREATE”、“ALTER”、“DROP”等操作類型。

l 在“Class Type”顯示所記錄的各類對象“TABLE”、“VIEW”、“STORED PROCEDURE”等資料庫物件。

l 在下面的“Statement”地區，可以看到所記錄的程式碼。

l 取得發生事件的日期與時間，可能是利用的SQL Server 2008之後新提供的日期時間函數SYSUTCDATETIME()。回傳的資料類型是datetime2，內容值包含執行SQL Server執行個體的伺服器的日期和事件。而這個日期與時間是以國際標準時間（Coordinated Universal Time:UTC）時間格式回傳，此函數將取回伺服器上的格林威治時間（Greenwich Mean Time:GMT），也就是落後北京時間標準8小時。

備忘：datetime2是SQL Server 2008之後新增的資料類型，包含了日期資料，並結合以24小時製為基礎的時間。Datetime2可視為既有的datetime資料類型的擴充，它具有較大的日期範圍、較精確的小數有效位元和選擇性的使用者指定有效位元。日期範圍可以從公元1年1月1日到公元9999年12月31日，時間範圍可以到00:00:00到23:59:59.9999999，精確度為100ns（1ns=10的負九次方秒）。

 

採用格林威治時間（GMT）來記錄日期時間資料，對於DBA而言，若擁有位於不同時區的資料庫伺服器時，這是一個將日誌的時間標準化的作法。但對於非格林威治時間（GMT）時區的使用者而言，例如：北京時區是（GMT+08:00），可能是個困擾。在接下來的實踐中，將使用T-SQL函數fn_get_audit_file來分析與查看稽核線索的資料，就可以將格林威治時間（GMT）轉為適合的時區的資訊。

 

 

任務5 ：改用“Windows 應用程式事件記錄檔檔案”來記錄審核資訊。

步驟1：在“Security”，“Audits”節點上的“Audit-AdventureWorks2012-Create/Alter/Drop”對象上，右鍵禁用審核。

 

650) this.width=650;" title="clip_image007" style="border-top:0px;border-right:0px;border-bottom:0px;border-left:0px;" alt="clip_image007" src="http://s3.51cto.com/wyfs02/M02/57/82/wKioL1Scu8-z3OlXAAC3CPdpzPk315.jpg" border="0" height="252" />

 

若要調整“Audits”對象的屬性，必須要先禁用此“Audits”之後才能進行調整，否則將產生錯誤資訊，無法調整屬性。

 

步驟2：單擊被禁用的審核對象，單擊滑鼠右鍵選擇“Properties”選項。

 

步驟3：在“Audit Properties”視窗中的“Audit destination”框，下拉選擇“Application Log”。

 

650) this.width=650;" title="clip_image008" style="border-top:0px;border-right:0px;border-bottom:0px;border-left:0px;" alt="clip_image008" src="http://s3.51cto.com/wyfs02/M00/57/82/wKioL1Scu9CClDtiAAIY9nj8W88070.jpg" border="0" height="677" />

 

步驟4：單擊“OK”按鈕，完成屬性的調整。

 

步驟5：單擊被禁用的審核對象，右鍵再啟用審核。

 

步驟6：再次執行之前在資料庫AdventureWorks2012建立、修改與刪除資料庫物件的T-SQL代碼。

 

 

任務6 ：查看存放在“Windows 應用程式事件記錄檔檔案”內的審核資訊。

步驟1：展開“Security”，“Audits”節點。

 

步驟2：在剛才建立的“Audit-AdventureWorks2012-Create/Alter/Drop”對象上，右鍵選擇“View Audit Logs”。

 

步驟3：在“Log File Viewer”視窗左上方的“Select logs”地區中，確認有勾選“Windows NT”，“Application”。

 

步驟4：在右邊的“Log file summary”地區，即可查看所記錄到資料。

 

650) this.width=650;" title="clip_image009" style="border-top:0px;border-right:0px;border-bottom:0px;border-left:0px;" alt="clip_image009" src="http://s3.51cto.com/wyfs02/M00/57/84/wKiom1ScuyOjFB6zAAY5aRJDSdE103.jpg" border="0" height="685" />

 

採用“Windows應用程式事件記錄檔檔案”方式來存放稽核線索，具備以下特性。

l 審核內部所記錄的日期時間部分，仍以格林威治時間（GMT）來記錄。

l 在應用程式事件記錄檔檔案所記錄的日誌時間，已經轉為資料庫伺服器所在時區的日期時間。

l 審核所記錄到資料全部存放在訊息地區內，其“statement”部分，有記錄所執行的T-SQL程式碼。但這在閱讀與後續分析上，勢必會增加困擾。若與存放到二進位檔案的審核目標比較起來，二進位檔案存放的稽核線索已經分類在各資料行中，如此一來，不但增加可讀性，也容易進行篩選與分析。

 

若使用Windows的事件檢視器來閱讀所記錄到的審核心數據，可以在“Application”頁查看所記錄的資訊。

 

650) this.width=650;" title="clip_image010" style="border-top:0px;border-right:0px;border-bottom:0px;border-left:0px;" alt="clip_image010" src="http://s3.51cto.com/wyfs02/M00/57/82/wKioL1Scu9KCjEL6AAP7yqi-VmY603.jpg" border="0" height="618" />

本文出自 “SQL Server Deep Dives” 部落格，請務必保留此出處http://ultrasql.blog.51cto.com/9591438/1596119

SQL Server 審核（Audit）-- 建立資料庫層級的審核