I previously published stored programs to help implement a (more) comprehensive password policy in MySQL 5.6, building on the password complexity plugin now available in MySQL 5.6.  This proof-of-concept has been expanded recently, and the updated package is available here.  There’s a few notable changes to the earlier version:

Moved all created objects out of mysql system database

The mysql database is meant for system tables, and I try to keep everything not directly managed by the MySQL server out of that database.  The initial proof-of-concept implementation violated this principal – the update corrects this by creating and using a new password_policy database …

I’ve been looking at the new ALTER USER … PASSWORD EXPIRE command as I try to implement a comprehensive password policy for MySQL 5.6.  There’s a few aspects of this feature that I found interesting, and thought others might benefit from what I’ve learned.  Here’s a quick summary:

You can use ALTER USER … PASSWORD EXPIRE in prepared statements as of 5.6.8-rc

This is important because there’s no other way to dynamically bind ALTER USER statements to a user name and host, which is necessary if you are trying to automate anything related to password policies.  This wasn’t the case with earlier 5.6 releases, but was fixed in 5.6.8-rc:

mysql> SELECT password_expired
-> FROM mysql.user
-> WHERE user = 'root' AND host = 'localhost';
+------------------+
| password_expired |
+------------------+
| N                |
+------------------+
1 row in set (0.00 sec)

mysql> SET @sql = 'ALTER USER …

I’ve been working on revising my password policy scripts, and in the process, thought about the privileges required.  My first draft added tables to the mysql system database and leveraged the root@localhost account.  I’m looking to lock that down for the next iteration.  It’s easy to move the tables and procedures out of the mysql system database into a new password_policy database, but what to do about the use of the root account?

Ideally we would use an account that has the minimum privileges necessary to successfully execute the stored procedures.  But these aren’t trivial permissions:

• SELECT from mysql.user table
• CREATE USER to support use of ALTER USER … PASSWORD EXPIRE
• EXECUTE for called stored …

As Todd Farmer points out in Understanding mysql_config_editors security aspects, the new .mylogin.cnf file generated by mysql_config_editor does not securely store the password used to login to the database. It just obfuscates it.

The format of the file is as follows (as of MySQL 5.6.7-RC):

• 4 Bytes Zero (Version Information)
• 20 Bytes Key Generation Matter
• Repeated:
  • 4 Bytes Length information
  • Length bytes crypted matter. The crypt is done using the AES ENCRYPT function, which in itself is insecure: It is an aes-128-ecb with a NULL IV.
  …

In a previous post, I noted that the new new password verification plugin in MySQL 5.6 provides a basis for implementing a more comprehensive password policy.  Most notably, password policies include requirements around password strength, duration, and reuse.  While the password validation plugin focuses on password strength policy components, there are ways to roll your own processes in support of password expiration and reuse policy components.  Unlike the password verification plugin, the tools I will describe below don’t hook directly into account maintenance commands.

You can download the full .SQL file (in a .ZIP package) here, and I’ll walk through the various …

Managing Multiple MySQL Servers From One phpMyAdmin Installation (Using SSL Encryption)

This tutorial explains how you can manage multiple MySQL servers from one phpMyAdmin installation. For security reasons, communication between phpMyAdmin and any remote MySQL server is using SSL encryption (this is not necessary for a local MySQL server since communication between phpMyAdmin and MySQL is not leaving the server). phpMyAdmin is a free software tool written in PHP, intended to handle the administration of MySQL over the World Wide Web. phpMyAdmin supports a wide range of operations with MySQL.

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 …

At the end of September, the MySQL Connect 2012 conference will be held as part of Oracle OpenWorld in San Francisco. MySQL Connect is a two day event that allows attendees to focus on MySQL at a technical depth with presentations and interaction with many of the MySQL developers, engineers and other knowledgeable staff. There is also a range a international speakers to give broader knowledge to the presentations.

I am presenting a Hands-On Lab on Sunday 30th September 16:15 - 17:15 entitled HOL10474 - MySQL Security: Authentication and Auditing. The sessions goes through an introduction to the plugin API and how it can help expand the capabilities of MySQL. Since it is a hands-on lab, …

The recent release of 5.6.6 includes a new utility, mysql_config_editor, which makes it easier to interact with MySQL clients securely.  At the same time, it’s easy to overstate the security benefits of using this new tool, and unfortunately, I think a couple of statements in the release notes and documentation go a step too far (something we’re in the process of correcting).  Ronald quoted one of them in his blog:

MySQL now provides a method for storing authentication credentials securely in an option file named .mylogin.cnf.

This enhancement really isn’t about securing passwords at a file-system level.  Don’t assume that the encryption …

In case you missed it, MySQL 5.6.6, also known as Milestone 9, was recently released. I have yet to install this, however just one part of the MySQL 5.6.6 Release Notes makes placing installing and testing high on my TODO list.

Updated 20 Sep, 2012. Be sure to also read Todd’s post Understanding mysql_config_editor’s security aspects about a more in-depth and accurate description of this new feature. In summary, “It makes secure access via MySQL client applications easier to use”.

That is the reported improvements in password management. From the release notes:

Security Improvements

These security improvements were implemented:

MySQL now provides a method for …