文章插图
一、应用场景
1.控制A账号上班时间不允许从某些段的IP进行登录数据库 。
2.控制B账号登录数据库只能使用某些程序进行登录 。
3.记录所有账号的登录来源 。(根据需要可以不用)
注意:此方法,一定要在本地经过严格测试后再使用 。
二、实现方式 。
1.创建测试数据库 。
CREATE DATABASE TestDB2.创建Config用于控制账号的配置 。
CREATE TABLE [dbo].[Config]([LoginName] [sysname] NOT NULL,[Type] [VARCHAR](50) NOT NULL,[Value] [NVARCHAR](50) NOT NULL,[IsEnabled] [BIT] NOT NULL,PRIMARY KEY CLUSTERED([LoginName] ASC,[Type] ASC,[Value] ASC,[IsEnabled] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]3.配置说明 :
1.)B账号只能使用下面的程序连接数据库 。
Red Gate Software%Microsoft SQL Server Management Studio%dbForge SQL Complete%2.)控制A账号从84,85段IP连接数据库的访问 。
文章插图
4.创建表ServerLoginLog用于记录登录信息 。
CREATE TABLE [dbo].[ServerLoginLog]([LogID] [BIGINT] IDENTITY(1,1) NOT NULL,[SPID] [SMALLINT] NOT NULL,[LoginName] [sysname] NOT NULL,[ClientHost] [NVARCHAR](200) NOT NULL,[ClientHostName] [NVARCHAR](200) NULL,[ClientProgramName] [NVARCHAR](200) NOT NULL,[LoginType] [sysname] NOT NULL,[EventType] [sysname] NULL,[XmlEvent] [XML] NOT NULL,[ServerName] [NVARCHAR](200) NOT NULL,[SID] [NVARCHAR](200) NOT NULL,[PostTime] [DATETIME2](3) NOT NULL,[CreateTime] [DATETIME2](3) NOT NULL,PRIMARY KEY CLUSTERED([LogID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]5.在实例上创建触发器记录账号的登录信息 。
USE [master]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TRIGGER [trg_connection_limit]ON ALL SERVERFOR LOGONASBEGINDECLARE @data XML;DECLARE @spidOfHostName NVARCHAR(100)='';DECLARE @spidOfProgramName NVARCHAR(100)='';DECLARE @spid SMALLINT=0;SET @data = https://www.isolves.com/it/sjk/SQL/2020-06-09/EVENTDATA();SET @spid= @data.value('(/EVENT_INSTANCE/SPID)[1]', 'SMALLINT');SELECT @spidOfHostName=dess.HOST_NAME,@spidOfProgramName=dess.program_nameFROM sys.dm_exec_sessions dess(NOLOCK)LEFT JOIN sys.dm_exec_requests der(NOLOCK) ON der.session_id=dess.session_idWHERE dess.session_id=@spidINSERT INTO [ServerLog].[dbo].[ServerLoginLog] ([SPID],[SID],[ClientHost],[ClientHostName],[LoginName],[LoginType],[EventType],[XmlEvent],[ServerName],[ClientProgramName],[PostTime],[CreateTime])VALUES(@data.value('(/EVENT_INSTANCE/SPID)[1]', 'SMALLINT'),@data.value('(/EVENT_INSTANCE/SID)[1]', 'nvarchar(200)'),@data.value('(/EVENT_INSTANCE/ClientHost)[1]', 'varchar(200)'),ISNULL(@spidOfHostName,''),@data.value('(/EVENT_INSTANCE/LoginName)[1]', 'sysname'),@data.value('(/EVENT_INSTANCE/LoginType)[1]', 'sysname'),@data.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname'),@data,@data.value('(/EVENT_INSTANCE/ServerName)[1]', 'sysname'),ISNULL(@spidOfProgramName,''),@data.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime'),GETDATE());ENDGOENABLE TRIGGER [trg_connection_limit] ON ALL SERVERGO6.在ServerLoginLog中创建表触发器,当登录的账号不满足相关测试时进行回滚不允许登录 。
SET ANSI_NULLS ON;GOSET QUOTED_IDENTIFIER ON;GO/*日志登陆初发器*/CREATE TRIGGER [dbo].[trg_ServerLoginLog_insert] ON [dbo].[ServerLoginLog]AFTER INSERTASBEGINDECLARE @LoginName NVARCHAR(200);DECLARE @ClientProgramName NVARCHAR(200);DECLARE @ClientHost NVARCHAR(200);SELECT @LoginName = ied.LoginName ,@ClientProgramName = ied.ClientProgramName ,@ClientHost = ClientHostFROM Inserted ied;IF @LoginName = 'sa'OR @LoginName = 'Admin'BEGINIF NOT EXISTS ( SELECT *FROM Config cfWHERE cf.LoginName = @LoginNameAND cf.[Type] = 'allow_client_program_name'AND cf.IsEnabled = 1AND @ClientProgramName LIKE cf.Value )BEGINPRINT 'Login信息:[' + @LoginName + ']帐号使用的客户端是['+ @ClientProgramName + ']';ROLLBACK;END;END;IF EXISTS ( SELECT *FROM Config cfWHERE cf.LoginName = @LoginNameAND cf.[Type] = 'not_allow_ip'AND cf.IsEnabled = 1AND cf.Value = https://www.isolves.com/it/sjk/SQL/2020-06-09/@ClientHost )BEGINPRINT 'Login信息:[' + @LoginName + ']帐号使用的客户端IP是[' + @ClientHost+ ']';ROLLBACK;END;END;GO7.启用启用访问策略.
WITH t AS(SELECT * FROM Config cf WHERE cf.LoginName='Ttest') UPDATE t SET IsEnabled=1; ---启用访问策略(0时禁用范围策略)8.验证策略是否生效 。
1.)策略启用后,账号登录的时候会出现如下的错误 。
文章插图
推荐阅读
- 如何正确控制汽车方向盘
- 程序员用Python实现自动化控制键盘和鼠标
- 华为网络设备安全访问,访问控制列表ACL,理论+实战,两分钟掌握
- 指纹登录是怎么跑起来的
- 华为无线控制器升级步骤及注意事项
- 全网最强TCP/IP拥塞控制总结
- 华为交换机在江湖之维护宝典,用户登录教你玩转密码
- 冬季血压为什么难以控制 如何控制高血压
- 在Linux上使用finger命令查询登录用户信息
- 淘宝直播不支持子账号登录 淘宝直播子账号可以开通几个