Get instant live expert help with SQL
“My expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Enter your problem description Our Experts are available now

Your message must be at least 40 characters
Select your SQL Environment:
Your message must be at least 40 characters
By submitting this form you agree to Got It's privacy policy.

What is MySQL Message You Must Reset Your Password – Querychat

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:

  1. 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.

  1. 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');

Behaviour Precedence

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.

  1. Log in to the database:

  1. Execute some queries:

Show tables;

select count(*) from tmp_data_001;

  1. Login to SQL workbench and force expire password from the Management and Sessions tab.

  1. Login again from MySQL client and see the message asking the user to SET his/her password.

  1. Set the password and the normal execution can go on after setting it.

 

Leave a Reply

avatar
  Subscribe  
Notify of
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc