MySQL 5.7 User Password Expiration

databases
mysql

#1

MySQL 5.7 User Password Expiration

Starting with MySQL 5.7.4 and in later versions, user passwords will now have an expiration timer set, by default, for 360 days. If you would like to keep this functionality, you certainly can; however, for some the need to change your user passwords once a year could be a nuisance.

If you suddenly come across the following error, it is likely that your user’s password as expired.

 ERROR 1820 (HY000): You must SET PASSWORD before executing this statement

Before you can make any changes one of two actions must happen, otherwise, you could continually meet the same error.

Firstly, you could modify the password, thereby resetting the password expiry flag. You can accomplish this by using the following Query. Replacing ‘user’ with the user whose password is expired and ‘host’ which is the server’s address. Finally,‘MyNewPass’ would be the new password.

 ALTER USER 'user'@'host' IDENTIFIED BY 'MyNewPass';

A second option is to set the password expiry flag to never expire and restart the MySQL service.

The setting that is responsible for this behavior is "default_password_lifetime"

There are a few methods for modifying this setting for your MySQL server instances.

  1. Global Password Expiration Variable
  2. Single User Password Expiration
  3. Global Password Expiration in my.cnf (my.ini for Windows)

Global Password Expiration Variable

Within the MySQL server instance CLI (command line interface) or using a GUI client like PHPMyAdmin or MySQL Workbench, you can execute a query to modify the setting for all users password expiry. Once you have logged into your MySQL instance, run the following query to remove the Password Expiry.

show global variables like 'default_password_lifetime';

This query will output the value for the “default_password_lifetime” variable. The output should look similar to the following.

 +---------------------------+-------+
 | Variable_name | Value |
 +---------------------------+-------+
 | default_password_lifetime | 360 |
 +---------------------------+-------+
 1 row in set (0.00 sec)

As you can see in the example above the value is set to “360.” That numeric value is representative of days. So this value would be the system default.

To alter this value, run the query below:

 set global default_password_lifetime=0;

Note: When running SQL queries remember your semi-colon (";") at the end of the query!

Now that the value has been altered, you can run the same “show” query to see your change.

mysql> show global variables like 'default_password_lifetime';
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| default_password_lifetime | 0 |
+---------------------------+-------+
1 row in set (0.00 sec)

Single User Password Expiration

If changing the variable globally is a little too broad of a stroke, you can elect to modify this rule for a single user.

Log into your MySQL instance and then run the query below:

 ALTER USER 'user'@'host' PASSWORD EXPIRE NEVER

Note: Replace the user and host with the appropriate value.

This will allow the user you have specified in the query to have a password that will not expire; however, this will only affect the user for that particular “host.” You will need to apply the “Alter User” statement to all of the user/host iterations to ensure that the password remains the same for each one.

Note: You can find all of the user/host iterations by running the following query:

 SELECT User, Host FROM mysql.user;

Global Password Expiration in my.cnf

This method of modifying the Password Expiration variable is not within the MySQL instance itself but instead, you will be manipulating the value from the configuration file. This method will require that you restart the MySQL instance to implement, where the variable through the query method will be implemented without a restart of the MySQL server.

  1. Locate your “my.cnf” file (generally this file will be located in your “/etc/” directory for a Linux OS) and edit the file with your preferred text editor.

    • If you are using Windows, you are looking for an “my.ini” file in your “/MySQL/” directory
  2. Next locate or add the following line:

    [mysqld]
    default_password_lifetime=0

Note: Make sure that the “default_password_lifetime” is nested, or placed under the [mysqld] line.

  1. Save the file and restart your MySQL server instance.