The recent MySQL 5.6.6 release includes a new password verification plugin. This is a great tool for enforcing passwords that meet certain strength thresholds. Quoting the options from the manual, there are three different criteria levels that can be applied:

STRONG policy adds the condition that password substrings of length 4 or longer must not match words in the dictionary file, if one has been specified.

Note that the definitions for WEAK and MEDIUM include references to default criteria that can actually be further tweaked to your institutional requirements. If you require passwords to be at least 12 characters long, and you don’t require both upper and lower-case letters in your passwords, you can customize this:

(You can also set these in server configuration files or as command-line arguments to keep these values across server restarts.)

I wasn’t 100% certain I understood how validate_password_mixed_case_count worked, so I tested it (I changed other variables to allow a password 4 characters in length, and not to check for other character classes):

So, a value of “2” means that you must specify at least two upper-case andtwo lower-case letters.

Perceptive readers will notice above that I wasn’t actually setting any user passwords in the examples – I was just doing a straight SELECT PASSWORD(); command. That’s leading to the second point of this blog post (hint is in the title), but I’ll leave that for the end.

The highest criteria level is STRONG, which allows you to also check against a dictionary file. You enable it by setting the validate_password_policy_number property to “STRONG”. Here’s where I start to get a bit picky. I don’t like properties that say they are “numbers” taking values like “STRONG” – even if they are just symbols that are tied to numeric values in the background. I filed a bug report; hopefully that can be changed before codified in something more permanent than a DMR release.

The dictionary file used by STRONG level password validation has a few caveats spelled out in the manual:

Its contents should be lowercase, one word per line. Contents are treated as having a character set of utf8. The maximum permitted file size is 1MB.

My testing suggests a few things of note:

The password file must be specified at server startup, and cannot be changed at runtime (documented as such).

The password file contents are cached, and can only be flushed by restarting the server (filed feature request).

No warning is generated if the specified file exceeds 1MB threshold, and the password file is silently ignored (see bug report).

Any strings in the dictionary file containing upper-case letters are silently ignored.

You can use the dictionary file to prohibit non-alpha sequences like, “1234”.

The validation checking is done regardless of whether PASSWORD() or OLD_PASSWORD() are used.

It’s really great to finally have control over password strength policy in the database, and to be able to tune the behavior to meet the password strength requirements for your organization. It’s a great start towards support of a comprehensive password policy inside MySQL – the two most common additional requirements (not yet addressed) are ensuring periodic password rotation, and prohibiting password reuse.

When the plugin is enabled, the password validation is done within the PASSWORD() function call. That means that you can check your password validation settings without actually setting passwords, by using SELECT PASSWORD() statements as I did above.

That also has a pretty significant implication for applications that leverage PASSWORD() to store hashes. The documentation for the PASSWORD() function explicitly discourages this, but it’s done. I should know; I ignored the advice many years ago (and got bit when PASSWORD() output changed in 4.1). Quoting the manual:

Users who have ignored this good advice in the manual – as I once did – will find that enabling the password validation plugin in 5.6 may impact their application. It’s worth double-checking for any application uses of PASSWORD() (or OLD_PASSWORD()).

As you note, this feature adjusts the functionality of the PASSWORD() function. A user can still set their password directly to the hash they want to use. This isn’t perhaps a very big concern, but it makes it easy to circumvent these requirements for anyone who wishes to do so:

I agree. On the system initially tested, the dictionary was under 1MB. I opened a feature request to allow MySQL to use the standard dictionaries available. Please feel free to comment on that feature request if you think anything else is needed to enable this.