SQL的稽核

很常在教 SQL課程的時候，被問到一個問題，那就是：能不能限制 SQL Server的管理者無法做哪些事情？

答案，當然是否定的，這問題就好像你問我，我希望連人事都不知道員工們的薪水一樣不合理，或是說會計與出納不要經手錢這種問題，既然不可能，那就別問了吧！

然而，我們可以利用稽核的方式，記錄管理者到底做了什麼事，就像是放台監視器在櫃台結帳處一樣，這個功能，就是本期要討論的主題：稽核。

作者：楊先民  
精誠資訊/恆逸教育訓練中心資深講師

※網路引用請註明完整出處

SQL Server的稽核

如果你想要記錄 SQL Server的活動，有兩種方式，一種是非企業版的方法，也就是在伺服器中按右鍵選擇屬性，然後選擇安全性，就可以將 C2稽核的選項打勾，如下圖：

這個功能是非企業版的 SQL Server也可以使用的稽核機制，而會將使用者對 SQL Server所做的設定錄制在 log的目錄中，我們就用幾個動作來測試這個稽核機制到底有沒有管用？

下面三張圖，分別是對 SQL Server進行新增資料庫、新增帳號以及刪除資料庫做設定，並且查看稽核有沒有發揮它的效用：

 

 

做完這三個動作，我們到 Log目錄下看，會有一個名稱為 log的稽核檔存在，完整的目錄在下圖就可以找到：

 

這個 log檔會一直增加後面的連續編號，例如 log2、log3依此類推，如果其中有一個 log檔案被砍掉了，你使用 sql profiler就會開不起來，而且 log檔案的循序性就會中斷，這樣就會被發現中間被人動手腳變成黑畫面了（笑）。

 

 

上面兩張圖是利用 sql profiler看到的結果，不過可以發現，只會出現 Audit Addlogin Event這樣的 event class，以及 Object:Created還有 Deleted（後面有資料庫名稱），不過幸好有誰在什麼時間，幹了這些事情，但是資訊就比較沒有那麼的清楚就是。

 

SQL Server 2008之後企業版的稽核

如果你是使用 SQL Server 2008的企業版版本的話，那還可以來試試另一個專門提供給企業版的稽核功能（附帶一提，這個功能己經在 SQL Server 2016年之後改成下放到一般的標準版了），而且相對之下比較不耗費資源。此話怎講？因為此種稽核你可以自行定義要稽核什麼，而不是全部事件都一股腦的記錄下來，例如你不用記錄登出事件，你只要記錄一些和安全性有關的事件，以本例而言，我是記錄備份還原事件，你就不用擔心資料庫被管理者自行備份到其他資料庫系統，因為這些你都可以利用企業版的稽核功能達成目的。

而它的設定方式，也相當的簡單，如下：

 

首先，先在安全性的地方建立一個稽核設定，主要是設定要把稽核資訊存到什麼地方，例如存到事件檢視器，或是存到檔案中，以本例而言，我是存到文字檔中，所以定義一個目錄，如下圖：

 

如果設定要存到檔案中，只要設定檔案所存放的目錄即可，並且還可以設定檔案多大就要換一個新的檔案存放。

接下來可以設定「伺服器稽核規格」或是「資料庫稽核規格」，這樣你就可以選擇要稽核的是伺服器層級還是資料庫層級了。

為了簡化稽核的設定，現在的稽核都是設定成「稽核群組」，以本例而言，是BackupRestore的稽核群，換言之，你若是備份資料庫會被記錄下來，而還原資料庫也會被記錄下來。

當然，它的稽核群還蠻多種的詳細可以參考一下 help，選擇你想稽核的事件，這裡就不一個一個詳述。

同樣的，我們試著備份，以及還原資料庫，就可以在稽核的log中看到下面的資訊：

而由於當初設定這些稽核的log我們設定在 c:\data之中，所以看一下這些檔案是否安在：

如果你企圖刪除這些檔案的話，預設 SQL Server沒有停掉服務是刪除不掉的，而你如果搭配前面所提的 C2稽核的話，當你停掉 SQL Server服務時，C2稽核中的log會記錄 SQL Server被 shutdown的資訊，而在企業級的稽核中也會有 Audit session changed的訊息，如下圖：

 

換言之，管理者即便是自己設定稽核，即便是自己把稽核給停掉或是刪除檔案，這些都是會存在記錄的，因此是一點也不用擔心管理者自己亂來的問題，只是還是需要一個負責稽核的單位，定時對這些稽核檔，或是裡面的資訊定期的審視，以免出現大問題才想看這些檔案，說不定已經被 SQL Server管理者處理成黑畫面了。

最後，稍微多提一下，以上的這些稽核動作，都是地端需要額外去設定，如果是使用雲端的話，則都不用設定喔～～～