SQL Server 審核（Audit）-- 審核對資料庫物件的訪問

標籤：audit   審核   sql server   

SQL Server 審核（Audit ）-- 審核對資料庫物件的訪問

 

任務1 ：建立登入賬戶，授予適當的許可權

步驟1：開啟SSMS，輸入如下語句，建立登入賬戶UltraSQL，授予訪問AdventureWorks2012資料庫的Person.Person和Person.Password許可權。

 

USE masterGO--Create Login UltraSQLCREATE LOGIN UltraSQLWITH PASSWORD=N’Mpdfzh7’,DEFAULT_DATABASE=AdventureWorks2012, CHECK_EXPIRATION=OFF, CHECK_POLICY=OFFGO--Create User UltraSQL In AdventureWorks2012USE AdventureWorks2012GOCREATE USER UltraSQL FOR LOGIN UltraSQLGO--Grant Select Privilege to Person.Person & Person.PasswordGRANT SELECT ON Person.Person TO UltraSQLGRANT SELECT ON Person.Password TO UltraSQLGO

 

 

任務2 ：建立審核

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

 

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

 

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

l 在“Audit name”輸入Audit-AdventureWorks2012-AccessTable。

l 在“Audit destination”選擇“File”

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

l 取消勾選位於“Maximum rollover files”框下的“Unlimited”。

l 在“Number of files”輸入100。

l 取消勾選位於“Maximum file size”框下的“Unlimited”。

l 在“Maximum file size”輸入10，選擇“MB”框。

設定“Maximum file size”為10MB，可以讓每個審核檔案的大小，維持在指定的大小之內，當此審核檔案的空間耗用完之後，將參照“Maximum rollover files”選項的設定，依據預設值，最多可保留的審核檔案資料量，可達2147483647個檔案。這裡，每個審核檔案最大可達10MB，最多可保留的檔案數量是100個。

 

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

 

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

 

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

 

 

任務3 ：建立訪問資料表的審核

步驟1：展開“Database”，“AdventureWorks2012”，“Security”，“Database Audit Specifications”節點。

 

650) this.width=650;" title="clip_image002" style="border-top:0px;border-right:0px;border-bottom:0px;border-left:0px;" border="0" alt="clip_image002" src="http://s3.51cto.com/wyfs02/M00/57/9D/wKiom1SgsLGCwZJFAADwuWF0pLY694.jpg" height="325" />

 

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

 

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

l 在“Name”輸入DatabaseAuditSpecification-UltraSQLAccessPerson.Password。

l 在“Audit”下拉框選擇之前建立的審核對象Audit-AdventureWorks2012-AccessTable。

l 在“Audit Action Type”中選擇“SELECT”。

l 在“Object Class”中選擇“OBJECT”選項。

l 在“Object Name”中單擊圖表，選擇“Person.Password”選項。

l 在“Principal Name”中單擊圖表，選擇“UltraSQL”選項。

 

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

 

 

步驟4：參照步驟3，在“Action”地區的第二行部分，添加對“Audit Action Type”是“UPDATE”的監視。。以針對使用者UltraSQL查詢或是更新資料表Person.Password進行審核。

 

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

 

步驟5：右鍵點擊剛剛建立的資料庫審核規範“DatabaseAuditSpecification-UltraSQLAccessPerson.Password”，選擇“Enable Database Audit Specification”選項。

 

 

任務4 ：測試審核功能

步驟1：以UltraSQL的身份，通過SSMS串連到AdventureWorks2012。

 

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

 

步驟2：使用UltraSQL帳號，對資料表Person.Password執行查詢與更新等操作。

 

