MySQL Security – User Account Locking

March 14, 2018

If you are using MySQL 8.0, I would recommend you to read : MySQL Security – Failed-Login Tracking and Temporary Account Locking


When thinking about security within a MySQL installation, you should 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 in order to protect your data including some advanced features like Transparent Data Encryption aka TDEAudit, Data Masking & De-Identification, Firewall, Password Management, Password Validation Plugin, The Connection-Control Plugins, etc…

MySQL Security

For security reasons some context require you to setup a user account locking policy. Thus an unauthorized user is not able (anymore) to login to the MySQL server. In this 3rd article of the MySQL Security series, we will see how to [un]lock a user account.

User Account Locking

MySQL supports locking and unlocking user accounts using the ACCOUNT LOCK and ACCOUNT UNLOCK clauses for the CREATE USER and ALTER USER statements:

  • When used with CREATE USER, these clauses specify the initial locking state for a new account. In the absence of either clause, the account is created in an unlocked state.
  • When used with ALTER USER, these clauses specify the new locking state for an existing account. In the absence of either clause, the account locking state remains unchanged.

So let’s create a user batchman { not this one 🙂 } with all privileges on sakila database:

Account locking state is recorded in the account_locked column of the mysql.user table:

The output from SHOW CREATE USER indicates whether an account is locked or unlocked:

Indeed accounts are unlock by default.

The new user account is allowed to connect to the server and can handle sakila’s objects:

Lock account

What if your security policy requires you to lock this account?

ACCOUNT LOCK clause is the answer :

batchman@localhost is now locked!

Any connection attempt with this user will failed :

Note.

If the user is still connected you’ll need to kill the connection.

MySQL returns an ER_ACCOUNT_HAS_BEEN_LOCKED error.

The server increments the Locked_connects status variable that indicates the number of attempts to connect to a locked account and writes a message to the error log :

In order to go further

MySQL Security Series

  1. Password Validation Plugin
  2. Password Management
  3. User Account Locking
  4. The Connection-Control Plugins
  5. Enterprise Audit
  6. Enterprise Transparent Data Encryption (TDE)
  7. Enterprise Firewall
  8. Enterprise Data Masking and De-Identification

Reference Manual

MySQL Security

Thanks for using MySQL!

Follow me on twitter

Watch my videos on my YouTube channel and subscribe.

My Slideshare account.

My Speaker Deck account.

Thanks for using MySQL!

15 Responses to “MySQL Security – User Account Locking”

  1. […] User Account Locking […]

  2. […] User Account Locking […]

  3. […] On peut affirmer que MySQL n’a jamais été très flexible quand à la gestion des usagers!  Mais ce temps est révolu avec la fonctionnalité de account locking!  Les détails ici. […]

  4. […] Data Encryption aka TDE,  Audit, Firewall, Password Management, Password Validation Plugin, User Account Locking, […]

  5. […] some advanced features like Audit, TDE, Password Management, Password Validation Plugin, User Account Locking, […]

  6. […] some advanced features like  Audit,  Firewall, Password Management, Password Validation Plugin, User Account Locking, […]

  7. […] User Account Locking : Configurer une stratégie de verrouillage des comptes utilisateur. […]

  8. […] Data Masking & De-Identification, Password Management, Password Validation Plugin, User Account Locking, […]

  9. […] MySQL Security – User Account Locking […]

  10. […] User Account Locking […]

  11. […] User Account Locking […]

  12. […] User Account Locking […]

  13. […] User Account Locking […]

  14. […] User Account Locking […]

  15. […] User Account Locking […]