使用Sql server 的全局触发器：记录登录ip、限制IP 实现白名单功能
本文由 猛 于 2020-3-20 12:14 发布在 SQL
引用：
Sql server 使用全局触发器限制IP 实现白名单功能 - yangzx1986的博客 - CSDN博客
https://blog.csdn.net/yangzx1986/article/details/78609079
SQL Server 限制IP登陆（登陆触发器运用） - liujiayu2的专栏 - CSDN博客
https://blog.csdn.net/liujiayu2/article/details/46426697
1.创建WhiteList表
手动创建一个RAS_SYS_WhiteList表，包含两个字段：
id: [numeric](18, 0) IDENTITY(1,1) NOT NULL,
IP: nvarchar NULL
主键id自增。
然后手动插入一条记录： insert into RAS_SYS_WhiteList(IP) values(’’) --切记一定要加，否则等下触发器建立后就无法进来了（解决方案在下面）★★★★★★★★★★
2.触发器：记录所有连接ip
--创建登录触发器，将登陆者ip添加到RAS_SYS_WhiteList表中-- =============================================
-- Description: <将登录者ip添加到RAS_Sys_WhiteList表中>
-- =============================================
CREATE TRIGGER [tr_logon_AddIP]
ON ALL SERVER
FOR LOGON
AS
BEGIN
IF IS_SRVROLEMEMBER('sysadmin') = 1
BEGIN
DECLARE @IP NVARCHAR(15);
SET @IP = (SELECT EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]', 'NVARCHAR(15)'));
IF NOT EXISTS(SELECT IP FROM cntrustdb.dbo.RAS_Sys_WhiteList WHERE IP = @IP )
insert into cntrustdb.dbo.RAS_Sys_WhiteList(IP) values(@IP);
END;
END;
3.触发器：限制本机和指定的IP登陆
--创建登录触发器，限制本机和指定的IP登陆
-- =============================================
-- Description: <限制本机和指定的IP登陆>
-- =============================================
CREATE TRIGGER [tr_logon_CheckIP]
ON ALL SERVER
FOR LOGON
AS
BEGIN
IF IS_SRVROLEMEMBER('sysadmin') = 1
BEGIN
DECLARE @IP NVARCHAR(15);
SET @IP = (SELECT EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]', 'NVARCHAR(15)'));
IF NOT EXISTS(SELECT IP FROM cntrustdb.dbo.RAS_Sys_WhiteList WHERE IP = @IP )
ROLLBACK;
END;
END
可以插入新的IP，然后用其他终端来测试（服务器本机不好测试，因为无论怎么改IP，都是）
4.WhiteList表中忘记添加’’，导致登录不了
假如确实犯傻了，自信过度了，比如我这样，想把这个表内的ip都加密，结果发现不一致导致登录失败了，怎么都进不来，怎么办？按照以下步骤来：
| （1）打开Sql Server Manage Studio，不要登录（反正是会失败），去菜单：“文件”--“新建”--“使用当前连接的查询”，在打开的登录窗口中，服务器名称输入：ADMIN:127.0.0.1，账号sa + 原密码，，点击登录，成功 |
（2）在打开的查询界面执行：drop trigger tr_logon_CheckIP on all server 执行成功，就删除掉这个触发器了。
（3）重新登录sa，发现可以进去了
5.不使用白名单IP表，在全局触发器中直接限制登录ip
-- =============================================
-- Author: <听风吹雨>
-- Create date: <2013.05.21>
-- Description: <限制test用户只能在本机和指定的IP中登陆>
-- Blog: <http://www.cnblogs.com/gaizai/>
-- =============================================
CREATE TRIGGER [tr_connection_limit]
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
--限制test这个帐号的连接
IF ORIGINAL_LOGIN()= 'test'
--允许test在本机和下面的IP登录
AND
(SELECT EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]', 'NVARCHAR(15)'))
NOT IN('<local machine>','192.168.1.50','192.168.1.120')
ROLLBACK;
END;