USE AdventureWorks2012GOSELECT * FROM Person.PasswordSELECT * FROM Person.PersonSELECT * FROM Person.PasswordUPDATE Person.Password SET ModifiedDate=GETDATE() WHERE BusinessEntityID=1/*Msg 229, Level 14, State 5, Line 1The UPDATE permission was denied on the object ‘Password‘, database ‘AdventureWorks2012‘, schema ‘Person‘.*/SELECT * FROM Person.Password WHERE BusinessEntityID=1SELECT a.PasswordHash,b.FirstName,b.LastNameFROM Person.Password a INNER JOIN Person.Person bON a.BusinessEntityID = b.BusinessEntityIDSELECT TOP 1 * FROM Person.Password WHERE BusinessEntityID=1

 

 

任務5 ：使用“Log File Viewer” ，閱讀稽核線索

步驟1：以sysadmin身份登入執行個體，展開“Object Explorer”，“Security”，“Audits”節點。

 

步驟2：在審核“Audit-AdventureWorks2012-AccessTable”上單擊右鍵，選擇“View Audit Logs”。

 

步驟3：在“Log File Viewer”視窗的左側，選擇“Audit Collection”，“Audit-AdventureWorks2012-AccessTable”，並利用以下的方式來閱讀所記錄的審核資訊。

l 在右邊的“Log file summary”地區內，在“Action ID”框下，單擊“SELECT”操作類型的資料行日誌。

l 在下方的“Database Principal Name”地區，可以看到所記錄的資料庫使用者。在“Statement”地區可以看到所記錄的程式碼。

 

650) this.width=650;" title="clip_image006" style="border-top:0px;border-right:0px;border-bottom:0px;border-left:0px;" border="0" alt="clip_image006" src="http://s3.51cto.com/wyfs02/M01/57/9D/wKiom1SgsLfigpJSAAYdB6gJMRA588.jpg" height="686" />

 

有個特別的現象，整理如下。

l 以執行UPDATE語句為例，將會引發SELECT與UPDATE等兩個“Action ID”行為，也將會產生兩筆稽核線索，所記錄下來的資料除了在“Action ID”地區是不同的之外（一個是SELECT，一個是UPDATE），其餘部分都一樣。此外，即便是使用者UltraSQL沒有許可權可以修改資料表，這些行為也將被審核對象所記錄，但在“Succeeded”地區部分日誌為False。

 

650) this.width=650;" title="clip_image007" style="border-top:0px;border-right:0px;border-bottom:0px;border-left:0px;" border="0" alt="clip_image007" src="http://s3.51cto.com/wyfs02/M02/57/9D/wKiom1SgsLijy87JAAZt-Vb2y4s797.jpg" height="682" />

 

 

任務6 ：使用T-SQL 函數來分析、篩選審核的日誌資料

步驟1：利用sysadmin身份，執行SSMS，輸入如下查詢語句，使用sys.dm_server_audit_status動態管理檢視來查看各個審核對象的目前狀態。

 

USE masterGOSELECT audit_id N‘Audit ID‘, name N‘Audit Name‘, status_desc N‘Server Audit Status‘,Status_time N‘Last Status Changed Timestamp‘, audit_file_size N‘Audit File Size‘,Audit_file_path N‘Audit File Full Path‘FROM sys.dm_server_audit_status;

 

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

 

l 資料行status_desc（伺服器審核狀態）：查看各個審核對象是否已經啟用。

l 資料行audit_file_size（審核檔案大小（KB））：查看以二進位檔案為目標的審核檔案，其所使用的空間，以KB為單位；若非二進位檔案，例如：以“Windows應用程式事件記錄檔檔案”來存放的稽核線索，則會顯示NULL。

l 資料行audit_file_path（審核檔案目標的完整路徑名稱）：查看以二進位檔案為目標的審核檔案，其所在的完整路徑與檔案名稱。

 

步驟2：單擊“New Query”，執行如下代碼，使用函數fn_get_audit_file分析審核檔案

 

