(二) 有些时候,你数据库可能有许多个登陆帐号,而你更希望的是限制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;