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:
WEAKpolicy tests password length only. Passwords must be at least 8 characters long.
MEDIUMpolicy adds the conditions that passwords must contain at least 1 numeric character, 1 lowercase and uppercase character, and 1 special (nonalphanumeric) character.
STRONGpolicy 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:
mysql> SET GLOBAL validate_password_length = 12; Query OK, 0 rows affected (0.00 sec) mysql> SET GLOBAL validate_password_mixed_case_count = 0; Query OK, 0 rows affected (0.00 sec)
(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):
mysql> SET GLOBAL validate_password_mixed_case_count = 2; Query OK, 0 rows affected (0.00 sec) mysql> select password('test'); ERROR 1819 (HY000): Your password does not satisfy the current policy requirements mysql> select password('tesT'); ERROR 1819 (HY000): Your password does not satisfy the current policy requirements mysql> select password('teST'); +-------------------------------------------+ | password('teST') | +-------------------------------------------+ | *AFA290948A4DC0B109F5E263FA78A736DC66C139 | +-------------------------------------------+ 1 row in set (0.00 sec) mysql> select password('tEST'); ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
So, a value of “2” means that you must specify at least two upper-case and two 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:
PASSWORD()function is used by the authentication system in MySQL Server; you should not use it in your own applications. For that purpose, consider
SHA2()instead. Also see RFC 2195, section 2 (Challenge-Response Authentication Mechanism (CRAM)), for more information about handling passwords and authentication securely in your applications.
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()).