--EX1 Query the Audit File/*Fn_get_audit_file (Transact-SQL)fn_get_audit_file(file_pattern, {default | initial_file_name | NULL },{default | audit_file_offset | NULL})Param:file_patternSet the Audit File Full Path.*/SELECT * FROM sys.fn_get_audit_file(N’ D:\MSSQL\DATA\Audit_logs\Audit-Login-Create%9Alter%9Drop_AF3AAECB-30CC-4476-9395-8754E60E356C_0_130639659785910000.sqlaudit’,default,default);--EX2 Using * Query some Audit Files created by the same audit objectSELECT * FROM sys.fn_get_audit_file(N’ ’ D:\MSSQL\DATA\Audit_logs\Audit-Login-Create%9Alter%9Drop_*.sqlaudit’,default,default);--EX3 attention: event_time type is datetime2,stored GMTSELECT event_time N’Audit caused Date & Time(GMT)’, server_principal_name N’Login’,Database_principal_name N’User’, database_name N’Database’, object_name N’Object Name’, statement N’TSQL’FROM sys.fn_get_audit_file(N’ ’ D:\MSSQL\DATA\Audit_logs\Audit-Login-Create%9Alter%9Drop_*.sqlaudit’,default,default);--EX4 Convert to Timezone Bejing(GMT+08:00) using data type datetimeoffset & Function SWITCHOFFSET)SELECT SWITCHOFFSET(CAST(event_time AS datetimeoffset),’+08:00’) N’Audit Action caused date & time(Timezone Bejing GMT+08:00)’,Server_principal_name N’Login’, database_principal_name N’User’,Database_name N’Database’, object_name N’Object Name’, statement N’TSQL’FROM sys.fn_get_audit_file(N’ ’ D:\MSSQL\DATA\Audit_logs\Audit-Login-Create%9Alter%9Drop_*.sqlaudit’,default,default);--EX5 Import all audit files in the directory into systemSELECT *FROM sys.fn_get_audit_file(N’ ’ D:\MSSQL\DATA\Audit_logs\*’,default,default);--EX6 Convert to Timezone Bejing(GMT+08:00) using data type datetimeoffset & Function SWITCHOFFSET)SELECT SWITCHOFFSET(CAST(event_time AS datetimeoffset),’+08:00’) N’Audit Action caused date & time(Timezone Bejing GMT+08:00)’,Server_principal_name N’Login’, database_principal_name N’User’,Database_name N’Database’, object_name N’Object Name’, statement N’TSQL’FROM sys.fn_get_audit_file(N’ ’ D:\MSSQL\DATA\Audit_logs\*’,default,default);

 

說明：

l 在EX1部分中函數fn_get_audit_file的第一個變數，更換為執行sys.dm_server_audit_status後，取得欄位audit_file_path的值部分，或是填入完整的審核檔案的檔案名稱。在第二個和第三個變數，填入default，採取預設值即可。

l 若要將同一個審核對象所產生的多個審核檔案，都載入到系統內進行分析，可以在檔案名稱上，搭配使用萬用字元*，參考EX2代碼。

l 審核所記錄的日期時間是格林威治時間（GMT），資料類型為datatime2，可以轉換成資料類型datetimeoffset，並利用函數SWITCHOFFSET，將此資料改以北京時區（GMT+08:00）的格式來顯示，參考EX3和EX4。

l 若需要將指定檔案夾內的各個審核檔案，都載入到系統內進行分析，可以搭配萬用字元*，參考EX5和EX6。

 

 

任務7 ：審核檔案的歸檔存放

步驟1：在D:\MSSQL\DATA\Audit_logs內建立新的檔案夾Archive_Audit。

 

步驟2：開啟檔案夾D:\MSSQL\DATA\Audit_logs，複製此檔案夾內的審核檔案（*.sqlaudit）到檔案夾D:\MSSQL\DATA\Audit_logs內，此為簡易備份審核檔案的方式。

 

步驟3：若要刪除已經備份過的審核檔案，請先確認此審核對象已經禁用。否則，正在使用的審核檔案將將無法刪除。

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

SQL Server 審核（Audit）-- 審核對資料庫物件的訪問