安基網 首頁 系統 數據庫 查看內容

SQL Server賬號控制(控制某些IP登錄時間和某些賬號的登錄方式)

2020-6-9 03:36| 投稿: xiaotiger |來自: 互聯網


免責聲明:本站系公益性非盈利IT技術普及網,本文由投稿者轉載自互聯網的公開文章,文末均已注明出處,其內容和圖片版權歸原網站或作者所有,文中所述不代表本站觀點,若有無意侵權或轉載不當之處請從網站右下角聯系我們處理,謝謝合作!

摘要: 一、應用場景1.控制A賬號上班時間不允許從某些段的IP進行登錄數據庫。2.控制B賬號登錄數據庫只能使用某些程序進行登錄。3.記錄所有賬號的登錄來源。(根據需要可以不用)注意:此方法,一定要在本地經過嚴格測試后再使用。二、實現方式。1.創建測試數據庫。CREATE DATABASE TestDB2.創建Config用于控制 ...

一、應用場景

1.控制A賬號上班時間不允許從某些段的IP進行登錄數據庫。

2.控制B賬號登錄數據庫只能使用某些程序進行登錄。

3.記錄所有賬號的登錄來源。(根據需要可以不用)

注意:此方法,一定要在本地經過嚴格測試后再使用。

二、實現方式。

1.創建測試數據庫。

CREATE DATABASE TestDB

2.創建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]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO


CREATE TRIGGER [trg_connection_limit]

ON ALL SERVER

FOR LOGON

AS

BEGIN


DECLARE @data XML;

DECLARE @spidOfHostName NVARCHAR(100)='';

DECLARE @spidOfProgramName NVARCHAR(100)='';

DECLARE @spid SMALLINT=0;


SET @data = EVENTDATA();

SET @spid= @data.value('(/EVENT_INSTANCE/SPID)[1]', 'SMALLINT');

SELECT @spidOfHostName=dess.HOST_NAME

,@spidOfProgramName=dess.program_name

FROM sys.dm_exec_sessions dess(NOLOCK)

LEFT JOIN sys.dm_exec_requests der(NOLOCK) ON der.session_id=dess.session_id

WHERE [email protected]

INSERT 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()

);


END


GO

ENABLE TRIGGER [trg_connection_limit] ON ALL SERVER

GO

6.在ServerLoginLog中創建表觸發器,當登錄的賬號不滿足相關測試時進行回滾不允許登錄。

SET ANSI_NULLS ON;

GO

SET QUOTED_IDENTIFIER ON;

GO

/*日志登陸初發器*/

CREATE TRIGGER [dbo].[trg_ServerLoginLog_insert] ON [dbo].[ServerLoginLog]

AFTER INSERT

AS

BEGIN

DECLARE @LoginName NVARCHAR(200);

DECLARE @ClientProgramName NVARCHAR(200);

DECLARE @ClientHost NVARCHAR(200);

SELECT @LoginName = ied.LoginName ,

@ClientProgramName = ied.ClientProgramName ,

@ClientHost = ClientHost

FROM Inserted ied;


IF @LoginName = 'sa'

OR @LoginName = 'Admin'

BEGIN

IF NOT EXISTS ( SELECT *

FROM Config cf

WHERE cf.LoginName = @LoginName

AND cf.[Type] = 'allow_client_program_name'

AND cf.IsEnabled = 1

AND @ClientProgramName LIKE cf.Value )

BEGIN

PRINT 'Login信息:[' + @LoginName + ']帳號使用的客戶端是['

+ @ClientProgramName + ']';

ROLLBACK;

END;

END;


IF EXISTS ( SELECT *

FROM Config cf

WHERE cf.LoginName = @LoginName

AND cf.[Type] = 'not_allow_ip'

AND cf.IsEnabled = 1

AND cf.Value = @ClientHost )

BEGIN

PRINT 'Login信息:[' + @LoginName + ']帳號使用的客戶端IP是[' + @ClientHost

+ ']';

ROLLBACK;

END;

END;

GO

7.啟用啟用訪問策略.

WITH t AS(

SELECT * FROM Config cf WHERE cf.LoginName='Ttest'

) UPDATE t SET IsEnabled=1; ---啟用訪問策略(0時禁用范圍策略)

8.驗證策略是否生效。

1.)策略啟用后,賬號登錄的時候會出現如下的錯誤。



2.)驗證使用某些程序登錄。

連接參數中隨便設定App=aa,進行登錄,出現如下的錯誤。




3.)使用允許的程序進行連接,便可以登錄。

設定APP=Microsoft SQL Server Management Studio%



"

小編推薦:欲學習電腦技術、系統維護、網絡管理、編程開發和安全攻防等高端IT技術,請 點擊這里 注冊賬號,公開課頻道價值萬元IT培訓教程免費學,讓您少走彎路、事半功倍,好工作升職加薪!

本文出自:https://www.toutiao.com/a6835837361201349127/

免責聲明:本站系公益性非盈利IT技術普及網,本文由投稿者轉載自互聯網的公開文章,文末均已注明出處,其內容和圖片版權歸原網站或作者所有,文中所述不代表本站觀點,若有無意侵權或轉載不當之處請從網站右下角聯系我們處理,謝謝合作!


鮮花

握手

雷人

路過

雞蛋

相關閱讀

最新評論

 最新
返回頂部
创业如何赚钱 专业配资 安徽快3预测网站 稀土股票有哪些 今日福彩3d开机试机号 东方6 1生肖走势图 云南铜业股票行情 江西快三活动 辽宁快乐12走势手机版 黑龙江11选5开讲结果 排列三最简单选号方法 辽宁11选5预测软件 幸运农场幸运五结果 浙江6+1app tcl股票 吉林快三精准在线计划 2012现金百家乐游戏