MySQL :: 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:

root@mysql> CREATE USER Alice IDENTIFIED BY ‘pwd@1’ PASSWORD
         -> REQUIRE CURRENT;
Query OK, 0 rows affected (0.00 sec)

root@mysql> ALTER USER Alice IDENTIFIED BY ‘pwd@2’;
ERROR 13207 (HY000): Current password needs to be specified in the REPLACE clause in order to change it.

root@mysql> ALTER USER Alice IDENTIFIED BY ‘pwd@2’ REPLACE ‘xyz’;
ERROR 13206 (HY0000): Incorrect current password. Specify the correct password which has to be replaced.

root@mysql> ALTER USER Alice IDENTIFIED BY ‘pwd@2’ REPLACE ‘pwd@1’;
Query OK, 0 rows affected (0.00 sec)

Bob@mysql> SET PASSWORD='pwd@3' REPLACE 'pwd@2';
Query OK, 0 rows affected (0.00 sec)

root@mysql> CREATE USER Alice IDENTIFIED BY ‘pwd@1’ PASSWORD

-> REQUIRE CURRENT;

Query OK, 0 rows affected (0.00 sec)

root@mysql> ALTER USER Alice IDENTIFIED BY ‘pwd@2’;

ERROR 13207 (HY000): Current password needs to be specified in the REPLACE clause in order to change it.

root@mysql> ALTER USER Alice IDENTIFIED BY ‘pwd@2’ REPLACE ‘xyz’;

ERROR 13206 (HY0000): Incorrect current password. Specify the correct password which has to be replaced.

root@mysql> ALTER USER Alice IDENTIFIED BY ‘pwd@2’ REPLACE ‘pwd@1’;

Query OK, 0 rows affected (0.00 sec)

Bob@mysql> SET PASSWORD='pwd@3' REPLACE 'pwd@2';

Query OK, 0 rows affected (0.00 sec)

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.

Enforce all users to provide the current password but exempt a few users.
Exempt all users to provide the current password but enforce a few users.
Enforce the default behavior but exempt/enforce it from a few users.
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.

root@mysql> CREATE USER Alice IDENTIFIED BY  'pwd@1' PASSWORD
         -> REQUIRE CURRENT;

1 2	root@mysql> CREATE USER Alice IDENTIFIED BY 'pwd@1' PASSWORD -> REQUIRE CURRENT;

Modify the password option of existing users as following.

root@mysql> ALTER  USER  Bob IDENTIFIED BY 'pwd@2' PASSWORD
         -> REQUIRE CURRENT;

1 2	root@mysql> ALTER USER Bob IDENTIFIED BY 'pwd@2' PASSWORD -> REQUIRE CURRENT;

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

root@mysql> ALTER USER Carole IDENTIFIED BY ‘pwd@3’ PASSWORD REQUIRE  
         -> CURRENT OPTIONAL;

1 2	root@mysql> ALTER USER Carole IDENTIFIED BY ‘pwd@3’ PASSWORD REQUIRE -> CURRENT OPTIONAL;

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.

root@mysql> CREATE USER Alice IDENTIFIED BY ‘pwd@1’ PASSWORD REQUIRE
         -> CURRENT OPTIONAL;

1 2	root@mysql> CREATE USER Alice IDENTIFIED BY ‘pwd@1’ PASSWORD REQUIRE -> CURRENT OPTIONAL;

Modify the password option of existing users as following.

root@mysql> ALTER USER Bob IDENTIFIED BY 'pwd@2' PASSWORD REQUIRE 
         -> CURRENT OPTIONAL;

1 2	root@mysql> ALTER USER Bob IDENTIFIED BY 'pwd@2' PASSWORD REQUIRE -> CURRENT OPTIONAL;

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

root@mysql> ALTER USER Carole IDENTIFIED BY ‘pwd@3’ PASSWORD REQUIRE 
         -> CURRENT;

1 2	root@mysql> ALTER USER Carole IDENTIFIED BY ‘pwd@3’ PASSWORD REQUIRE -> CURRENT;

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.

root@mysql> CREATE USER Alice IDENTIFIED BY ‘pwd@1’ PASSWORD REQUIRE 
         -> CURRENT DEFAULT;

