本文部分内容转载自:追踪记录每笔业务操作数据改变的利器——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)啊

wKioL1knq2fjqe2MAAEBkVtlUzk919.gif


一天,突发奇想,formuser是记录在ASP.NET membership的数据库里的。是否能通过sql存储过程或者触发器来记录每条user记录的改变呢?!

下图问,ASP.NET membership数据表关系图,对比可以看出我们只要用到aspnet_usersaspnet_membership里面的信息就够了

wKioL1knorqBVSu8AAL10SWjUug691.jpg


网上找资料,发现这个在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为开启:

wKioL1knpAfxawOwAAAk08LVTVQ820.png


第三步、开启数据表的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个作业

wKioL1knpj7CKRnsAAA1I9kbOMw758.jpg



2、在开启了CDC的数据库中,进入到系统表,可以看到CDC自动创建的跟踪记录表

wKioL1knpnPxEHUoAAB-XN0i6yk239.jpg


3、我们来查询一下

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

wKiom1knp3rBgphHAADD8bxYCek650.png说明:

__$operation字段为2表示的是“插入”操作,3表示的是修改之前的数据,4表示的是修改之后的数据


第五步、后续

数据表有了,自己输出的字段名称优化一下,一张完整登录日志就完成了,嵌入到html页面中,完美