MySQL Security – Password Management

March 7, 2018

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, Firewall, Password Validation PluginUser Account Locking, etc…

MySQL Security

Some regulations required that the password is renewed in a timely and appropriate manner (e.g. every 90 days). In this article, 2nd of the MySQL 5.7 Security series, we will see how to establish a policy for password expiration with MySQL 5.7 Password Management.


Password Management


Basic password policy practices teaches us :

  • Each user must have a password
  • A user’s password should be changed periodically

MySQL provides password-expiration capability, which enables database administrators to require that users reset their password.

Let’s have a closer look!


Basically there are different clauses a DBA can use with CREATE USER or ALTER USER to establish a per account password expiration policy.

Let’s play with some of them.



Force user to change its password at the first connection.

Note that password_expired column is Y.

This new MySQL user will be able to connect to the server but he must reset its password.


Column password_expired is now N.




Force user to change its password every N days.

Note that password_lifetime column is 90.


After 90 days any statement will generate error 1820 :


Password could be reset with ALTER USER command :




It’s also possible to disable password expiration for an account

Note that password_lifetime column is 0.




This clause sets the account so that the global password expiration policy applies, as specified by the default_password_lifetime system variable.

In MySQL 5.7 it applies to accounts that use MySQL built-in authentication methods (accounts that use an authentication plugin of mysql_native_password or sha256_password).


The default default_password_lifetime value is 0, which disables automatic password expiration. If the value of default_password_lifetime is a positive integer N, it indicates the permitted password lifetime; passwords must be changed every N days.


default_password_lifetime can be changed at runtime with SET GLOBAL command. However it must be set in the MySQL configuration file for persistence.


e.g. Create a user account with the default global password policy (90 days)



In order to go further

MySQL 5.7 Security Series

  1. Password Validation Plugin
  2. Password Management
  3. User Account Locking


Reference Manual


MySQL Security


Thanks for using MySQL!


2 Responses to “MySQL Security – Password Management”

  1. […] Password Management […]

  2. […] data including some advanced features like Transparent Data Encryption aka TDE,  Audit, Firewall, Password Management, Password Validation Plugin, […]

Leave a Reply