1 2	root@mysql> CREATE USER Alice IDENTIFIED BY ‘pwd@1’ PASSWORD REQUIRE -> CURRENT DEFAULT;

You are creating the new user without specifying this clause.

root@mysql > CREATE USER Alice IDENTIFIED BY ‘pwd@1’;

1	root@mysql > CREATE USER Alice IDENTIFIED BY ‘pwd@1’;

You are modifying the clause for the existing user.

root@mysql> ALTER USER Bon IDENTIFIED BY ‘pwd@2’ PASSWORD REQUIRE 
         -> CURRENT DEFAULT;

1 2	root@mysql> ALTER USER Bon IDENTIFIED BY ‘pwd@2’ PASSWORD REQUIRE -> CURRENT DEFAULT;

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.

root@mysql> CREATE USER Alice IDENTIFIED BY ‘pwd@1’ 
         -> PASSWORD REQUIRE CURRENT;

root@mysql> CREATE USER Bob IDENTIFIED BY ‘pwd@2’ 
         -> PASSWORD REQUIRE CURRENT OPTIONAL;

root@mysql> CREATE USER Carol IDENTIFIED BY ‘pwd@3’ 
         -> PASSWORD REQUIRE CURRENT DEFAULT;

root@mysql> CREATE USER Alice IDENTIFIED BY ‘pwd@1’

-> PASSWORD REQUIRE CURRENT;

root@mysql> CREATE USER Bob IDENTIFIED BY ‘pwd@2’

-> PASSWORD REQUIRE CURRENT OPTIONAL;

root@mysql> CREATE USER Carol IDENTIFIED BY ‘pwd@3’

-> PASSWORD REQUIRE CURRENT DEFAULT;

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

password_option Clause	password_require_curent Server Variable Value	Current Password
PASSWORD REQUIRE CURRENT	OFF	Required
PASSWORD REQUIRE CURRENT	ON	Required
PASSWORD REQUIRE CURRENT OPTIONAL	OFF	Not required
PASSWORD REQUIRE CURRENT OPTIONAL	ON	Not required
PASSWORD REQUIRE CURRENT DEFAULT	OFF	Not required
PASSWORD REQUIRE CURRENT DEFAULT	ON	Required
Clause is not specified	OFF	Not required
Clause is not specified	ON	Required

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.

root@mysql> CREATE USER Eve IDENTIFIED BY 'pwd@1';
Query OK, 0 rows affected (0.00 sec)

root@mysql> GRANT CREATE USER ON *.* to Eve WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

Eve@mysql> ALTER USER Alice IDENTIFIED BY 'pwd@3' REPLACE 'pwd@2';
ERROR 13205 (HY000): Do not specify the current password while changing it for other users.

Eve@mysql> ALTER USER Alice IDENTIFIED BY 'pwd@3';
Query OK, 0 rows affected (0.00 sec)

root@mysql> CREATE USER Eve IDENTIFIED BY 'pwd@1';

Query OK, 0 rows affected (0.00 sec)

root@mysql> GRANT CREATE USER ON *.* to Eve WITH GRANT OPTION;

Query OK, 0 rows affected (0.00 sec)

Eve@mysql> ALTER USER Alice IDENTIFIED BY 'pwd@3' REPLACE 'pwd@2';

ERROR 13205 (HY000): Do not specify the current password while changing it for other users.

Eve@mysql> ALTER USER Alice IDENTIFIED BY 'pwd@3';

Query OK, 0 rows affected (0.00 sec)

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

Eve@mysql> ALTER USER Eve IDENTIFIED BY 'pwd@3' REPLACE 'pwd@1';
Query OK, 0 rows affected (0.00 sec)

Eve@mysql> ALTER USER Eve IDENTIFIED BY 'pwd@3';
Query OK, 0 rows affected (0.00 sec)

Eve@mysql> ALTER USER Eve IDENTIFIED BY 'pwd@3' REPLACE 'pwd@1';

Query OK, 0 rows affected (0.00 sec)

Eve@mysql> ALTER USER Eve IDENTIFIED BY 'pwd@3';

Query OK, 0 rows affected (0.00 sec)

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!