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、达梦等主流商业和开源数据库。并成为首批国内达梦战略合作伙伴之一，拥有海量经验和完善的人员培养体系。并同时提供超融合，私有云整体解决方案。
来源：freebuf.com 2020-05-29 15:32:53 by: database
请登录后发表评论
注册