随笔- 729 文章- 0 评论- 51 阅读- 123万

基于mssql 触发器的访问权限设置

以下来源于工作中真实使用到的案例，具体ip信息有变化，其他均无问题，同学们可以参考测试后使用。欢迎交流通过

基于mssql 触发器的访问权限设置，过程需要2步骤完成

1 2	`1、在master库中新建3张记录表` `2、创建触发器`

　一、新建表

USE [master]
GO
 
/****** Object:  Table [dbo].[Login_Info]    Script Date: 12/02/2020 16:19:48 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE TABLE [dbo].[Login_Info](
    [Login_Name] [nvarchar](256) NULL,
    [Login_Time] [datetime] NULL,
    [Host_Name] [nvarchar](128) NULL,
    [ValidIP] [nvarchar](128) NULL
) ON [PRIMARY]
 
GO
 
 
 
USE [master]
GO
 
/****** Object:  Table [dbo].[LogonLog]    Script Date: 12/02/2020 16:19:59 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
SET ANSI_PADDING ON
GO
 
CREATE TABLE [dbo].[LogonLog](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [session_id] [smallint] NULL,
    [login_time] [datetime] NULL,
    [host_name] [nvarchar](128) NULL,
    [original_login_name] [nvarchar](128) NULL,
    [client_net_address] [varchar](48) NULL,
 CONSTRAINT [PK_LogonLog] PRIMARY KEY CLUSTERED 
(
    [Id] 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
 
SET ANSI_PADDING OFF
GO
 
USE [master]
GO
 
/****** Object:  Table [dbo].[ValidLogOn]    Script Date: 12/02/2020 16:20:08 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE 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] 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

　二、新建触发器

/****** Object:  DdlTrigger [tr_logon_CheckLogOn]    Script Date: 12/02/2020 14:09:59 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
 
 
 
CREATE TRIGGER [tr_logon_CheckLogOn]
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
    SET CONCAT_NULL_YIELDS_NULL, ANSI_PADDING, ANSI_WARNINGS ON;
    DECLARE @LoginName sysname
    DECLARE @IP NVARCHAR(15)
    SET @LoginName = ORIGINAL_LOGIN();
    SET @IP = (SELECT EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]', 'NVARCHAR(15)')); 
     
    --全部登陆记录
    --INSERT INTO [master].[dbo].[Login_Info]
    --   SELECT ORIGINAL_LOGIN(), GETDATE(),HOST_NAME,EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]','NVARCHAR(128)')
    --   FROM MASTER.sys.dm_exec_sessions a WHERE a.session_id = @@SPID 
    --判断登录名和IP
    IF NOT EXISTS(SELECT [ValidIP] FROM [master].[dbo].[ValidLogOn] WHERE [LoginName] = @LoginName AND [ValidIP] = @IP)
    BEGIN
            IF(SUBSTRING(@IP,1,11) != '192.168.1.')  and (@IP != '127.0.0.1') and (@IP != '<local machine>')
            BEGIN
        ROLLBACK;
        --日志记录
        INSERT INTO [master].[dbo].[LogonLog]
            ([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
END;
 
 
 
GO
 
SET ANSI_NULLS OFF
GO
 
SET QUOTED_IDENTIFIER OFF
GO
 
ENABLE TRIGGER [tr_logon_CheckLogOn] ON ALL SERVER
GO