使用MS sql CDC功能記錄使用者登入情況

本文部分内容轉載自：追蹤記錄每筆業務操作資料改變的利器——SQLCDC

<a href="http://www.cnblogs.com/artech/archive/2010/11/20/cdc.html" target="_blank">http://www.cnblogs.com/artech/archive/2010/11/20/cdc.html</a>

SharePoint foundation使用form user的認證方式。最近企業開始了等保測評。其中有兩個整改意見比較頭疼：

1、賬号使用強壯型密碼驗證

2、使用者登入需要有審計日志

第一點，formuser裡通過webconfig的設定參數達到效果，具體可以看之前我的一篇文章

第二點，頭疼了

剛開始有幾個思路

1、開啟iis日志，然後開發個小程式進行資料提取和分析。

需要代碼開發，協調開發資源。中間成本過高

2、開啟SharePoint的審計功能

SharePoint能通過配置開啟針對清單、文檔庫的審計功能，wss需要通過指令行配合頁面代碼實作。開發量不是很大，但是和上面說的登入審計的需求有一些不比對，感覺有點大材小用了。學過項目管理的應該明白，我們堅決不能“鍍金”，不然怎麼增長（pian）業務（qian）啊

<a href="https://s2.51cto.com/wyfs02/M02/96/FC/wKioL1knq2fjqe2MAAEBkVtlUzk919.gif" target="_blank"></a>

一天，突發奇想，formuser是記錄在ASP.NET membership的資料庫裡的。是否能通過sql存儲過程或者觸發器來記錄每條user記錄的改變呢？！

下圖問，ASP.NET membership資料表關系圖，對比可以看出我們隻要用到aspnet_users和aspnet_membership裡面的資訊就夠了

<a href="https://s4.51cto.com/wyfs02/M01/96/FB/wKioL1knorqBVSu8AAL10SWjUug691.jpg" target="_blank"></a>

網上找資料，發現這個在Ms sql 2005時代是非常痛苦的，需要寫很多觸發器，而且影響資料庫性能。而在2008以後，微軟開啟了CDC功能。簡介如下：

CDC的全名為Change Data Capture，顧名思義，就是用于追蹤和捕捉資料改變。CDC是在SQL Server 2008中才出現的新特性，而這個特性則在很早之前就出現在了Oracle中。對于SQL Server之前版本來說，在沒有CDC的情況下，如果需要記錄基于某個資料表的資料改變，我們隻能采用觸發器，具體來說就是通過手工建立After Insert、After Update和After Delete觸發器去記錄變化的資料。而CDC給了我們一種更為友善、易用和省心的方式去記錄某個資料表的曆史操作。

心中竊喜，我們用的是MS sql server 2008 R2。說幹就幹。

第一步、準備工作

1、確定SQL server版本在2008以上

2、CDC功能是依賴于SQL agent服務的，確定開啟

第二步、開啟資料庫的CDC功能

以資料庫名稱testDB為例子

檢查是否開啟，可以使用如下語句，0為未開啟，1為開啟：

<a href="https://s1.51cto.com/wyfs02/M01/96/FC/wKioL1knpAfxawOwAAAk08LVTVQ820.png" target="_blank"></a>

第三步、開啟資料表的CDC功能

說明：資料表的CDC特性的開啟通過執行sys.sp_cdc_enable_table存儲過程實作。調用該存儲過程的最簡的方式就是指定資料表的Schema、名稱和用于提取改變資料必須具有的權限（角色）。我通過執行下面的T-SQL将我們建立的Users表的CDC特性打開，其中@role_name參數被設定成NULL，表明我不對讀取改變資料操作進行授權。sys.sp_cdc_enable_table具有很多參數，至于相應參數所影響的CDC行為，可以參考SQL Server 2008線上文檔。

第四步、完成，可以去找到對應CDC資料表看記錄了

完成後

1、在SQL server代理項中，自動生成了2個作業

<a href="https://s4.51cto.com/wyfs02/M01/96/FC/wKioL1knpj7CKRnsAAA1I9kbOMw758.jpg" target="_blank"></a>

2、在開啟了CDC的資料庫中，進入到系統表，可以看到CDC自動建立的跟蹤記錄表

<a href="https://s1.51cto.com/wyfs02/M01/96/FC/wKioL1knpnPxEHUoAAB-XN0i6yk239.jpg" target="_blank"></a>

3、我們來查詢一下

__$operation字段為2表示的是“插入”操作，3表示的是修改之前的資料，4表示的是修改之後的資料

第五步、後續

資料表有了，自己輸出的字段名稱優化一下，一張完整登入日志就完成了，嵌入到html頁面中，完

本文轉自   yuxye   51CTO部落格，原文連結:http://blog.51cto.com/fishvsfrog/1929727