MySQL allows database administrators to apply password expiration policies for the database users. What happens if a user with an expired password tries to connect to the database is what we will discuss in this article. MySQL allows different behaviors based on system settings and based on the client trying to establish the connection.
MySQL Password Expiration Mechanism
To understand what is the error message “You must reset your password using alter user statement before executing this statement” that the user may encounter, we need to first understand how password expiration mechanism works in MySQL.
The behavior of password expiration is different between versions of MySQL. In 5.7.11 and onwards, the default setting is to never expire a password automatically. Instead, an admin can either forcibly expire a password or needs to modify the system setting default_password_lifetime.
To forcibly expire the password of a user we need to use the ALTER USER statement as below:
ALTER USE user_name PASSWORD EXPIRE;
This immediately expires the password of the user.
To expire the password for the user automatically after N days we issue the following command
ALTER USER user_name PASSWORD EXPIRE INTERVAL N DAYS;
The DBA can apply password expiration policies globally using the default_password_lifetime global setting. The following line in my.cnf (MySQL ‘s configuration file) makes the password for all user expire automatically after 360 days:
default_password_lifetime = 360
The same behavior can be affected by using the SET command as below:
SET GLOBAL default_password_lifetime = 360
In MySQL 5.7.11 the default setting for default_password_lifetime is zero, meaning that the password never expires automatically. It can, however, be forcibly be expired. Inversions prior to this version, the default setting was 360, meaning that the password expires every 360 days (approximately a year).
To make a password never expire on user level we can issue the statement:
ALTER USER user_name PASSWORD EXPIRE NEVER
Or using SET statement :
SET GLOBAL default_password_lifetime = 0
Or using the following line in my.cnf file:
default_password_lifetime = 0
Mysql Message – You Must Reset Your Password Using Alter User Statement Before Executing This Statement
As explained in the previous section, the password of a user might have expired. That is the password is valid but is expired. The expiration could have been forced by a DBA or could have been the result of a system-wide/user-specific setting.
In this case (ie. the password has been expired) how the experience turns out to be from this stage onwards, depending on the client used to connect and the server settings.
MySQL presents two types of behaviors when we try to connect to it with an expired password:
- It disconnects the client with a message indicating the user that his/her password is expired and it needs to be reset. The exact message is:
ERROR 1862 (HY000): Your password has expired. To log in you must change it using a client that supports expired passwords.
- It connects in “Sandbox” mode, where a very limited set of operations are allowed. Specifically only the SET command is allowed. It is actually with this command that the user can change his expired password to something else.
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
The first type of behavior is observed when a client is a non-interactive client like programmatic access through a driver. In such cases, the session disconnects when an expired password is used.
The second behavior is observed in an interactive client like MySQL command line. In such a case, the interactive shell allows the user to establish a session but doesn’t permit normal operations unless and until the password is reset. To reset the password in this Sandbox mode we issue the following command:
SET PASSWORD = PASSWORD('new_password');
Whether a session shall be connected in Sandbox mode or disconnected on login depends on client and server settings. If the client indicates that it can connect via sandbox mode, the server allows and grants a sandbox session.
On the other hand, if the client is either unable to indicate its preference or cannot indicate its preference, the server’s setting namely disconnect_on_expired_password determines the behavior.
If disconnect_on_expired_password is enabled (default) the client is not able to log in and is disconnected when it tries to connect. If the setting is disabled, the sandbox mode is enabled.
Setting (Resetting) Password – Sample
Let’s demonstrate the workflow with the following steps. Let’s assume we have a database named trov1 and a user troy.
- Log in to the database:
- Execute some queries:
select count(*) from tmp_data_001;
- Login to SQL workbench and force expire password from the Management and Sessions tab.
- Login again from MySQL client and see the message asking the user to SET his/her password.
- Set the password and the normal execution can go on after setting it.