Using MySQL 8.0.34 validate_password.changed_characters_percentage to Enforce Password Change Requirements

MySQL 8.0.34 introduces a new password‑validation variable validate_password.changed_characters_percentage that controls the minimum percentage of characters that must differ between the old and new passwords before the change is accepted.

1 Requirement

To use this feature you must enable the password verification policy (available since MySQL 8.0.13) by setting the global variable password_require_current or using the PASSWORD REQUIRE CURRENT clause when creating or altering a user. The author recommends enabling password_require_current globally.

set persist password_require_current = 1;
select @@password_require_current;
+---------------------------+
| @@password_require_current |
+---------------------------+
| 1                         |
+---------------------------+

2 Create Test Environment

A MySQL 8.0.34 instance is installed and the validate_password component is loaded.

select @@version, @@version_comment;
+----------+---------------------------+
| @@version| @@version_comment          |
+----------+---------------------------+
| 8.0.34   | MySQL Community Server - GPL |
+----------+---------------------------+
INSTALL COMPONENT 'file://component_validate_password';
select @@validate_password.changed_characters_percentage;
+-----------------------------------------------+
| @@validate_password.changed_characters_percentage |
+-----------------------------------------------+
| 0                                             |
+-----------------------------------------------+

3 Testing

The variable is set to 50, meaning at least half of the characters must differ when a password is changed.

set global validate_password.changed_characters_percentage = 50;
select @@validate_password.changed_characters_percentage;
+-----------------------------------------------+
| @@validate_password.changed_characters_percentage |
+-----------------------------------------------+
| 50                                            |
+-----------------------------------------------+

A user percona1 with password Percona@321 is created and granted SELECT privileges.

create user 'percona1'@'localhost' identified by 'Percona@321';
grant select on *.* to 'percona1'@'localhost';
flush privileges;

Attempting to change the password to Percona@567 fails because only three characters differ, which is less than the required 50% (five characters).

alter user percona1@localhost identified by 'Percona@567' replace 'Percona@321';
ERROR 4165 (HY000): The new password must have at least '5' characters that are different from the old password. It has only '3' character(s) different.

Changing to Percona%#567 succeeds because it contains five different characters.

alter user percona1@localhost identified by 'Percona%#567' replace 'Percona@321';
Query OK, 0 rows affected

4 How It Handles Case Sensitivity

A second user percona2 with password PERCONa@321 is created. Changing the password to perconA@321 fails because MySQL treats upper‑ and lower‑case letters as equal for this comparison.

alter user percona2@localhost identified by 'perconA@321' replace 'PERCONa@321';
ERROR 4165 (HY000): The new password must have at least '5' characters that are different from the old password. It has only '0' character(s) different.

5 Handling Different Character Counts

Three more scenarios are tested. Adding characters that already exist in the old password (e.g., appending several '3's) does not satisfy the percentage requirement, while adding truly new characters does.

alter user percona3@localhost identified by 'Percona@3214455667788' replace 'Percona@321';
Query OK, 0 rows affected

6 Conclusion

MySQL 8.0 brings significant security improvements; the validate_password.changed_characters_percentage variable provides a flexible way to enforce stronger password changes.

References

A quick peek at MySQL 8.0.34 and MySQL 8.1.0

MySQL 8: Password Verification Policy