Todd's MySQL Blog

Tiny tidbits of trivia from Todd

New 5.6 password verification plugin (and impacts to PASSWORD() function)

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:

  • WEAK policy tests password length only. Passwords must be at least 8 characters long.
  • MEDIUM policy adds the conditions that passwords must contain at least 1 numeric character, 1 lowercase and uppercase character, and 1 special (nonalphanumeric) character.
  • 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:

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:

  1. The password file must be specified at server startup, and cannot be changed at runtime (documented as such).
  2. The password file contents are cached, and can only be flushed by restarting the server (filed feature request).
  3. No warning is generated if the specified file exceeds 1MB threshold, and the password file is silently ignored (see bug report).
  4. Any strings in the dictionary file containing upper-case letters are silently ignored.
  5. You can use the dictionary file to prohibit non-alpha sequences like, “1234”.
  6. 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:

Note

The PASSWORD() function is used by the authentication system in MySQL Server; you should not use it in your own applications. For that purpose, consider MD5() or 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()).

 

 

Single Post Navigation

4 thoughts on “New 5.6 password verification plugin (and impacts to PASSWORD() function)

  1. On my system, /usr/share/dict/words is 2.4MB and contains mixed-case words. It would be handy to be able to use this standard/ubiquitous file.

  2. Kolbe Kegel on said:

    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:

    mysql> select password(‘test’);
    +——————————————-+
    | password(‘test’) |
    +——————————————-+
    | *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 |
    +——————————————-+
    1 row in set (0.00 sec)

    mysql> select concat(‘*’,upper(sha1(unhex(sha1(‘test’)))));
    +———————————————-+
    | concat(‘*’,upper(sha1(unhex(sha1(‘test’))))) |
    +———————————————-+
    | *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 |
    +———————————————-+
    1 row in set (0.00 sec)

    mysql> set password=’*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29′;
    Query OK, 0 rows affected (0.00 sec)

  3. Todd Farmer on said:

    Hi Baron,

    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.

  4. Todd Farmer on said:

    Hi Kolbe,

    Yes, you are correct – there are ways to circumvent this (some requiring more knowledge or privileges than others).

Leave a Reply

Your email address will not be published. Required fields are marked *


9 + nine =

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>