一.本文所涉及的内容（Contents）

二.背景（Contexts）

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

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

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

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

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

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

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

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

三.实现代码（SQL Codes）

(一) 我机器的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:        
       -- =============================================CREATE TRIGGER [tr_connection_limit]ON ALL SERVER WITH EXECUTE AS 'sa'FOR LOGONASBEGIN--限制test这个帐号的连接IF ORIGINAL_LOGIN()= 'test'--允许test在本机和下面的IP登录AND(SELECT EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]', 'NVARCHAR(15)'))NOT IN('
       
        ','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的用法可以参考：，ORIGINAL_LOGIN()函数可以参考：，EVENTDATA()函数用法可以参考：

(二) 有些时候，你数据库可能有许多个登陆帐号，而你更希望的是限制IP，而登陆名跟IP并没有直接的关联，那这应该怎么实现呢？

　　首先创建一个Logon_DB数据库，再创建一个ValidIP表，在表中插入<local machine>和192.168.1.195，表示允许本地和IP为192.168.1.195进行登陆，登陆的帐号属于服务器角色：sysadmin。

--Script3：--创建测试数据库USE MASTERGOCREATE DATABASE Logon_DB--创建IP过滤表USE Logon_DBGOCREATE TABLE dbo.ValidIP (     IP NVARCHAR(15),     CONSTRAINT PK_ValidIP PRIMARY KEY CLUSTERED(IP) ); GO--插入过滤IPUSE Logon_DBGOINSERT INTO dbo.ValidIP(IP) VALUES('
     
      ');INSERT INTO dbo.ValidIP(IP) VALUES('192.168.1.195');--创建登录触发器-- =============================================-- Author:        
      <听风吹雨>
       -- Create date:    <2013.05.21>-- Description:    
       <限制本机和指定的ip登陆>
        -- Blog:        
        -- =============================================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 [Logon_DB].[dbo].[ValidIP] WHERE IP = @IP)         ROLLBACK;    END;END;

　　这次我们在IP为：192.168.1.120的机器上进行测试，这个IP之前是允许使用test帐号登陆的（tr_connection_limit），这次使用sa这个帐号登陆，返回了Figure3的错误信息，这是因为它违反了登陆触发器tr_logon_CheckIP的规则。

（Figure3：sa用户登陆错误信息）

　　在IP为192.168.1.195的机器上使用sa这个帐号成功登陆，再次执行Script2脚本，返回的列表如Figure4所示。

（Figure4：用户登陆信息）

(三) 还有一种场景，我们需要限制某些用户只能在指定的时间内登陆数据库，比如业务上某些运用只能在晚上跑的，通过这个登陆触发器，可以防止运用修改执行时间在白天中运行。

　　首先我们创建一个名为nightworker的登陆名，再创建一个LogonBlockedLog的登陆拦截日志表，接着创建登陆触发器：tr_logon_CheckTime，在早上7:00之后至晚上18:00之前（BETWEEN 7 AND 17）是不允许nightworker帐号登陆数据库的。

--Script4：--创建nightworker登录账号CREATE LOGIN nightworker WITH PASSWORD = '123'GO--创建登录拦截日志表USE Logon_DBGOCREATE TABLE dbo.LogonBlockedLog (     [Id] INT IDENTITY(1,1),    [session_id] SMALLINT,    [login_time] DATETIME,    [host_name] NVARCHAR(128),    [original_login_name] NVARCHAR(128),    [client_net_address] VARCHAR(48),    CONSTRAINT PK_LogonLog PRIMARY KEY CLUSTERED(Id) ); --创建登录触发器-- =============================================-- Author:        
     <听风吹雨>
      -- Create date:    <2013.05.21>-- Description:    
      <限制登陆时间>
       -- Blog:        
       -- =============================================CREATE TRIGGER [tr_logon_CheckTime]ON ALL SERVER WITH EXECUTE AS 'sa'FOR LOGONASBEGIN    IF ORIGINAL_LOGIN()='nightworker' AND    DATEPART(hh,GETDATE()) BETWEEN 7 AND 17    BEGIN        ROLLBACK;        INSERT INTO [Logon_DB].[dbo].[LogonBlockedLog]            ([session_id]            ,[login_time]            ,[host_name]            ,[original_login_name]            ,[client_net_address])        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            WHERE a.session_id = @@SPID    END;END;

