Author: Yang Taotao

Senior database expert, specializing in MySQL for more than ten years. He is good at backup and recovery, SQL tuning, monitoring, operation and maintenance, high availability architecture design related to MySQL, PostgreSQL, MongoDB and other open source databases. At present, he works in aikesheng, providing MySQL related technical support and MySQL related course training for major operators and banking and financial enterprises.

Source: original contribution

*It is produced by aikesheng open source community. The original content cannot be used without authorization. For reprint, please contact Xiaobian and indicate the source.

Scenes we often encounter: using a bank card to withdraw money from ATM, transfer money on APP, online shopping payment and other links. Due to the continuous wrong password for a certain number of times, the bank card is locked and cannot be used. It can be used normally unless you go to the bank counter with a valid certificate and unlock it manually.

As MySQL database is used in more and more financial scenarios, the card locking function caused by continuous wrong bank card password is ready to come out. MySQL has launched a similar strategy since 8.0.19: failed login tracking and temporary account locking. Failed login tracking and temporary password locking, hereinafter referred to as FLTTAL.

Different from the previous password policies, FLTTAL has no global parameter matching and can only be matched when creating users or changing user attributes. There are two options:

FLTTAL has the following points to pay attention to:

1. failed_login_attempts and password_lock_time must be different from 0 for FLTTAL to take effect.

2. Failed is not specified when creating a new user_ login_ Attempts and password_lock_time, FLTTAL is turned off by default.

3. For users who have used FLTTAL, the administrator will not change the original password authentication policy after changing their user.

4. Once the account is locked, you cannot log in even if you enter the correct password.

5. There is also the most important point: due to the continuity of FLTTAL's password verification, the FLTTAL counter will be reset after any successful login. For example, failed_ login_ When attempts is set to 3, the first two consecutive passwords are input incorrectly, the third time the correct password is input, and the FLTTAL counter is reset.

Next, let's see how to use this password authentication strategy:

1. For ordinary users:

The administrator creates the user test1@'localhost' and sets the FLTTAL policy: the number of failed retries is 3 and the password locking time is 3 days.

   mysql:(none)>create user test1@'localhost' identified by 'test' failed_login_attempts 3 password_lock_time 3;
   Query OK, 0 rows affected (0.14 sec)

The password is entered incorrectly three times in a row, and the test1@'localhost' account is locked:

   root@ytt-ubuntu:/home/ytt# mysql -utest1  -p -S /opt/mysql/mysqld.sock
   Enter password: 
   ERROR 1045 (28000): Access denied for user 'test1'@'localhost' (using password: NO)
   root@ytt-ubuntu:/home/ytt# mysql -utest1  -p -S /opt/mysql/mysqld.sock
   Enter password: 
   ERROR 1045 (28000): Access denied for user 'test1'@'localhost' (using password: NO)
   root@ytt-ubuntu:/home/ytt# mysql -utest1  -p -S /opt/mysql/mysqld.sock
   Enter password: 
   ERROR 3955 (HY000): Access denied for user 'test1'@'localhost'. Account is blocked for 3 day(s) (3 day(s) remaining) due to 3 consecutive failed logins.

The administrator can only unlock the account for normal use: (or forget the password and ask the administrator to unlock the account and reset the new password.)

   mysql:(none)>alter user test1@'localhost' account unlock;
   Query OK, 0 rows affected (0.00 sec)

Login again with the correct password: login succeeded.

   root@ytt-ubuntu:/home/ytt# mysql -utest1  -p -S /opt/mysql/mysqld.sock -e "select 'hello world\!'"
   Enter password: 
   +--------------+
   | hello world! |
   +--------------+
   | hello world! |
   +--------------+

1. How to use proxy users:

For proxy users, FLTTAL only affects proxy users, not hidden real users.

See my previous article for the introduction of agent users: https://mp.weixin.qq.com/s/gw...

Previously created proxy user:

   mysql:(none)>show grants for ytt_fake;
   +-------------------------------------------------+
   | Grants for ytt_fake@%                           |
   +-------------------------------------------------+
   | GRANT USAGE ON *.* TO `ytt_fake`@`%`            |
   | GRANT PROXY ON `ytt_real`@`%` TO `ytt_fake`@`%` |
   +-------------------------------------------------+
   2 rows in set (0.00 sec)

Change the real user plug-in to mysql_native_password allows them to log in normally:

   mysql:(none)>alter user ytt_real identified with mysql_native_password;
   Query OK, 0 rows affected (0.10 sec)

To proxy user ytt_fake sets FLTTAL policy: the number of failed retries is 2 and the password locking time is 7 days.

   mysql:(none)>alter user ytt_fake failed_login_attempts 2 password_lock_time 7;
   Query OK, 0 rows affected (0.14 sec)

The proxy user enters the wrong password twice in a row, and the account is locked:

   root@ytt-ubuntu:/home/ytt# mysql -u ytt_fake -p -hytt-ubuntu
   Enter password: 
   ERROR 1045 (28000): Access denied for user 'ytt_fake'@'ytt-ubuntu' (using password: YES)
   root@ytt-ubuntu:/home/ytt# mysql -u ytt_fake -p -hytt-ubuntu
   Enter password: 
   ERROR 3955 (HY000): Access denied for user 'ytt_fake'@'ytt-ubuntu'. Account is blocked for 7 day(s) (7 day(s) remaining) due to 2 consecutive failed logins.

Use real users to log in, not affected by proxy users: real users can log in normally.

   root@ytt-ubuntu:/home/ytt# mysql -u ytt_real -p -hytt-ubuntu -e "select 'hello world\!'";
   Enter password: 
   +--------------+
   | hello world! |
   +--------------+
   | hello world! |
   +--------------+

After the user account is locked and login is prohibited, in addition to manually unlocking and resetting the counter by the administrator, there are also the following methods to reset the counter:

1. The MySQLD service is restarted.
2. Execute FLUSH PRIVILEGES to brush the disk of user authority data.
3. A successful account login.
4. The lock time has expired. For example, if the lock time is 7 days and the administrator does not do any processing within 7 days, the FLTTAL counter is reset.
5. Administrator changes failed again_ login_ Attempts or password_lock_time option, FLTTAL counter reset.

Summary:

Here we explain the failed login tracking and temporary password locking strategy of MySQL 8.0. Combined with other password verification strategies introduced before, it can make up for the shortcomings of MySQL database in this field.