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

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

http://www.cnblogs.com/artech/archive/2010/11/20/cdc.html

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

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

2、用戶登錄需要有審計日志

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

SqlMembership參數說明  http://fishvsfrog.blog.51cto.com/388027/1927484

第二點，頭疼了

剛開始有幾個思路

1、開啟iis日志，然后開發個小程序進行數據提取和分析。

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

2、開啟SharePoint的審計功能

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

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

下圖問，ASP.NET membership數據表關系圖，對比可以看出我們只要用到aspnet_users和aspnet_membership里面的信息就夠了

網上找資料，發現這個在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為例子

Use TestDb

Go

Exec sys.sp_cdc_enable_db

Go

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

第三步、開啟數據表的CDC功能

Use TestDb

Go

Exec sys.sp_cdc_enable_table 'dbo', 'aspnet_Users', @role_name = NULL

Go

Exec sys.sp_cdc_enable_table 'dbo', 'aspnet_Membership', @role_name = NULL

Go

說明：數據表的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個作業

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

3、我們來查詢一下

select userid,[__$operation],[LastLoginDate] from [sysystemaccount].[cdc].[dbo_aspnet_Membership_CT]

說明：

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

第五步、后續

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