　　现在时间是17:20左右，我使用nightworker登陆数据库，这违反了登陆触发器：tr_logon_CheckTime，所以提示Figure5的错误信息，并且在LogonBlockedLog拦截日志表中出现了一条记录，这个表可以帮助我们更好的了解登陆账号的登陆信息。

（Figure5：nightworker用户登陆错误信息）

（Figure6：拦截日志表）

(四) 我们进一步模仿MySQL的mysql.User表的用法，用表保存用户与IP的对应关系，这样就可以对所有登陆用户进行控制了。

--Script5：--登陆名与有效IP对应表USE Logon_DBGOCREATE TABLE [dbo].[ValidLogOn](    [Id] INT IDENTITY(1,1) NOT NULL,    [LoginName] [sysname] NOT NULL,    [ValidIP] [nvarchar](15) NOT NULL,    CONSTRAINT [PK_ValidLogOn] PRIMARY KEY CLUSTERED ([Id]) )--创建唯一约束索引CREATE UNIQUE NONCLUSTERED INDEX [IX_ValidLogOn_LV] ON [dbo].[ValidLogOn] (    [LoginName] ASC,    [ValidIP] ASC) --插入测试数据INSERT [dbo].[ValidLogOn] ([LoginName], [ValidIP]) VALUES (N'BARXXX\Administrator', N'
     
      ')INSERT [dbo].[ValidLogOn] ([LoginName], [ValidIP]) VALUES (N'nightworker', N'
      
       ')INSERT [dbo].[ValidLogOn] ([LoginName], [ValidIP]) VALUES (N'nightworker', N'192.168.1.48')INSERT [dbo].[ValidLogOn] ([LoginName], [ValidIP]) VALUES (N'sa', N'
       
        ')INSERT [dbo].[ValidLogOn] ([LoginName], [ValidIP]) VALUES (N'sa', N'127.0.0.1')INSERT [dbo].[ValidLogOn] ([LoginName], [ValidIP]) VALUES (N'sa', N'192.168.1.48')INSERT [dbo].[ValidLogOn] ([LoginName], [ValidIP]) VALUES (N'test', N'
        
         ')INSERT [dbo].[ValidLogOn] ([LoginName], [ValidIP]) VALUES (N'test', N'192.168.1.120')INSERT [dbo].[ValidLogOn] ([LoginName], [ValidIP]) VALUES (N'test', N'192.168.1.48')INSERT [dbo].[ValidLogOn] ([LoginName], [ValidIP]) VALUES (N'test', N'192.168.1.50')--创建登录触发器-- =============================================-- Author:        
         <听风吹雨>
          -- Create date:    <2013.05.21>-- Description:    
          <限制登陆名和ip>
           -- Blog: 
           -- =============================================CREATE TRIGGER [tr_logon_CheckLogOn]ON ALL SERVER WITH EXECUTE AS 'sa'FOR LOGONASBEGIN DECLARE @LoginName sysname DECLARE @IP NVARCHAR(15) SET @LoginName = ORIGINAL_LOGIN(); SET @IP = (SELECT EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]', 'NVARCHAR(15)')); --判断登录名和IP IF NOT EXISTS(SELECT [ValidIP] FROM [Logon_DB].[dbo].[ValidLogOn] WHERE [LoginName] = @LoginName AND [ValidIP] = @IP) ROLLBACK;END;

（Figure7：登陆名与有效IP对应表）

　　用户登陆名与IP对应关系表[ValidLogOn]，有几点需要注意的，BARXXX\Administrator这个是Windows 身份验证中操作系统的帐号，你需要根据你的实际情况进行修改；IP当中你则需要注意<local machine>和127.0.0.1这些特殊的地址，我个人还是建议在这个表中加入这些信息的。

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

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

--Script6：--插入测试数据INSERT [dbo].[ValidLogOn] ([LoginName], [ValidIP]) VALUES (N'nightworker', N'192.168.1.*')--创建登录触发器-- =============================================-- Author:        
     <听风吹雨>
      -- Create date:    <2013.05.21>-- Description:    
      <登陆名和ip过滤，支持ip范围规范>
       -- Blog:        
       -- =============================================CREATE TRIGGER [tr_logon_CheckLogOn_RangeIP]ON ALL SERVER WITH EXECUTE AS 'sa'FOR LOGONASBEGIN    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 <> '
       
        ' 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,@blockedEND;

（Figure7_1：添加192.168.1.*后）

（Figure8：nightworker用户登录通过）

（Figure9：sa用户登录拦截）

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

(六) 这里使用CLR扩展函数对上面类似Host like 192.168.1.* 的实现，关于CLR的一些基础可以参考：

　　新建程序集（引用一个写好的SQLCLR.dll文件）之后执行下面的SQL脚本创建标量值函数，创建成功后效果如下图所示：

--Script7：--CLR实现IP范围判断CREATE FUNCTION [dbo].[RegexIsMatch](@input [nvarchar](max), @pattern [nvarchar](4000))RETURNS [nvarchar](4000) WITH EXECUTE AS CALLERAS EXTERNAL NAME [SQLCLR].[UserDefinedFunctions].[RegexIsMatch]GOEXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFile', @value=N'SQLRegex.cs' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'FUNCTION',@level1name=N'RegexIsMatch'GOEXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFileLine', @value=N'20' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'FUNCTION',@level1name=N'RegexIsMatch'GO

（Figure10：注册成功后）

--Script8：--创建登录触发器-- =============================================-- Author:        
     <听风吹雨>
      -- Create date:    <2013.05.21>-- Description:    
      <登陆名和ip过滤，支持ip范围规范>
       -- Blog:        
       -- =============================================CREATE TRIGGER [tr_logon_CheckLogOn_RangeIP_ByCRL]ON ALL SERVER WITH EXECUTE AS 'sa'FOR LOGONASBEGIN    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 [LoginName] FROM [Logon_DB].[dbo].[ValidLogOn] WHERE [LoginName] = @LoginName AND [ValidIP] = @IP)     BEGIN        SET @ValidIP = (SELECT TOP 1 [ValidIP] FROM [Logon_DB].[dbo].[ValidLogOn]             WHERE [LoginName] = @LoginName AND [ValidIP] LIKE '%[*]');        --是否存在IP范围匹配        IF (CHARINDEX('*',@ValidIP) > 0 AND @IP <> '
       
        ' AND @IP <> '127.0.0.1' AND dbo.RegexIsMatch(@IP,@ValidIP) = 'True')            SET @blocked = 0        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,@blockedEND;

四.补充说明（Addon）

(一) 我有一台服务器A在本地无法使用SSMS登陆（2005），原因是因为我在A上重新安装DotnetFramework的时候失败了，但是不影响程序链接A的数据库，在服务器B也可以使用SSMS链接到服务器A，我维护数据库有时候需要在单用户下进行表分区的维护（锁比较多），如果在正常情况下，我只需要在A停止TCP/IP就可以阻止其它用户登陆，那这种情况有什么办法解决呢？对的，让指定的服务器B的IP能访问服务器A的数据库，写个登陆触发器，重启SQL Server服务，维护完之后删除登陆触发器，具体的SQL代码可以参考Script1的登陆触发器：tr_connection_limit。

（Figure11：A服务器SSMS打开错误）

(二) 如果在登陆触发器中需要读取表[Logon_DB].[dbo].[ValidLogOn]，如果在ON ALL SERVER后面没有加入WITH EXECUTE AS 'sa'，当你使用test或者nightworker登陆就会一直报错，因为test和nightworker是没有权限读取[Logon_DB].[dbo].[ValidLogOn]表。而tr_logon_CheckIP之所以不用WITH EXECUTE AS 'sa'是因为这本身就是对服务器角色sysadmin的逻辑处理。

(三) 测试本地登陆的情况的时候需要测试[.]、[local]、[localhost]、[127.0.0.1]、[ipconfig]里面显示的内网IP地址这五种情况。（[.]、[local]、[localhost]在EVENTDATA()的ClientHost标签都是显示<local machine>）