首页 > 数据库开发 > SQL2008禁止某些IP访问, 记录外网访问的触发器处理机制

SQL2008禁止某些IP访问, 记录外网访问的触发器处理机制

USE [master]
GO

--禁止访问的IP
CREATE TABLE [dbo].[ForbiddenIP](
[IP] [nvarchar](15) NOT NULL,
[说明] [nvarchar](50) NULL,
[设定时间] [datetime] NULL,
CONSTRAINT [PK_ForbiddenIP] PRIMARY KEY CLUSTERED
(
[IP] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[ForbiddenIP] ADD  CONSTRAINT [DF_ForbiddenIP_设定时间]  DEFAULT (getdate()) FOR [设定时间]
GO

--外网登陆记录表
CREATE TABLE [dbo].[loginlog](
[loginame] [varchar](30) NULL,
[ipaddress] [varchar](40) NULL,
[spid] [int] NULL,
[hostname] [varchar](30) NULL,
[logtime] [datetime] NULL
) ON [PRIMARY]

GO

 

--访问记录触发器

CREATE trigger [tr_login] on all server WITH EXECUTE AS 'sa'

for logon
as
declare @loginame  varchar(30),
@ipaddress varchar(30),
@spid      int,
@hostname  varchar(30);
select @loginame = eventdata().value('(/EVENT_INSTANCE/LoginName)[1]', 'sysname'),
@ipaddress = eventdata().value('(/EVENT_INSTANCE/ClientHost)[1]', 'sysname'),
@spid = eventdata().value('(/EVENT_INSTANCE/SPID)[1]', 'int');
if  ((substring(@ipaddress,1,7)<>'192.168') and (@ipaddress not in ('127.0.0.1', 'servername')))
begin
select @hostname = [host_name] from sys.dm_exec_sessions where session_id = @spid;
begin try
insert into master.dbo.loginlog(loginame,ipaddress,spid,hostname, logtime)
values( @loginame,@ipaddress,@spid,@hostname, getdate() );
end try
begin catch
print '写入日志错误'
end catch

end

GO

--访问IP限制触发器

CREATE TRIGGER [tr_LoginCheck]
ON ALL SERVER
FOR LOGON
AS
declare @IP varchar(15);
set @ip=EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]', 'varchar(15)');
IF Exists (select * from ForbiddenIP where ip=@ip)
ROLLBACK TRAN

GO


本文固定链接: http://www.devba.com/index.php/archives/374.html | 开发吧

报歉!评论已关闭.