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

(五) 对上面的再延伸一点，如果想类似Host like 192.168.1.* 这样进行范围的过滤，那这又应该怎么实现呢？

可以使用CLR扩展函数对IP进行判断，后面会讲到这种方式。这里使用SQL就能解决的方法，仅供参考。开放登录名nightworker在内网所有IP：192.168.1.* 访问本机的权限。

查看本栏目更多精彩内容：http://www.bianceng.cn/database/SQLServer/

--Script6：
--插入测试数据
INSERT [dbo].[ValidLogOn] ([LoginName], [ValidIP]) VALUES (N'nightworker', N'192.168.1.*')
    
--创建登录触发器
-- =============================================
-- Author:        <听风吹雨>
-- Create date:    <2013.05.21>
-- Description:    <登陆名和IP过滤，支持IP范围规范>
-- Blog:        <http://www.cnblogs.com/gaizai />
-- =============================================
CREATE TRIGGER [tr_logon_CheckLogOn_RangeIP]
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
    DECLARE @LoginName sysname
    DECLARE @IP NVARCHAR(15)
    DECLARE @ValidIP NVARCHAR(15)
    DECLARE @len INT
    DECLARE @data XML
    DECLARE @blocked BIT;
        
    SET @len = 0
    SET @blocked = 0
    SET @LoginName = ORIGINAL_LOGIN();
    SET @data = EVENTDATA();
    SET @IP = @data.value('(/EVENT_INSTANCE/ClientHost)[1]', 'NVARCHAR(15)');
        
    --判断登录名和IP
    IF NOT EXISTS(SELECT [ValidIP] FROM [Logon_DB].[dbo].[ValidLogOn] WHERE [LoginName] = @LoginName AND [ValidIP] = @IP) 
    BEGIN
        --是否存在IP范围匹配
        SET @ValidIP = (SELECT TOP 1 [ValidIP] FROM [Logon_DB].[dbo].[ValidLogOn] 
            WHERE [LoginName] = @LoginName AND [ValidIP] LIKE '%[*]');
        --如果存在就替换Client的IP
        IF (CHARINDEX('*',@ValidIP) > 0 AND @IP <> '<local machine>' AND @IP <> '127.0.0.1')
        BEGIN
            DECLARE @SubValidIP NVARCHAR(15)
            SET @SubValidIP = SUBSTRING(@ValidIP,0,CHARINDEX('*',@ValidIP))
            SET @len = LEN(@SubValidIP) + 1
            IF(SUBSTRING(@IP,0,@len) != @SubValidIP)
            BEGIN
                ROLLBACK;
                SET @blocked = 1
            END
        END
        ELSE
        BEGIN
            ROLLBACK;
            SET @blocked = 1
        END
    END
    
    --日志记录
    INSERT INTO [Logon_DB].[dbo].[LogonLog]
        ([session_id]
        ,[login_time]
        ,[host_name]
        ,[original_login_name]
        ,[client_net_address]
        ,[XmlEvent]
        ,[Blocked])
    SELECT
        @data.value('(/EVENT_INSTANCE/SPID)[1]', 'smallint'),
        GETDATE(),
        @data.value('(/EVENT_INSTANCE/ServerName)[1]', 'sysname'),
        @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'sysname'),
        @data.value('(/EVENT_INSTANCE/ClientHost)[1]', 'NVARCHAR(15)'),
        @data,@blocked
END;

（Figure7_1：添加192.168.1.*后）

（Figure8：nightworker用户登录通过）

（Figure9：sa用户登录拦截）

从Figure8和Figure9的对比可以知道，在同一台机器192.168.1.208使用nightworker和sa有不同的效果，nightworker用户登录成功，sa用户登录被拦截了。