積沙成塔

【情境描述】

一個嚴謹的企業環境中，所有資料庫伺服器上的組態設定或異動作業應該都有一套嚴謹的 SOP 來規定，不應該讓開發人員或非 DBA 可以隨意的調整資料庫組態或建立/修改/刪除資料庫，若您使用下列的 T-SQL 進行資料庫差異備份。

BEGIN TRY

    DECLARE @DB sysname

    DECLARE @DBPath nvarchar(120) 

    DECLARE db_cur CURSOR FOR

        SELECT name

        FROM sys.databases

        WHERE name not in ('tempdb')

        AND state = 0

    OPEN db_cur

    FETCH NEXT FROM db_cur INTO @DB

    WHILE (@@FETCH_STATUS = 0)

        BEGIN

            SET @DBPath = '\\FileServer\dbbackup\DBServer\' + @DB

                                + '_' + DATENAME(WEEKDAY,GETDATE())

                                + '_Diff.BAK'

            BACKUP DATABASE @DB TO DISK = @DBPath WITH DIFFERENTIAL

            FETCH NEXT FROM db_cur INTO @DB

        END

    CLOSE db_cur

    DEALLOCATE db_cur

    EXEC msdb.dbo.sp_notify_operator

       @name = N'Terry',

       @subject = N'SQL Server message on DBServer',

       @body = N'資料庫差異備份成功'

END TRY

BEGIN CATCH

    EXEC msdb.dbo.sp_notify_operator

       @name = N'Terry',

       @subject = N'SQL Server message on DBServer',

       @body = N'資料庫差異備份失敗'

END CATCH

有一天無預警的新增一個使用者資料庫，可能會因此造成差異備份作業失敗，例如下圖的錯誤訊息：

本文將告訴您如何利用 SQL Server 2012 的 Audit 功能來達到有資料庫被建立、修改和卸除時留下軌跡，以利事後的追蹤。

【實作步驟】

步驟一、新增稽核：稽核是用來設定 SQL Server Audit 所記錄的 Log 的存放位置及稽核失敗時要進行的動作。

由物件總管中點選【執行個體 > 安全性 > 稽核 > 新增稽核】。

於【建立稽核】視窗中輸入稽核名稱，預設稽核紀錄的目的地是存放在檔案系統，此選項若您使用預設值時請指定檔案路徑，您也可以選擇放在安全性記錄檔或應用程式記錄檔。此外，在這個版本的 SQL Server 增加一個新的稽核紀錄失敗選項 - 失敗作業，讓您在稽核失敗時可以允許不產生稽核事件的動作繼續進行，會產生稽核事件的動作則會失敗，讓您在管理上更多了彈性。以及建議您適當的調整稽核檔案數目上限及檔案大小上限（其中檔案大小上限最小 2 MB），以避免因為啟用 SQL Server Audit 造成磁碟空間被塞滿導致系統發生異常。

稽核剛被建立時會處於停用狀態，請於建立好的稽核上按滑鼠右鍵，選擇【啟用稽核】。

啟用成功後將會看到如下圖的對話視窗。

此時稽核物件圖示左邊向下箭頭的小圖示也會隨之消失，變成如下圖的樣子。

步驟二、新增伺服器稽核規格：用來指定您要針對那些 SQL Server 的動作進行稽核。

由物件總管中點選【執行個體 > 安全性 > 伺服器稽核規格 > 新增伺服器稽核規格】。

於【Create Server Audit Specification】視窗中輸入伺服器稽核規格的名稱並選擇步驟一所建立的稽核，重點在於稽核動作類型的選擇，SQL Server 2012 提供多項稽核動作群組和動作，就本文的預期目標而言，想利用 SQL Server Audit 資料庫管理的動作，因此選擇的稽核動作類型為 DATABASE_CHANGE_GROUP。

和稽核建立時相同，伺服器稽核規格物件建立後預設也是停用的，您可以在建立好的稽核規格上按滑鼠右鍵，選擇啟用伺服器稽核規格。

啟用成功您將看到如下圖的對話視窗。

此時伺服器稽核規格圖示左邊向下箭頭的小圖示也會隨之消失，變成如下圖的樣子。

步驟三、嘗試建立資料庫：確認建立資料庫動作是否會被 SQL Server Audit 所記錄。

筆者利用下列的 T-SQL 指令碼建立資料庫：

CREATE DATABASE AuditTestDB

GO

接著在步驟一所建立的稽核物件上按滑鼠右鍵選擇【檢視稽核記錄】。

於【記錄檔檢視器】視窗中您可以看到剛剛建立資料庫的詳細資訊，由哪個登入使用了什麼指令來建立資料庫都被忠實的記錄下來，如下圖所示：

步驟四、嘗試修改資料庫：確認修改資料庫的動作會被 SQL Server Audit 所記錄。

筆者利用下列 T-SQL 指令碼變更資料庫的復原模式：

USE [master]

GO

ALTER DATABASE [AuditTestDB] SET RECOVERY SIMPLE WITH NO_WAIT

GO

接著重新整理記錄檔檢視器您可以看到動作識別碼為 ALTER 的記錄就是變更資料庫復原模式所留下的軌跡，如下圖所示：

步驟五、嘗試卸除資料庫：確認卸除資料庫的動作會被 SQL Server Audit 所記錄。

筆者利用下列的 T-SQL 卸除步驟三所建立的資料庫：

DROP DATABASE AuditTestDB

GO

同樣的，重新整理記錄檔檢視器後找到動作識別碼為 DROP 的記錄您就可以看到卸除資料庫所留下的軌跡，如下圖所示：

步驟一至步驟五的完整 T-SQL 指令碼如下：

--#region 建立稽核

USE [master]

GO

CREATE SERVER AUDIT [Audit-20130220-171311]

TO FILE 

(    FILEPATH = N'D:\'

    ,MAXSIZE = 2 MB

    ,MAX_ROLLOVER_FILES = 2147483647

    ,RESERVE_DISK_SPACE = OFF

)

WITH

(    QUEUE_DELAY = 1000

    ,ON_FAILURE = CONTINUE

)

GO

--#endregion

--#region 建立伺服器稽核規格

USE [master]

GO

CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification-20130220-172825]

FOR SERVER AUDIT [Audit-20130220-171311]

ADD (DATABASE_CHANGE_GROUP)

GO

--#endregion

--#region 建立資料庫

CREATE DATABASE AuditTestDB

GO

--#endregion

--#region 修改資料庫

USE [master]

GO

ALTER DATABASE [AuditTestDB] SET RECOVERY SIMPLE WITH NO_WAIT

GO

--#endregion

--#region 卸除資料庫

DROP DATABASE AuditTestDB

GO

--#endregion

【結論】

經過上一節的演練相信您應該可以了解如何利用 SQL Server Audit 來追蹤資料庫管理的作業並留下軌跡，但這樣做只能事後留軌跡無法事前避免，要做到事前的防堵或許可以考慮利用 DDL Trigger 來實現，有關實作細節留在下次再介紹。

【參考資料】

- 如何利用 SQL Server Audit 追蹤登入密碼修改歷程

- SQL Server Audit (Database Engine)

- 建立伺服器稽核與伺服器稽核規格

- SQL Server Audit 動作群組和動作