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 tables, procedures and events used to implement a password policy which checks password expiration and reuse below. Continue reading Implementing a password policy in 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.

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 used will prevent attackers from getting passwords stored in the .mylogin.cnf file if they have read access to it – it absolutely will not.  There are multiple ways to extract the password out of this file, some more complex than others.  Don’t make the mistake of believing that the password storage is safe just because the contents are encrypted.

So, if this enhancement isn’t about secure storing of passwords, what is it about?  It makes secure access via MySQL client applications easier to use.  For example, it helps eliminate the need to include a password as an argument to mysql or other command-line clients:

shell>  mysql -hproduction -uroot -pmypass

becomes

shell> mysql --login-path=production

(assuming we used mysql_config_editor to set up a “production” configuration)

That’s obviously more secure if somebody can view the running processes – you don’t want to expose your password like that.   Of course, you could always just include the -p argument and enter the password interactively (that doesn’t echo it to the screen or display it in any process list.  But that doesn’t help with scripts or batch jobs where interactive password entry isn’t an option.  So, great – this enhancement helps eliminate the need for including the password where it can’t be entered interactively.  Sounds great – but this mechanism has long existed in MySQL through the use of configuration files (and you can have as many different configuration files as you want dedicated to various connections).  You can easily put the password under the [client] header, and invoke clients like so:

shell> mysql --defaults-file=~/production.cnf

Again, assuming there’s ~/production.cnf file that has the connection attributes needed defined in the [client] section.

So, if it’s already possible to eliminate passwords for both interactive and batch use cases, why is this needed?  The most immediate answer is that this makes it easier to get password storage – when you do it – right.  In the above example with the production.cnf file, you have to create that file yourself and ensure that it has appropriate privileges assigned.  That’s not the case when you use mysql_config_utility.  Quoting from the manual:

The login file must be readable and writable to the current user, and inaccessible to other users. Otherwise, mysql_config_editor ignores it, and the file is not used by client programs, either.

So one of the principal benefits of mysql_config_editor is ease-of-use – you can rely on it to store your password in a file that is not readable by other users.  That’s not to say that you could not have done exactly the same thing yourself by setting appropriate file permissions on a .cnf file of your own making, but using the tool eliminates the possibility that it gets overlooked.  Note that I believe that this aspect of the feature may not be working properly for Windows at this stage, and I’m in the process of validating and reporting a bug on this.

Now you may be wondering:  If the file encryption doesn’t protect against attackers having file system access, what does it protect against?  It eliminates a password storage location that might be misused by a user in a fit of urgency.  If a user can’t get access (their own) plain-text passwords from .mylogin.cnf, it may throw just one more hurdle preventing passwords from being copied and pasted into other files (which would have the same need for file system restrictions) or command-line parameters.  In short, it reduces the possibility that users may unintentionally use their own passwords in an insecure manner.

All that said, the feature does have some nice ease-of-use aspects that aren’t explicitly security-related.  For example, you don’t even have to store passwords using the utility, and use it as a collection of all your hard-to-remember server locations, and still enter the password interactively:

shell> bin\mysql_config_editor print --all
[slave1]
host = some.really.obscure.slave.host
[slave2]
host = another.really.obscure.slave.host

shell> bin\mysql --login-path=slave1 --user=something -p
Enter password: ****

The options you specify as client application arguments will overwrite whatever is stored in .mylogin.pass:

shell> bin\mysql --login-path=slave1 --host=localhost --user=root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5

So, if you do choose to store passwords in your .mylogin.cnf file, and you use the same password for multiple accounts (look at me advocating security), you can have a single entry that remembers your password, and choose the host as an argument to the client.

This can be a useful enhancement for very specific security requirements and helps make it easier to be more secure in handling passwords as well as connections to multiple servers.  At the same time, it should not be confused with a mechanism that prevents attackers from acquiring passwords if they have file-system level access.

As stated in the 5.6.6 release notes, the default value of –bind-address has changed from IPv4-specific “0.0.0.0” to “*” – the latter value which allows MySQL to bind to IPv6 interfaces by default.  There are a few implications to this change.

First, make sure you’ve hardened both your IPv4 and your IPv6 access points to your MySQL server.  If you are running IPv4 interfaces only, you can also change the –bind-address value back to the previous default value of “0.0.0.0”.  Because MySQL now listens by default on both IPv4 and IPv6 interfaces, an installation that has only hardened IPv4 interfaces may find unaddressed vulnerabilities exposed via the IPv6 interface that is now used by default.

Second, the change in defaults can have an impact on startup speed for deployments where there is no IPv6 support.  On my Windows XP laptop, startup stalls for 45 seconds while MySQL lets Windows determine that IPv6 is not supported:

120813 10:17:16 [Note] Server hostname (bind-address): ‘*’; port: 3307
120813 10:18:01 [Note] IPv6 is not available.
120813 10:18:01 [Note]   – ‘0.0.0.0’ resolves to ‘0.0.0.0’;
120813 10:18:01 [Note] Server socket created on IP: ‘0.0.0.0’.

Why Windows takes 45 seconds to figure this out is anybody’s guess, but explicitly setting –bind-address to 0.0.0.0 eliminates this behavior, and server start-up speed is restored:

120813 10:27:39 [Note] Server hostname (bind-address): ‘0.0.0.0’; port: 3307
120813 10:27:39 [Note]   – ‘0.0.0.0’ resolves to ‘0.0.0.0’;
120813 10:27:39 [Note] Server socket created on IP: ‘0.0.0.0’.

Another option is to make sure your OS has IPv6 support enabled (Microsoft documents this procedure for XP in KB article 2478747).

Whether you have IPv6 support enabled or not, make sure you consider how the change in behavior introduced in 5.6.6 will affect you.