MySQL 8.0.13: Change Current Password Policy

We have introduced a new policy for you to enforce on your non-privileged users. It requires their current password at the time they set a new password. It is optional and off by default. You can control it globally (for all non-privileged users) or on a per-user basis.

This policy makes it harder for some to permanently alter a user’s password if they get access to user’s session and not the credentials themselves.

We have added this improvement through WL#11544 along with the various kinds of password management tools already available in MySQL.

Privileged vs Non-privileged users

You can categorize users in two categories based on whether or not they can change the password of other users.

Privileged user:

If a user either has ‘CREATE USER’ privilege, or ‘UPDATE’ privilege on the mysql database. These users can change the password of other users. Usually privileged users are those who have administrative privileges.

Non-privileged user:

The other users who cannot change the password except their own are called non-privileged users since there is no special privilege is required to change their own password.  The following change current password policy is applicable to these users. Henceforth, we shall refer non-privileged users as user unless explicitly mentioned otherwise.

The Replace Clause

Let us first see how the users will provide the current password. We shall understand the change current password policy right after it. We have added the REPLACE clause to the existing ALTER/CREATE USER … IDENTIFIED BY and SET PASSWORD statements. Users will have to provide this clause at the time they set the new password as following.

For instance:

Change Current Password Policy

It is a policy that determines whether the users have to provide the current password at the time they set the new password. This policy is governed by the following new settings.

  • Extended password_option clauses in the CREATE|ALTER USER SQL statements.
  • A Boolean global server variable ‘password_require_current’ with default value as OFF.

You can enforce this policy to the users broadly in either of the following four modes.

  1. Enforce all users to provide the current password but exempt a few users.
  2. Exempt all users to provide the current password but enforce a few users.
  3. Enforce the default behavior but exempt/enforce it from a few users.
  4. Hybrid mode.

Let us understand each mode and its pros vs cons

1. Enforce all the users to provide the current password but exempt a                  few users

You can enforce all of the users to provide the current password at the time of changing their password.

  • Create the new user with password option clause as following.

  • Modify the password option of existing users as following.

  • There could be exception to above rule. You can exempt the policy to such users as following.

Pros:

  • You will enforce all the users to provide the current password irrespective of the value of global system variable ‘password_current_require’. Thus this mode ensures higher security.

Cons:

  • You will have to update the password option to ‘PASSWORD REQUIRE CURRENT’ for the existing users since the default value of this clause is ‘PASSWORD REQUIRE CURRENT DEFAULT’.
  • You will have to ensure that all new users are created with the password option clause ‘PASSWORD REQUIRE CURRENT’.
2. Exempt all users to provide the current password but enforce a few users

You can enforce the selected users to provide the current password but not the rest of the users.

  • Create the new user with password option clause as following.

  • Modify the password option of existing users as following.

  • There could be exception to above rule. You can enforce the policy to such users as following.

Pros:

  • Convenient since majority of the users will not be asked to provide the current password.

Cons:

  • You will have to update the password option to ‘PASSWORD REQUIRE CURRENT OPTIONAL’  for the existing users since the default value of this clause is ‘PASSWORD REQUIRE CURRENT DEFAULT’.
  • You will have to ensure that all new users are created with the password option clause ‘PASSWORD REQUIRE CURRENT OPTIONAL’.
  • This mode is less secure than the previous mode.
3. Enforce the default behavior but exempt/enforce it from a few users.

This mode provides the fine balance between the previous two modes. It is controlled by two settings. The password option clause ‘PASSWORD REQUIRE CURRENT DEFAULT’ and the system variable ‘password_require_current’.  The system variable makes it easier to exempt or enforce the policy to all users.

Users get password option clause ‘PASSWORD REQUIRE CURRENT DEFAULT’ in following scenarios.

  • You have upgraded the server.
  • You are creating the new user and providing this password option clause explicitly.

  • You are creating the new user without specifying this clause.

  • You are modifying the clause for the existing user.

Now, you can exempt or enforce the current password policy for the users who have above password option clause set.

  • password_require_current=ON means that you enforce the users to provide the current password.
  • Password_require_current=OFF means that you exempt the user to not provide the current password.

Pros:

  • You can easily toggle the policy behavior. Therefore, you will have fine grain control over the policy.
  • This password option clause is set by default hence there is no need to provide either of the other two password option clauses.

Cons:

  • You have to be careful while changing the system variable value. If you toggle the value from ON to OFF then you might loosen the security. If you toggle it from OFF to ON then you might break the default behavior for all the users with above clause.
4. Hybrid Mode

In a perfect world, the first mode would have been sufficient to tighten the security for all the users. But, in the real world there could be situations when you would mix of all three modes we discussed above.

For instance:  You need to create three users Alice, Bob and Carole.

  • You may need to enforce the current password policy to Alice always.
  • You may need to exempt the Bob from the current password policy always.
  • You may need to control the policy through system variable for Carole.

It is possible to achieve above behavior as following.

We can summarize the current password policy as following based on the above discussions.

password_option Clause password_require_curent Server Variable ValueCurrent Password
PASSWORD REQUIRE CURRENT OFFRequired
PASSWORD REQUIRE CURRENT ONRequired
PASSWORD REQUIRE CURRENT OPTIONAL OFFNot required
PASSWORD REQUIRE CURRENT OPTIONAL ONNot required
PASSWORD REQUIRE CURRENT DEFAULT OFFNot required
PASSWORD REQUIRE CURRENT DEFAULT ONRequired
Clause is not specified OFFNot required
Clause is not specified ONRequired

As you might have observed in above table that the password_option clause takes higher precedence over the global server variable.

Changing the Password of Others

There could also be situations when the policy is enforced to a user but he forgot his current password. In such circumstances, privileged users come to the rescue.

Only privileged users can change the password of other users. Privileged users need not to know the current password of the user whose password is being changed. Let us understand the behavior through an example.

It will also be worth mentioning that for privileged users specifying the current password is always optional.

One general advice is that you should always enforce the users to use secure connections to connect to the server.  Refer the manual section for more details.

Conclusion

We had added Password rotation policy in MySQL 8.0. Now, with MySQL 8.0.13 we have further strengthened password management by adding the capability for users to specify the current password in order to change the same.  We demonstrated different ways of using the ‘change current password policy’.  We hope you’ll give it a try and provide us feedback. For more details and usage samples, please refer manual section.

Thank you for using MySQL!

2 thoughts on “MySQL 8.0.13: Change Current Password Policy

  1. In 2. Exempt all users to provide the current password but enforce a few users,
    it seems the three sqls all ends with
    REQUIRE CURRENT OPTIONAL,
    is there any mistake?

Leave a Reply

Your email address will not be published. Required fields are marked *

Please enter * Time limit is exhausted. Please reload CAPTCHA.