MySQL has various kinds of password policy enforcement tools: a password can expire (even automatically), can be forced to be of a certain length, contain amounts of various types of characters and be checked against a dictionary of common passwords or the user account name itself.

But one thing was missing until recently: there was no password history preserved. Thus, when people were required to change their password, they could just reuse their current password and call it a day !

This important omission was filled in by WL#6595: Password Rotation Policy.

Now a certain backlog of password hashes is preserved in a new system table (mysql.password_history) together with the timestamps of when these passwords were set and of course the user account to which they belong.

Here’s how the table drives on my server.

First, let’s set a global password policy:

Let’s create a user without a password:

Now let’s set a password for it (as foo):

Now let’s set another password (as foo):

And finally let’s drop the user:

There’s two parameters that define the password reuse policy for an user account:

• password history depth: this is the number of distinct passwords set before a password can be reused.
• password reuse interval: this is the amount of time it takes before a password can be reused again.

Imagine you set password history depth to 1. This means that rotating 2 passwords in the following order is allowed:

It also means that only 1 row will be needed in the password_history table for that account: that of the previous password:

Note: The server automatically maintains the mysql.password_history rows so you don’t have to worry about that. They are automatically added when changing your password and deleted when no longer needed.

You can of course inspect the table yourself and see how it drives.

The problem with the above setting is that one can do the changes in a very quick succession and still end up with the same password they’re used to. This is not very secure !

This is where the password reuse interval parameter can help.

It defines the minimum number of days (!) before a password can be reused. If you set password reuse interval to a non-zero value then no quick successions of password changes can result in the same password re-activated again as demonstrated above.

As root:

As foo (current password is foo, in the same day):

How can we set these two parameters ?

In a pretty standard way.

First there are two global variables: password_reuse_interval and password_history that control the parameters on global level for all user accounts that do not override the policy locally.

And if you need to override the globals for some accounts there are clauses to CREATE USER and ALTER USER that do that, e.g.

or

Once you do that the values for that user account are pinned to whatever is specified and the values of the global variables no longer apply.

And If you need to revert the per user override you can do that with

This gets the account back into following the global values.

Note that, to avoid surprises, by default the whole policy is turned off:

This password reuse policy works for all authentication methods that store passwords locally.

This currently means: mysql_native_password, sha256_password and caching_sha2_password. Luckily one of them is the default too 🙂

So don’t expect this to work for authentication methods like unix sockets authentication plugin or the windows native authentication. Mostly because the credentials for these are stored and maintained externally and the external stores usually provide good password reuse policies.

So go ahead, download a recent 8.0 package and give these a try !

If you’re lost there’s the excellent reference manual section to help you.

And thank you for using MySQL !