Interpretation of new features | MySQL 8.0 new password policy (final part)

Author: Yang Taotao

Senior database expert, specializing in MySQL research for more than ten years. Good at MySQL, PostgreSQL, MongoDB and other open source databases related to backup and recovery, SQL tuning, monitoring operation and maintenance, high-availability architecture design, etc. Currently working in Aikesheng, providing MySQL-related technical support and MySQL-related course training for major operators and banking and financial enterprises.

Source of this article: original contribution

*The original content is produced by the open source community of Aikesheng, and the original content shall not be used without authorization. For reprinting, please contact the editor and indicate the source.

---

Scenarios we often encounter: When using a bank card to withdraw money at an ATM, transfer money on the APP, and make online purchases, etc., due to a certain number of consecutive wrong passwords, the bank card is locked and cannot be used unless you have a valid certificate. Go to the bank counter to manually unlock it before it can be used normally.

As the MySQL database is used in more and more financial scenarios, the card lock function caused by the continuous input of wrong bank card passwords is coming out. MySQL has launched a similar strategy since 8.0.19: Failed-Login Tracking and Temporary Account Locking. The translation is failed login tracking and temporary password lock, which we will refer to as FLTTAL later.

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

FLTTAL has the following points to note:

1. Both failed_login_attempts and password_lock_time must be non-zero for FLTTAL to take effect.

2. Create a new user without specifying failed_login_attempts and password_lock_time, FLTTAL is closed by default.

3. For users who have used FLTTAL, the administrator does not change the original password authentication policy after altering the user.

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

5. And the most important point: due to the continuity of FLTTAL's correct password verification, for any successful login, the FLTTAL counter is reset. For example, if failed_login_attempts is set to 3, the first two consecutive wrong passwords are entered, and the third time the correct password is entered, the FLTTAL counter is reset.

Then let's take a look at how to use this password authentication strategy specifically:

1. to use 161f8f699ee6c9 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 lockout 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)

If the wrong password is entered 3 times in a row, the account test1@'localhost' 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 unlocks the account for normal use: (Or if you forget the password, 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 correct password: Login successful.

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

2. For proxy users:

For proxy users, FLTTAL only affects the proxy user itself, and does not affect the hidden real user.

For the introduction of proxy users, please refer to my previous article: https://mp.weixin.qq.com/s/gw_yoF57-WPcUpy18MiigQ

Proxy user created earlier:

   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 plugin to mysql_native_password so that it can log in normally:

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

Set FLTTAL policy for proxy user ytt_fake: 2 failed retries and 7 days password lockout time.

   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.

Log in with a real user, without being affected by a proxy user: A real user 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 the login is prohibited, in addition to the administrator resetting the counter by unlocking it manually, there are several ways to reset the counter:

1. MySQLD service restart.
2. Execute FLUSH PRIVILEGES to flush the user permission data.
3. A successful account login.
4. The lock time has expired. For example, if the lock time is 7 days, the administrator does not do anything within 7 days, and the FLTTAL counter is reset.
5. The administrator re-changes the failed_login_attempts or password_lock_time options, and the FLTTAL counter resets.

Summarize:

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