MySQL Security – Failed-Login Tracking and Temporary Account Locking
When thinking about security within a MySQL installation, you can consider a wide range of possible procedures / best practices and how they affect the security of your MySQL server and related applications.
MySQL provides many tools / features / plugins or components in order to protect your data including some advanced features like Transparent Data Encryption (TDE), Audit, Data Masking & De-Identification, Firewall, Random Password Generation, Password Expiration Policy, Password Reuse Policy, Password Verification-Required Policy, Dual Password Support, Connection-Control Plugins, Password Validation Component, etc…
![MySQL Security](https://i0.wp.com/dasini.net/blog/wp-content/uploads/MySQL_DB_Lock2.png?ssl=1)
Basic password policy practices teaches us :
- Each user must have a password
- A user’s password should be changed periodically
However, often this is unfortunately not enough.
Good news, MySQL 8.0 provide an easy way to increase database security with its failed-login tracking and temporary account locking feature.
TL;DR
DBA can configure user accounts such that too many consecutive login failures cause temporary account locking.
After a number of consecutive time when the client failed to provide a correct password during a connection attempt, the user account can be temporary locked.
The required number of failures and the lock time are configurable per account, using the FAILED_LOGIN_ATTEMPTS (track consecutive login failures) and PASSWORD_LOCK_TIME (how many days to lock the account).
Both are options of the CREATE USER and ALTER USER statements.
Let’s have a quick look using MySQL 8.0
1 2 3 4 5 6 |
MySQL SQL> SELECT VERSION(); +-----------+ | VERSION() | +-----------+ | 8.0.19 | +-----------+ |
Account lock
Create a user that would have his account locked for 1 day after 1 consecutive failed logins :
1 2 3 4 5 6 |
$ mysqlsh root@localhost:3306 --sql ... MySQL localhost:3306 ssl SQL> CREATE USER aUser@localhost IDENTIFIED BY 'pAssw0rD' FAILED_LOGIN_ATTEMPTS 1 PASSWORD_LOCK_TIME 1; |
FAILED_LOGIN_ATTEMPTS : how many consecutive incorrect passwords cause temporary account locking.
A value of 0 disables the option.
PASSWORD_LOCK_TIME : number of days the account remains locked or UNBOUNDED (ie the duration of that state does not end until the account is unlocked).
A value of 0 disables the option.
We can see the user account details with mysql.user table :
1 2 3 4 5 6 |
MySQL localhost:3306 ssl SQL> SELECT user, host, User_attributes FROM mysql.user WHERE user = 'aUser'\G *************************** 1. row *************************** user: aUser host: localhost User_attributes: {"Password_locking": {"failed_login_attempts": 1, "password_lock_time_days": 1}} |
If login failed a “FAILED_LOGIN_ATTEMPTS” number of time (1 time in this example), the account will be locked :
1 2 3 4 5 |
MySQL localhost:3306 ssl SQL> \connect aUser@localhost:3306 Creating a session to 'aUser@localhost:3306' Please provide the password for 'aUser@localhost:3306': * MySQL Error 3955 (HY000): Access denied for user 'aUser'@'localhost'. Account is blocked for 1 day(s) (1 day(s) remaining) due to 1 consecutive failed logins. |
Tracking and locking could also be set up after the user creation :
1 2 3 4 5 6 7 8 |
$ mysqlsh root@localhost:3306 --sql MySQL localhost:3306 ssl SQL> CREATE USER aUser2@localhost IDENTIFIED BY 'Dr0wssAp'; ALTER USER aUser2@localhost FAILED_LOGIN_ATTEMPTS 2 PASSWORD_LOCK_TIME UNBOUNDED; |
In this example this user account will be locked (until the account is unlocked – more on that later) after 2 consecutive failed attempts.
You can also lock an account explicitly using ACCOUNT LOCK clause :
1 2 3 4 5 6 7 |
MySQL localhost:3306 ssl SQL> CREATE USER aLockedUser@localhost IDENTIFIED BY RANDOM PASSWORD ACCOUNT LOCK; +-------------+-----------+----------------------+ | user | host | generated password | +-------------+-----------+----------------------+ | aLockedUser | localhost | @.Yp{;ONp7-G62+EfON1 | +-------------+-----------+----------------------+ |
In this example I created a user account with a random password generated by MySQL. This account is created locked.
Details are visible with mysql.user table :
1 2 3 4 5 6 7 |
MySQL localhost:3306 ssl SQL> SELECT user, host, account_locked FROM mysql.user WHERE user = 'aLockedUser'; +-------------+-----------+----------------+ | user | host | account_locked | +-------------+-----------+----------------+ | aLockedUser | localhost | Y | +-------------+-----------+----------------+ |
Any connection to this account will raised error 3118 :
1 2 3 4 5 |
MySQL localhost:3306 ssl SQL> \connect aLockedUser@localhost:3306 Creating a session to 'aLockedUser@localhost:3306' Please provide the password for 'aLockedUser@localhost:3306': ******************** MySQL Error 3118 (HY000): Access denied for user 'aLockedUser'@'localhost'. Account is locked. |
This account can be activate with something like :
1 2 |
MySQL localhost:3306 ssl SQL> ALTER USER aLockedUser@localhost ACCOUNT UNLOCK FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME UNBOUNDED; |
Again mysql.user table will give you some information :
1 2 3 4 5 6 7 |
MySQL localhost:3306 ssl SQL> SELECT user, host, account_locked, User_attributes FROM mysql.user WHERE user = 'aLockedUser'\G *************************** 1. row *************************** user: aLockedUser host: localhost account_locked: N User_attributes: {"Password_locking": {"failed_login_attempts": 5, "password_lock_time_days": -1}} |
Account unlock
Account can be unlocked with an ALTER USER … ACCOUNT UNLOCK statement :
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
MySQL localhost:3306 ssl SQL> \connect aUser@localhost:3306 Creating a session to 'aUser@localhost:3306' Please provide the password for 'aUser@localhost:3306': MySQL Error 3955 (HY000): Access denied for user 'aUser'@'localhost'. Account is blocked for unlimited day(s) (unlimited day(s) remaining) due to 2 consecutive failed logins. ALTER USER aUser@localhost ACCOUNT UNLOCK; Query OK, 0 rows affected (0.0047 sec) \connect aUser@localhost:3306 Creating a session to 'aUser@localhost:3306' Please provide the password for 'aUser@localhost:3306': ******** Closing old connection... Your MySQL connection id is 63 ... SELECT USER(); +-----------------+ | USER() | +-----------------+ | aUser@localhost | +-----------------+ |
Other possibilities to unlock an account are :
- Execution of an ALTER USER statement for the account that sets either FAILED_LOGIN_ATTEMPTS or PASSWORD_LOCK_TIME (or both) to any value.
e.g.
1 |
ALTER USER aUser@localhost FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME 1; |
- Obviously when the lock duration passes.
In this case, failed-login counting resets at the time of the next login attempt. - Execution of FLUSH PRIVILEGES
- A server restart
To Go Further
Reference Manual
MySQL Security Serie (1st edition)
- Password Validation Plugin
- Password Management
- User Account Locking
- The Connection-Control Plugins
- Enterprise Audit
- Enterprise Transparent Data Encryption (TDE)
- Enterprise Firewall
- Enterprise Data Masking and De-Identification
Thanks for using MySQL!
Watch my videos on my YouTube channel and subscribe.
Thanks for using MySQL!
![Olivier DASINI](https://i0.wp.com/dasini.net/blog/wp-content/uploads/daz_presenter_494x429_clean_BW-color.png?resize=100%2C100)
MySQL Geek, author, blogger and speaker
I’m an insatiable hunger of learning.
—–
Blog: www.dasini.net/blog/en/
Twitter: https://twitter.com/freshdaz
SlideShare: www.slideshare.net/freshdaz
Youtube: https://www.youtube.com/channel/UC12TulyJsJZHoCmby3Nm3WQ
—–
[…] If you are using MySQL 8.0, I would recommend you to read : MySQL Security – Failed-Login Tracking and Temporary Account Locking […]
[…] Firewall, Password Expiration Policy, Password Reuse Policy, Password Verification-Required Policy, Failed-Login Tracking and Temporary Account Locking, Dual Password Support, Connection-Control Plugins, Password Validation Component, […]
[…] Firewall, Random Password Generation, Password Reuse Policy, Password Verification-Required Policy, Failed-Login Tracking and Temporary Account Locking, Dual Password Support, Connection-Control Plugins, Password Validation Component, […]
[…] Random Password Generation, Password Expiration Policy, Password Verification-Required Policy, Failed-Login Tracking and Temporary Account Locking, Dual Password Support, Connection-Control Plugins, Password Validation Component, […]