Details
-
Task
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Duplicate
-
None
-
None
Description
Implement a 'fail2ban' log, and a internal table, TCP connection blocker
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?)
BAD_LOGIN_TIME (utime)
BAD_LOGIN_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 = 999999 in seconds
|
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 'SHOULD STORE IPv4 AND IPv6',
BAD_LOGIN_TIME BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '(unixtimestamp in seconds - GMT)',
BAD_LOGIN_UNLOCK_TIME BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '(unixtimestamp in seconds - GMT)',
KEY HOST (HOST,BAD_LOGIN_UNLOCK_TIME)
);
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 BAD_LOGIN_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
|
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:
INSERT INTO INFORMATION_SCHEMA.BAD_LOGIN (HOST,BAD_LOGIN_TIME,BAD_LOGIN_UNLOCK_TIME) VALUES
(HOST, now(), now() + @@bad_login_time )
IF now() + @@bad_login_time > STATUS bad_login_garbage_collector_max_unlock_time THEN
STATUS bad_login_garbage_collector_max_unlock_time = now() + @@bad_login_time
maybe a optimization like:
IF "bad logins for this user" > bad_login_tries THEN
ADD host
REMOVE the oldest bad login, to make internal table small
7) At new IPv6 or IPv4 connection
Check if the IP is a 'BAD IP':
something like:
IF connection type = TCP THEN
|
IF host IN @@bad_login_bad_list THEN
|
DISCONNECT
|
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 AND BAD_LOGIN_UNLOCK_TIME>=NOW()
|
IF 'count' >= @@bad_login_tries THEN
|
DISCONNECT
|
STATUS 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)
|
EXIT
|
... continue with auth ...
|
|
|
... auth get wrong ...
|
IF wrong user/password THEN
|
DISCONNECT
|
STATUS 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)
|
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
|
Attachments
Issue Links
- is duplicated by
-
MDEV-4644 SECURITY - BAD Login / IP blocker - brute force security - fail2ban support - second implementation idea
- Open