![93e597247765bffbc442c6db61ce07c5.gif](https://i-blog.csdnimg.cn/blog_migrate/9c23c5ba91613ec56e7b30f8466d11a5.gif)
各位新朋友~记得先点蓝字关注我哦~
最近可能掉入触发器的坑了,不用着急,下次我们换点新鲜的,哈哈。
上次我们讲了DML触发器和DDL 触发器。
那今天,我们来讲讲登录触发器!
01
登录触发器
类似MySQL中的mysql.User表,记录相关登录名或者IP信息,来限制别有用心的连接,以达到数据库安全,那么,在SQL server中,我们是否也可以创建这样一张表来达到同样的目的呢。
答案是肯定的,话不多说,看下方:
MySQL中的mysql.user表
![1b16fb8675a5a4fdc541c3bac1526424.png](https://i-blog.csdnimg.cn/blog_migrate/59feb91251533c4a3c44f9d77c23724f.png)
SQL server 中的表:
SELECT [LoginName],[ValidIP]FROM [master].[dbo].[ValidLogOn]
![99143254f1ff5ff3b437e6ad89b6d2be.png](https://i-blog.csdnimg.cn/blog_migrate/515b44da0773f6f4dfa025b29a0d954d.png)
02
常见场景
2.1 限制某登录名只能在本机或指定IP连接
1、创建测试用户
![803e51b4afecafa4ffdba909bc5611e8.png](https://i-blog.csdnimg.cn/blog_migrate/445eda2ee7e31e9c2dd2edfebfa0e806.png)
2、创建触发器(只允许dsz在本机和238.4/5登录)
![27e02dec2c5607c2d14a3f2462cbf8d4.png](https://i-blog.csdnimg.cn/blog_migrate/e1caa0a260f0d61d8cc61bb1c6b72cd3.png)
3、使用dsz在本地和238.4登录。
![53343a4a09be82c4902ff42b11f5c697.png](https://i-blog.csdnimg.cn/blog_migrate/0134dc158492b02b1a8002d5fe10de56.png)
![99d1cf123ec8b3ddeae5d0c2d3aabe09.png](https://i-blog.csdnimg.cn/blog_migrate/afbe77c99ffcd7f33828530f6da37ce3.png)
从上图的最后一列可见,dsz这个用户仅仅只有本地和238.4连接。
4、使用dsz在238.6登录。
![bc54afc64c0dc6cb2e5d4c5e1f5d073f.png](https://i-blog.csdnimg.cn/blog_migrate/dda79a49efa3b75c656d080a64bc6242.png)
很明显,因为触发器,该连接被阻止。
接着查看我们本机的错误登录日志,如下:
![38391f860b852e7273a3b664c51af80a.png](https://i-blog.csdnimg.cn/blog_migrate/552a50d9c2e5d52880b8300f0cb42083.png)
![9b0738d45e8a1c9f6440e3c8bffa67b5.png](https://i-blog.csdnimg.cn/blog_migrate/b01c7b214e9888e60354e8a2f3e65256.png)
可见,该触发器对登录名dsz进行了限制,这也防止了生产环境密码泄露之后其他人恶意连接。
2.2 一个触发器限制多个用户
上面的触发器,只能限制dsz的连接,如果有很多用户,难道要写n个触发器吗?别急,我们先做个测试:
1、创建登录过滤表
![9175c1f1c8cac204b940c43a0349502a.png](https://i-blog.csdnimg.cn/blog_migrate/3bf3ceeac1662cbb8830a6e71ac81872.png)
2、插入允许连接的登录名+IP
![cfd7ed04f88b77d258aeaa072e501c07.png](https://i-blog.csdnimg.cn/blog_migrate/0343d75c71395d2d2db698eb905d39e9.png)
3、创建触发器
![f333068202a72347c2a2e882e8548bfe.png](https://i-blog.csdnimg.cn/blog_migrate/88aa19fbfa3184894c162fde91cb45b6.png)
![969983ebb6fe53dd88a5ed48dbc340ff.png](https://i-blog.csdnimg.cn/blog_migrate/260fc7406e7ffac2062027a9c932bbf5.png)
![f133ffe596ab1533cc6fb871f471c077.png](https://i-blog.csdnimg.cn/blog_migrate/1c5568ad70b68846a051fee2cd82b84d.png)
4、测试_使用允许列表以外的登录连接。
![de18f297f5243c0ca5df475999df8f61.png](https://i-blog.csdnimg.cn/blog_migrate/2664271504f20f56f75c7585cd08eb2a.png)
![6647ee46470d10fc3a43e03acedb339d.png](https://i-blog.csdnimg.cn/blog_migrate/bdd63ab49823901fc3cbaa3d7506252e.png)
可见,不在允许列表中的无法连接(此处是针对触发器,我们也可以将触发器改成本地连接不需要通过该限制,有兴趣的可以在上面脚本中加以修改)
5、测试_使用允许列表以内的tr_dsz登录连接。
--使用tr_dsz用户分别在238.4和238.6连接。
![b1f3a048776bbc11a6e2acee80fa20df.png](https://i-blog.csdnimg.cn/blog_migrate/24ba6601d4bf8969336d917258595a2d.png)
![d006868a351057dfd0ef47fe2f023160.png](https://i-blog.csdnimg.cn/blog_migrate/1ea1d6dd8059621b0b64ca7beb7ddc74.png)
可见,238.4登录成功,238.6登录失败,在回看我们允许列表中tr_dsz对应的IP,就很明确了。那么,如果生产复杂,程序端分布在很多机器时,要一个一个加进去吗?我们接着测试。
6、测试_使用允许列表以内的dsz登录连接。
--使用dsz用户分别在238.4和238.6连接。
![0d3651b86c7da1602ab6a0473b3b8663.png](https://i-blog.csdnimg.cn/blog_migrate/fac3dbdeb2f32aece088a39572d7419f.png)
可见,两者都成功连接,那么,对于上一步骤的问题,当多台程序IP在同一网段时,我们可以用*替代。当然,不在同一网段,我们也可以加入该表。
登陆触发器场景多多,我这里就列举两个常用的,比如还可以限制某个用户在业务高峰期不允许连接等等。
03
如何记录登录用户信息
如果大家对上面2.2的触发器脚本有过研究,就会发现我们多次一举的将登录信息写入。
[LogonLog]表,难道真的是多次一举吗?
查询该表:
![29a2a72edefe763102d30090838bd6ed.png](https://i-blog.csdnimg.cn/blog_migrate/dfa5b25894cd191dc82e37b8a6519740.png)
![6d3949e428894ae8272c8c5f2dbeca8a.png](https://i-blog.csdnimg.cn/blog_migrate/b177b2cbea6017c0e7a6e9d9aa550b98.png)
看到表中的记录,是不是有点熟悉,在之前的DML和DDL触发器中也看到过,没错,这就是每个正常登录用户的信息,有了它,我们在排查某些问题时就有依据了。
04
被触发器关在外面如何解救!
当当当,最近几篇都在写触发器,大家都快看累了,那么,我们在创建的时候,如果一不小心把自己关在外面,登录不进去时怎么办……
小编在模拟2.2的场景时,在触发器的代码中,以下代码:
SELECT [ValidIP] FROM master.[dbo].[ValidLogOn] WHERE [LoginName] = @LoginName AND [ValidIP] = @IP中的数据库选错了,然后小编兴高采烈的去登录,然后就傻眼了,无论如何都登录不了,原因就是上面select根本获取不到任何结果,直接rollback。
这个时候,也不要太慌张,解决办法还是有的(不过话说回来,小编当时急的都出汗了,哈哈)
使用DAC——专用管理员连接,删除触发器就可以,以下介绍一种DAC连接,大家有空也可以多多研究这个。
1、打开DAC连接窗口(必须按照下图打开,不能直接新建查询)
![417b6ca408008acea0de4ddabd354a5c.png](https://i-blog.csdnimg.cn/blog_migrate/46285d6016ef7b1be1c3b958c7dc0392.png)
2、连接方式
![6bee11dc4996cde11e12ecec4178ab49.png](https://i-blog.csdnimg.cn/blog_migrate/aa22f891b6c1b7df5e68af6505120763.png)
服务器名称:这里必须写 admin:服务器名称。
登录名:该登录名必须拥有sysadmin权限。
3、连接后删除触发器
![fa2525c1b6f09ba8dd5b2ae502c9dc1d.png](https://i-blog.csdnimg.cn/blog_migrate/5a877b702d02ad076229f04b24ee8ec6.png)
哈哈,不愧是DAC,一个只能连接一个不说,连接成功后左侧资源管理器啥都看不到,这对于一个成天拿鼠标点来点去的我,有点懵,所以,还是要养成一个多敲命令的习惯。
话不多说,如图所示,执行删除触发器的命令,成功之后,擦擦额头的汗,继续修改我们2.2的脚本吧…...
![9af376cf86274894472bb900afeba62e.gif](https://i-blog.csdnimg.cn/blog_migrate/b0def12eb0f1393a56e5499a4e8b5625.gif)
美创运维中心数据库服务团队拥有Oracle ACE 1人、OCM 10余人、数十名Oracle OCP、MySQL OCP、红帽RHCA、中间件weblogic、tuxedo认证、达梦工程师 ,著有《Oracle DBA实战攻略》,《Oracle数据库性能优化方法和最佳实践》,《Oracle内核技术揭秘》等多本数据运维优化书籍。目前运维各类数据库合计2000余套,精通Oracle、MySQL、SQLServer、DB2、PostgreSQL、达梦等主流商业和开源数据库。并成为首批国内达梦战略合作伙伴之一,拥有海量经验和完善的人员培养体系。并同时提供超融合,私有云整体解决方案。
![3c39269ebc2597fc4acdaefdda33afd8.png](https://i-blog.csdnimg.cn/blog_migrate/43dcf17b9d19678a7e059b304fc87137.jpeg)