|
As a alternative to MDEV-575
we could develop it as a static internal table with less rows per host
bad login = wrong user or wrong password, that don't give access to server and disconect
GMT = 0:00 time zone (no timezone or unixtime)
Create a internal table (in memory or disk?) with:
HOST (ipv6 or ipv4?)
TRIES_COUNTER UNSIGNED TINYINT (0 to 255 is a good value to deny right?)
LAST_UNLOCK_TIME (utime)
implementation:
1) global server vars:
VARIABLES
bad_login_time = 999999 in seconds (setting to 0 = turn off bad login)
|
bad_login_tries = 0-255
|
bad_login_log_file = xxxxxxxxx (for fail2ban implementation, if = OFF or EMPTY, turn off log)
|
bad_login_log_size = 9999999 (after this size create a new file, if =0 set to 1MB)
|
bad_login_log_rotate = 99999 (number of files, if=0 set to 4 files)
|
bad_login_garbage_collector_disconnects = 9999 (number of disconnects to flush bad_login internall table, if =0, set to 1000)
|
bad_login_good_list = ip ; ip ; ip ; ip
|
bad_login_bad_list = ip ; ip ; ip ; ip
|
STATUS
bad_login_garbage_collector_counter = 0 (set to 0 at server startup)
|
bad_login_garbage_collector_max_unlock_time = 0 (set to 0 at server startup)
|
2) BAD_LOGIN table:
CREATE TABLE BAD_LOGIN (
HOST VARCHAR(32) NOT NULL DEFAULT '' COMMENT 'ipv6 or ipv4',
TRIES_COUNTER TINYINT UNSIGNED DEFAULT 0 COMMENT '0 to 255 is a good value to deny right?!'
LAST_UNLOCK_TIME BIGINT NOT NULL DEFAULT 0 COMMENT 'UNIX TIME STAMP'
PRIMARY KEY (HOST)
);
3) BAD_LOGIN log file (CSV?):
HOST, LOGIN TIME (GMT), UNLOCK TIME (GMT), HOST LOCKS, HOST LOCKED NOW (Y/N), HOST MAX UNLOCK TIME (GMT)
4) BAD_LOGIN table flush
something like:
STATUS bad_login_garbage_collector_counter=@@bad_login_garbage_collector_disconnects
|
IF bad_login_garbage_collector_max_unlock_time < NOW() THEN
|
TRUNCATE INFORMATION_SCHEMA.BAD_LOGIN;
|
STATUS bad_login_garbage_collector_max_unlock_time = 0
|
ELSE
|
DELETE FROM INFORMATION_SCHEMA.BAD_LOGIN WHERE LAST_UNLOCK_TIME<NOW();
|
IF rows deleted > 0 THEN
|
OPTIMIZE BAD_LOGIN;
|
STATUS bad_login_garbage_collector_max_unlock_time = SELECT MAX( BAD_LOGIN_UNLOCK_TIME ) FROM INFORMATION_SCHEMA.BAD_LOGIN
|
(or maybe leave the max_unlock_time unchanged... it's just a internal variable to optimize garbage collector)
|
5) ADD HOST TO LOG
check if log is ON
check if should rotate the log (file size and log files)
6) ADD HOST TO INTERNAL TABLE
add host to internal table:
(first update, after a insert, if host don't exists first will change 0 rows)
SET current_unlock_time=now() + @@bad_login_time;
UPDATE INFORMATION_SCHEMA.BAD_LOGIN SET
TRIES_COUNTER=if(TRIES_COUNTER=255,255,TRIES_COUNTER+1), // DON'T OVERFLOW
LAST_UNLOCK_TIME=IF(LAST_UNLOCK_TIME<current_unlock_time,current_unlock_time,LAST_UNLOCK_TIME)
WHERE HOST=current_host;
INSERT INTO INFORMATION_SCHEMA.BAD_LOGIN (HOST,TRIES_COUNTER,LAST_UNLOCK_TIME) VALUES
(HOST, 1, current_unlock_time )
IF current_unlock_time > STATUS bad_login_garbage_collector_max_unlock_time THEN
STATUS bad_login_garbage_collector_max_unlock_time = current_unlock_time
7) NEW DISCONNECT
DISCONNECT
STATUS bad_login_garbage_collector_counter = bad_login_garbage_collector_counter - 1
IF STATUS bad_login_garbage_collector_counter <=0 OR STATUS bad_login_garbage_collector_max_unlock_time < NOW() THEN
BAD_LOGIN table flush (4)
8) At new IPv6 or IPv4 connection
Check if the IP is a 'BAD IP':
something like:
|
DISCONNECT = DISCONNECT, AND CHECK FOR GARBAGE COLLECTOR
|
|
IF connection type = TCP THEN
|
IF host IN @@bad_login_bad_list THEN
|
DISCONNECT (7)
|
EXIT
|
IF STATUS bad_login_garbage_collector_max_unlock_time > NOW() AND host NOT IN @@bad_login_good_list THEN
|
SELECT COUNT(*) AS count FROM INFORMATION_SCHEMA.BAD_LOGIN WHERE HOST=current_ip
|
IF 'count' >= @@bad_login_tries THEN
|
ADD HOST TO INTERNAL TABLE (6) - ADD +1 IN BADLOGIN COUNTER AND UPDATE LAST BLOCK DATE
|
DISCONNECT (7)
|
EXIT
|
... continue with auth ...
|
|
|
... auth get wrong ...
|
IF wrong user/password THEN
|
DISCONNECT (7)
|
IF host NOT IN @@bad_login_good_list THEN
|
ADD host / datetime to INFORMATION_SCHEMA.BAD_LOGIN
|
ADD host / datetime to LOG
|
EXIT
|
|
8) add a INFORMATION_SCHEMA.BAD_LOGIN table
|
|
9) maybe write a fail2ban parser based in CSV file? just first and second columns will be used
|
|