MySQL 8.0 Failed Login Tracking and Temporary Account Locking (FLTTAL) – How to Use

When using MySQL in financial scenarios, a mechanism similar to ATM card lockout is needed; since MySQL 8.0.19 the database offers Failed‑Login Tracking and Temporary Account Locking (FLTTAL).

Configuration options

FLTTAL is applied per‑user and has two parameters:

FAILED_LOGIN_ATTEMPTS – the number of consecutive failed logins allowed ( N ).

PASSWORD_LOCK_TIME – the lock duration in days after the limit is reached, or UNBOUNDED for indefinite lock.

Key points to remember

Both failed_login_attempts and password_lock_time must be non‑zero for FLTTAL to be active.

If a new user is created without these settings, FLTTAL is disabled by default.

Altering a user who already has FLTTAL does not change the existing policy.

Once locked, the account cannot log in even with the correct password.

A successful login resets the failure counter.

Using FLTTAL for a regular user

Administrator creates a user with a 3‑attempt limit and a 3‑day lock:

mysql:(none)>create user test1@'localhost' identified by 'test' failed_login_attempts 3 password_lock_time 3;

After three consecutive wrong passwords, the account is locked:

root@host# mysql -utest1 -p -S /opt/mysql/mysqld.sock
ERROR 3955 (HY000): Access denied for user 'test1'@'localhost'. Account is blocked for 3 day(s) due to 3 consecutive failed logins.

The administrator can unlock it:

mysql:(none)>alter user test1@'localhost' account unlock;

And the user can log in again with the correct password.

Using FLTTAL for a proxy user

FLTTAL affects only the proxy account, not the underlying real account.

Example proxy user creation and configuration:

mysql:(none)>show grants for ytt_fake;

mysql:(none)>alter user ytt_real identified with mysql_native_password;

mysql:(none)>alter user ytt_fake failed_login_attempts 2 password_lock_time 7;

After two wrong passwords, the proxy account is locked, while the real user can still log in normally.

root@host# mysql -u ytt_fake -p -hhost
ERROR 3955 (HY000): Access denied for user 'ytt_fake'@'host'. Account is blocked for 7 day(s) due to 2 consecutive failed logins.

root@host# mysql -u ytt_real -p -hhost -e "select 'hello world!'"

Ways to reset the lock counter

Restart the MySQL server.

Execute FLUSH PRIVILEGES .

Perform a successful login.

Wait for the lock time to expire.

Change failed_login_attempts or password_lock_time values.

Conclusion

The article demonstrates MySQL 8.0's failed login tracking and temporary password locking strategy, which can be combined with other password policies to address security gaps in database authentication.