Force MySQL User to Change Password on Next Login

Managing database security often requires administrators to enforce password rotation policies or temporarily reset a user’s credentials. In MySQL, you can easily force a user to change their password upon their next login attempt using the PASSWORD EXPIRE clause. This article provides a straightforward guide on how to implement this security measure using SQL commands.

The SQL Command

To force a MySQL user to change their password on their next login, you use the ALTER USER statement with the PASSWORD EXPIRE option. You must run this command as a user with administrative privileges (such as root).

Run the following command in your MySQL terminal:

ALTER USER 'username'@'host' PASSWORD EXPIRE;

Replace 'username' with the specific MySQL user and 'host' with their designated connection host (for example, 'localhost' or '%').

What Happens Next?

Once this command is executed, the targeted user can still establish a connection to the database server using their current password. However, they will be restricted from executing any queries.

If the user attempts to run a query, MySQL will return the following error:

ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

To regain full database access, the user must update their password during that session by running:

ALTER USER USER() IDENTIFIED BY 'new_secure_password';

Setting Automatic Password Expiration Policies

If you prefer to set a policy where passwords expire automatically after a certain number of days rather than forcing an immediate manual reset, you can use the following methods:

For a Specific User:

To force a user to change their password every 90 days:

ALTER USER 'username'@'host' PASSWORD EXPIRE INTERVAL 90 DAY;

Globally for All Users:

To set a global policy where all MySQL passwords expire every 180 days, configure the default_password_lifetime system variable in your MySQL configuration file (my.cnf or my.ini):

[mysqld]
default_password_lifetime=180

Alternatively, you can set this globally at runtime using:

SET GLOBAL default_password_lifetime = 180;

How to Undo an Expiration Force

If you accidentally forced a password expiration and want to revert it without requiring the user to change their password, you can reset the status by running:

ALTER USER 'username'@'host' PASSWORD EXPIRE NEVER;

This command restores the user’s normal access privileges and removes the requirement for an immediate password update.