Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-4644

SECURITY - BAD Login / IP blocker - brute force security - fail2ban support - second implementation idea

    XMLWordPrintable

    Details

    • Type: Task
    • Status: Open (View Workflow)
    • Priority: Minor
    • Resolution: Unresolved
    • Fix Version/s: None
    • Component/s: None
    • Labels:

      Description

      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

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              Unassigned
              Reporter:
              rspadim roberto spadim
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated: