Expiring MySQL Passwords and Setting Password Strength


MySQL 5.6 introduced the ability to expire passwords. Many work environments have rules where it is mandatory to change passwords on a regular basis. It is easy to expire a single account with a ALTER USER 'dave'@'localhost' PASSWWORD EXPIRE; command.

mysql.user tabe

The mysql.user table

The mysql.user table now has a PASSWORD_EXPIRED column. A user attempting to login with an expired account using a client that supports, they will be prompted to change their password.

Forced to change password

An example of being forced into sandbox mode and changing the password. Note that user does not have SUPER or other admin level privs to change passwords.

SET PASSWORD does not check to see if you are reusing your old password.

You will not want to expire passwords for accounts used in applications automatically. It would be possible to write an application smart enough to handle sandbox mode and implement an acceptable password but my opinion (and probably mine alone) is that would be less hassle to plan manual updates on a regular basis.

There is a password validation plug-in plugin that lets you tune password complexity and provides for a password dictionary (a black list of words NOT to use).

An example of a MEDIUM strength password setting where one upper case, one lower case, one numeric,  and one special character are in the pass phase (of a settable length).

An example of a MEDIUM strength password setting where one upper case, one lower case, one numeric, and one special character are in the pass phase (of a settable length).

There are three levels of password checking — LOW, MEDIUM, and STRONG with MEDIUM being the default. Change the value of validate_password_policy as required. These represent increasingly strict password tests. The following descriptions refer to default parameter values; these can be modified by changing the appropriate system variables.

LOW policy tests password length only and it must be at least 8 characters long.

MEDIUM policy adds to LOW with the additional conditions that passwords must contain at least 1 numeric character, 1 lowercase and uppercase character, and 1 special (nonalphanumeric) character.

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

So what do you do if corporate rules require that interactive passwords are changed every XX days? Well, come back to the next entry of this blog.

About these ads

2 Comments

Filed under Administration, DBA Tools, MySQL

2 responses to “Expiring MySQL Passwords and Setting Password Strength

  1. Pingback: More MySQL Password Expiration | Open Source DBA's Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s