Database Security Trigger [GAUSS]

[[SA]Gauss]

Hello to everyone. I would like to share a system that I have developed for constantly stolen databases to help you.
Actually the easiest way is to turn on the firewall and only create the necessary rules but in some cases, the firewall may be disabled or we may forget it.

Working principle;
Detecting and blocking the IPs that try too many incorrect passwords to the system using logon trigger

Warning
Not forget add your ip adress to the Whitelist table

It is written in a very simple language so that you can customize the system for yourself.


First of all create database and tables

CREATE SECURITY DATABASE

CREATE DATABASE GaussDBGuard

CREATE BLACKLIST TABLE

USE [GaussDBGuard]
GO

/****** Object:  Table [dbo].[BlackList]    Script Date: 27.10.2020 10:12:12 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[BlackList](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[IP] [varchar](100) NOT NULL,
 CONSTRAINT [PK_BlackList] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE WHITELIST TABLE

USE [GaussDBGuard]
GO

/****** Object:  Table [dbo].[WhiteList]    Script Date: 27.10.2020 10:14:12 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[WhiteList](
	[IP] [varchar](100) NOT NULL,
 CONSTRAINT [PK_WhiteList] PRIMARY KEY CLUSTERED 
(
	[IP] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE LOG PROCEDURE

USE [GaussDBGuard]
GO
/****** Object:  StoredProcedure [dbo].[_DBGuardByGauss]    Script Date: 27.10.2020 10:57:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[_DBGuardByGauss]
AS
BEGIN
CREATE TABLE #LOG(LOGDATE DATETIME,PROCESSINFO VARCHAR(1000), TEXT VARCHAR(MAX))
insert into #LOG  exec xp_readerrorlog 
CREATE TABLE #IPLOG (IP VARCHAR(100))
insert into #IPLOG
select TRIM(REVERSE(SUBSTRING(REVERSE(TRIM(SUBSTRING(Text,97,25))),2,20))) from #LOG with (nolock) 
WHERE TEXT like 'Login failed for user ''sa''%'
GROUP BY TRIM(REVERSE(SUBSTRING(REVERSE(TRIM(SUBSTRING(Text,97,25))),2,20)))
HAVING COUNT(*)>50
ORDER BY COUNT(*) DESC
/*DUBLICATE CUT*/
insert into GaussDBGuard..BlackList
select DISTINCT IP from  #IPLOG
END

CREATE LOGON TRIGGER

USE [master]
GO

/****** Object:  DdlTrigger [_GAUSS_LOGON_CONTROL]    Script Date: 27.10.2020 11:02:27 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [_GAUSS_LOGON_CONTROL]
ON ALL SERVER
FOR LOGON
AS
BEGIN
	if not exists (select IP from GaussDBGuard..WhiteList where IP=CONNECTIONPROPERTY('client_net_address'))
		BEGIN
			if exists (select IP from GaussDBGuard..BlackList where IP=CONNECTIONPROPERTY('client_net_address'))
				BEGIN
					ROLLBACK
				END
		END
END
GO

ENABLE TRIGGER [_GAUSS_LOGON_CONTROL] ON ALL SERVER
GO

You can exec to scheduled times _DBGuardByGauss procedure with sqlagent

Please feel free to contact with any questions Gauss # 9953
GoodLuck!

[LegendarySouL]

what this mean? Database Security Trigger [GAUSS]

[Devsome]

Originally Posted by [SA]Gauss

[...]Detecting and blocking the IPs that try too many incorrect passwords to the system using logon trigger[...]

Originally Posted by LegendarySouL

what this mean? Database Security Trigger [GAUSS]

^