SQL Server如何限制IP登陆：登陆触发器的运用

在MySQL的mysql.User表保存了登陆用户的权限信息，Host和User字段则是关于登陆IP的限制。但是在SQL Server没有这样一个表，那SQL Server有什么办法可以实现类似的安全控制的功能呢？

SQL Server 包括三种常规类型的触发器：DML触发器、DDL触发器和登录触发器。DML触发器是比较常使用的，它通常在表或视图中修改数据（INSERT、UPDATE和DELETE 等）为了保证业务数据的完整性和一致性，可以对事务进行回滚等操作；如果你对DDL触发器感兴趣，可以参考：SQL Server DDL触发器运用，里面涉及到DDL触发器的知识；登陆触发器将在本文运用到关于IP登陆限制的解决方案中。

登陆触发器能为我们解决什么问题呢？本文将为你讲述5种运用登陆触发器的场景：

1) 限制某登录名（比如sa）只能在本机或者指定的IP中登陆；

2) 限制服务器角色（比如sysadmin）只能在本机或者指定的IP中登陆；

3) 限制某登录名（比如sa）只能某时间段内登陆；

4) 限制登录名与IP的对应关系，支持多对多关系；

5) 限制某登录名可以在某IP段登录（比如192.168.1.*），如下图；

二、实现过程

(一) 我机器的IP是：192.168.1.48，首先我在数据库创建一个test帐号，设置密码为123，接着创建登陆触发器：tr_connection_limit，它会在用户登陆的时候触发，通过EVENTDATA()函数返回的客户端的IP，使用ORIGINAL_LOGIN()函数返回的登陆名，对IP和登录名进行判断。

当登录名是test的时候，如果登陆的IP地址本地<local machine>或者是192.168.1.50，192.168.1.120就允许登陆，在这之外其它情况的登陆将回滚。登陆失败的如Figure1所示。

--Script1：创建test登录账号
CREATE LOGIN test WITH PASSWORD = '123'
GO
    
-- =============================================
-- 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;

（Figure1：test用户登陆错误信息）

我在一台IP为192.168.1.115的机器上使用test登陆名登陆我的SQL Server数据库，因为这个IP不在允许的IP列表中，所以出现了Figure1的错误信息。我再使用一台IP为192.168.1.120的机器登陆我的SQL Server数据库，成功登陆了，使用Script2返回登陆的信息；如Figure2，请看session_id为58的记录：登陆名为test，登陆的IP为192.168.1.120。

--Script2：返回登录信息
SELECT
a.[session_id],a.[login_time],a.[host_name],
a.[original_login_name],b.[client_net_address]
FROM MASTER.sys.dm_exec_sessions a 
INNER JOIN MASTER.sys.dm_exec_connections b 
ON a.session_id=b.session_id

（Figure2：用户登陆信息）

关于Script1脚本中EXECUTE AS的用法可以参考：EXECUTE AS (Transact-SQL)，ORIGINAL_LOGIN()函数可以参考：ORIGINAL_LOGIN (Transact-SQL)，EVENTDATA()函数用法可以参考：EVENTDATA (Transact-SQL)

（编辑：温州站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!