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

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
简介:

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


本文转自   yuxye   51CTO博客,原文链接:http://blog.51cto.com/fishvsfrog/1929727

相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情: https://www.aliyun.com/product/rds/sqlserver
相关文章
|
1月前
|
SQL 流计算
Flink CDC 1.12版本引入了对SQL Server的支持
【1月更文挑战第26天】【1月更文挑战第124篇】Flink CDC 1.12版本引入了对SQL Server的支持
41 1
|
1月前
|
SQL Java 分布式数据库
Flink CDC HBase字段类型与Flink SQL类型之间的转换
【1月更文挑战第4天】【1月更文挑战第19篇】Flink CDC HBase字段类型与Flink SQL类型之间的转换
84 1
|
1月前
|
SQL 运维 关系型数据库
一款 SQL 自动检查神器,再也不用担心 SQL 出错了,自动补全、回滚等功能大全
一款 SQL 自动检查神器,再也不用担心 SQL 出错了,自动补全、回滚等功能大全
31 0
|
2天前
|
SQL 分布式计算 DataWorks
MaxCompute产品使用问题之如何通过临时查询功能来书写和运行SQL语句
MaxCompute作为一款全面的大数据处理平台,广泛应用于各类大数据分析、数据挖掘、BI及机器学习场景。掌握其核心功能、熟练操作流程、遵循最佳实践,可以帮助用户高效、安全地管理和利用海量数据。以下是一个关于MaxCompute产品使用的合集,涵盖了其核心功能、应用场景、操作流程以及最佳实践等内容。
|
1月前
|
SQL 数据处理 HIVE
实时计算 Flink版产品使用合集之将OceanBase的CDC数据导入到Flink SQL的任务的步骤是什么
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
1月前
|
SQL JSON atlas
实时计算 Flink版产品使用合集之SQL Server CDC是否支持抽取SQL Server视图
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
1月前
|
SQL 资源调度 NoSQL
实时计算 Flink版产品使用合集之使用Flink CDC SQL MongoDB Connector时,可以采取什么措施来提升数据消费速率
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
1月前
|
SQL 关系型数据库 MySQL
实时计算 Flink版产品使用合集之当 SQL Server 源数据库中的数据更新后,CDC 吐出的操作(op)是怎样的
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
1月前
|
SQL 资源调度 Oracle
Flink CDC产品常见问题之sql运行中查看日志任务失败如何解决
Flink CDC(Change Data Capture)是一个基于Apache Flink的实时数据变更捕获库,用于实现数据库的实时同步和变更流的处理;在本汇总中,我们组织了关于Flink CDC产品在实践中用户经常提出的问题及其解答,目的是辅助用户更好地理解和应用这一技术,优化实时数据处理流程。
|
1月前
|
SQL Java 数据库连接
Mybatis之Mybatis的各种查询功能和特殊SQL的执行
【1月更文挑战第3天】 一、MyBatis的各种查询功能 1、查询一个实体类对象 2、查询一个List集合 3、查询单个数据 4、查询一条数据为map集合 5、查询多条数据为map集合 1、方法一 2、方法二 二、特殊SQL的执行 1、模糊查询 2、批量删除 3、动态设置表名 4、添加功能获取自增的主键
150 1
Mybatis之Mybatis的各种查询功能和特殊SQL的执行