mysql 触发器_运维日记|SQL Server登录触发器

各位新朋友～记得先点蓝字关注我哦～

最近可能掉入触发器的坑了，不用着急，下次我们换点新鲜的，哈哈。

上次我们讲了DML触发器和DDL 触发器。

那今天，我们来讲讲登录触发器！

01

登录触发器

类似MySQL中的mysql.User表，记录相关登录名或者IP信息，来限制别有用心的连接，以达到数据库安全，那么，在SQL server中，我们是否也可以创建这样一张表来达到同样的目的呢。

答案是肯定的，话不多说，看下方：

MySQL中的mysql.user表

SQL server 中的表：

SELECT [LoginName],[ValidIP]FROM [master].[dbo].[ValidLogOn]

02

常见场景

2.1 限制某登录名只能在本机或指定IP连接

1、创建测试用户

2、创建触发器(只允许dsz在本机和238.4/5登录)

3、使用dsz在本地和238.4登录。

从上图的最后一列可见，dsz这个用户仅仅只有本地和238.4连接。

4、使用dsz在238.6登录。

很明显，因为触发器，该连接被阻止。

接着查看我们本机的错误登录日志，如下：

可见，该触发器对登录名dsz进行了限制，这也防止了生产环境密码泄露之后其他人恶意连接。

2.2 一个触发器限制多个用户

上面的触发器，只能限制dsz的连接，如果有很多用户，难道要写n个触发器吗？别急，我们先做个测试：

1、创建登录过滤表

2、插入允许连接的登录名+IP

3、创建触发器

4、测试_使用允许列表以外的登录连接。

可见，不在允许列表中的无法连接(此处是针对触发器，我们也可以将触发器改成本地连接不需要通过该限制，有兴趣的可以在上面脚本中加以修改)

5、测试_使用允许列表以内的tr_dsz登录连接。

--使用tr_dsz用户分别在238.4和238.6连接。

可见，238.4登录成功，238.6登录失败，在回看我们允许列表中tr_dsz对应的IP，就很明确了。那么，如果生产复杂，程序端分布在很多机器时，要一个一个加进去吗？我们接着测试。

6、测试_使用允许列表以内的dsz登录连接。

--使用dsz用户分别在238.4和238.6连接。

可见，两者都成功连接，那么，对于上一步骤的问题，当多台程序IP在同一网段时，我们可以用*替代。当然，不在同一网段，我们也可以加入该表。

登陆触发器场景多多，我这里就列举两个常用的，比如还可以限制某个用户在业务高峰期不允许连接等等。

03

如何记录登录用户信息

如果大家对上面2.2的触发器脚本有过研究，就会发现我们多次一举的将登录信息写入。

[LogonLog]表，难道真的是多次一举吗？

查询该表：

看到表中的记录，是不是有点熟悉，在之前的DML和DDL触发器中也看到过，没错，这就是每个正常登录用户的信息，有了它，我们在排查某些问题时就有依据了。

04

被触发器关在外面如何解救！

当当当，最近几篇都在写触发器，大家都快看累了，那么，我们在创建的时候，如果一不小心把自己关在外面，登录不进去时怎么办……

小编在模拟2.2的场景时，在触发器的代码中，以下代码：

SELECT [ValidIP] FROM master.[dbo].[ValidLogOn] WHERE [LoginName] = @LoginName AND [ValidIP] = @IP中的数据库选错了，然后小编兴高采烈的去登录，然后就傻眼了，无论如何都登录不了，原因就是上面select根本获取不到任何结果，直接rollback。

这个时候，也不要太慌张，解决办法还是有的(不过话说回来，小编当时急的都出汗了，哈哈)

使用DAC——专用管理员连接，删除触发器就可以，以下介绍一种DAC连接，大家有空也可以多多研究这个。

1、打开DAC连接窗口(必须按照下图打开，不能直接新建查询)

2、连接方式

服务器名称：这里必须写  admin:服务器名称。

登录名：该登录名必须拥有sysadmin权限。

3、连接后删除触发器

哈哈，不愧是DAC，一个只能连接一个不说，连接成功后左侧资源管理器啥都看不到，这对于一个成天拿鼠标点来点去的我，有点懵，所以，还是要养成一个多敲命令的习惯。

话不多说，如图所示，执行删除触发器的命令，成功之后，擦擦额头的汗，继续修改我们2.2的脚本吧…...

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