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

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

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

--Script3：
--创建测试数据库
USE MASTER
GO
CREATE DATABASE Logon_DB
    
--创建IP过滤表
USE Logon_DB
GO
CREATE TABLE dbo.ValidIP ( 
    IP NVARCHAR(15), 
    CONSTRAINT PK_ValidIP PRIMARY KEY CLUSTERED(IP) 
); 
GO
    
--插入过滤IP
USE Logon_DB
GO
INSERT INTO dbo.ValidIP(IP) VALUES('<local machine>');
INSERT INTO dbo.ValidIP(IP) VALUES('192.168.1.195');
    
--创建登录触发器
-- =============================================
-- Author:        <听风吹雨>
-- Create date:    <2013.05.21>
-- Description:    <限制本机和指定的IP登陆>
-- Blog:        <http://www.cnblogs.com/gaizai />
-- =============================================
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_DB
GO
CREATE 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:        <http://www.cnblogs.com/gaizai />
-- =============================================
CREATE TRIGGER [tr_logon_CheckTime]
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
    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;