使用MS sql CDC功能记录用户登录情况

q2009a06z22

　　本文部分内容转载自：追踪记录每笔业务操作数据改变的利器——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 TestDbGoExec sys.sp_cdc_enable_dbGo　　检查是否开启，可以使用如下语句，0为未开启，1为开启：

　　第三步、开启数据表的CDC功能
　　
Use TestDbGoExec sys.sp_cdc_enable_table 'dbo', 'aspnet_Users', @role_name = NULLGoExec sys.sp_cdc_enable_table 'dbo', 'aspnet_Membership', @role_name = NULLGo　　
　　说明：数据表的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页面中，完美