安全研究 >> 安全研究详情

运维日记| SQL server 那点事——DML触发器

作者: 美创运维中心数据库服务团队发布日期: 02月05日


11月19日,21点,小编正六指霸屏,决赛圈1V4,忽然,电话响了,这种感觉很熟悉,不错,上次差点推掉对面水晶的那一幕又上演了……作为一名美创的员工,客户才是第一位,我毫不犹豫的摁下了接听键。


XXX吗?我数据库有人篡改了某张表,我一听,这个问题应该难度不大(自信ing!),赶紧回应客户:


您数据库有保留最近一次完整备份以及之后的完整日志吗?


1、有的话可以用完整备份恢复+日志备份(误操作前的LSN恢复)


2、也可以通过sys.fn_dblog()获取二进制日志,再解析出来误操作的语句进行恢复


结果客户的需求并不是该操作如何恢复,而是想确定这个篡改表的始作俑者,以及如何去记录以后数据库这种类似的情况。我很清楚的告诉客户,再没部署任何监控记录的基础上,解析日志也只能获取相关的数据记录,无法获取更过的信息(当然,不排除某种高端工具可能会获取)。


至于以后如何防患,如何记录,这个可以通过SQL server触发器或者审计功能来实现,而本文,将着重介绍DML触发器以及它如何实现客户的需求,请看下文。


01

DML触发器介绍




DML触发器是一种特殊类型的存储过程,它在指定的表中的数据发生变化时自动生效。唤醒调用触发器以响应 INSERT、UPDATE 或 DELETE 语句。





02

常见的DML触发器


2.1 创建语句


2.2 插入测试

可见,因为触发器的缘故,不满足要求的数据无法插入(这里的age>=100虽有悖常理,实则祝大家都长命百岁,哈哈)


 回到我们的主旨,这种触发器虽然能起到防患的作用,但是依旧无法达到我们预期的效果,我们不仅要防患于未然,更要能事事追踪溯源,话不多说,请看下文,也是我们这篇文章最最重要的干货!!!


03

如何用DML触发器记录操作

实现原理:

DML操作记录,简单的说,就是日志类触发器,也就是尽量全面地反映数据库表所进行的insert、update、delete操作,便于日后翻阅。




1、创建测试表:


2、创建DML日志表:


3、创建DML触发器:


4、查看当前触发器日志表:


5、用hostname\administrator执行DML操作:

执行结果为:


6、再用dsz登录执行DML操作:

执行结果为:


7、查看DML日志表:


显而易见,刚才的6次insert,2次update(一次update对应两条记录,因为分别记录了name的更改前后的值—),以及2次delete,都清楚的记录在该表内。


眼尖的网友可能要问,USERID和HOSTNAME很清楚,最后这两列有什么特殊的含义吗?


我们的触发器日志表如果仅仅记录一些基础的信息,还远远不够,我们还要将更改的数据挖掘出来,以便在需要的时候进行逆向DML。


那么,我们是不是也可以修改日志表,添加更多我们需要捕获的信息呢,答案是肯定的,不过这个就交给广大网友去探索、去更改了……


8、truncate 掉test表,再查看DML日志表:

可以看到,依旧只有12条记录,这是为什么呢?明明test的表都被删干净了啊???


莫慌莫慌,大家都知道,truncate和delete不同,它属于DDL语句,所以,关于DDL的触发器,且看下回分解,不要走开哦。

美创运维中心数据库服务团队拥有Oracle ACE 1人、OCM 10余人、数十名Oracle OCP、MySQL OCP、红帽RHCA、中间件weblogic、tuxedo认证、达梦工程师 ,著有《Oracle DBA实战攻略》,《Oracle数据库性能优化方法和最佳实践》,《Oracle内核技术揭秘》等多本数据运维优化书籍。目前运维各类数据库合计2000余套,精通Oracle、MySQL、SQLServer、DB2、PostgreSQL、达梦等主流商业和开源数据库。并成为首批国内达梦战略合作伙伴之一,拥有海量经验和完善的人员培养体系。并同时提供超融合,私有云整体解决方案。



转自杭州美创科技有限公司公众号,如需二次转载,请联系marketing@mchz.com.cn

服务热线:400-711-8011
Copyright ©2005-2020 杭州美创科技有限公司. All Rights Reserved. 浙ICP备12021012号-1