Improved password policy utility for MySQL 5.6

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 instead.

Change DEFINER to new pwd_policy@localhost user account

Applying another best-practices principle, I’ve also created a new user account with the minimum privileges required to successfully implement the password policy (instead of using root@localhost).  Thinking about this was what prompted me to create the system user authentication plugin, and I’ve used it here.  This helps prevent the need to issue and secure a password for an account that’s never meant to authenticate as a client.  If you don’t want to use the plugin, simply change the CREATE USER statement to fit your needs.

Add support to expire user password on violation

If you want to take password policy a step further than reporting and actually expire passwords for violations, you can do so now that 5.6.8-rc adds support for ALTER USER in prepared statements.

Table-based configuration

While the initial version had some configuration options (like the ability to set the password expiration duration), they were defined in the stored routines themselves – so one had to drop and recreate the stored routine to change the behavior.  The new version retains default behavior, but allows it to be overridden via a configuration table:

CREATE TABLE password_policy.password_policy_configuration (
config_key VARCHAR(50) NOT NULL PRIMARY KEY,
config_value INT NOT NULL DEFAULT 0
);

The implemented configuration options are:

  • password_expiration – The maximum number of days since the last recorded password change allowed before an account is in violation of policy.  Default behavior is 90 days.
  • password_reuse_threshold – The number of previous passwords that should be checked to ensure passwords are not reused.  The default behavior is checking the 10 most recent passwords.
  • check_password_expiration – Should passwords be checked for expiration, globally.  Setting this to zero means passwords are not checked for expiration.  A value of one means they should be checked and violations reported.  A value of two triggers the violation to be reported and the password expired.
  • check_password_reuse – Should passwords be checked for reuse, globally.  Setting this to zero means passwords are not checked for reuse.  A value of one means they should be checked and violations reported.  A value of two triggers the violation to be reported and the password expired.
  • check_no_password – Should accounts be checked for missing passwords.  Setting this to zero means checking for blank passwords is skipped.  A value of one means they should be checked and violations reported.  A value of two triggers the violation to be reported and the password expired.

These configuration options control the behavior of the password policy stored procedures globally.

 Account-specific exceptions and blacklists

When there are exceptions to global password policies, you can override behavior by configuring account-specific behavior.  This is controlled by the following table:

CREATE TABLE password_policy.password_policy_user_list (
user CHAR(16) COLLATE utf8_bin NOT NULL DEFAULT '',
host CHAR(60) COLLATE utf8_bin NOT NULL DEFAULT '',
check_expiration ENUM('REPORT', 'EXPIRE', 'IGNORE') DEFAULT 'REPORT',
check_no_password ENUM('REPORT', 'EXPIRE', 'IGNORE') DEFAULT 'REPORT',
check_reused_password ENUM('REPORT', 'EXPIRE', 'IGNORE') DEFAULT 'REPORT',
PRIMARY KEY (user, host)
);

As seen above, the default behavior is to report when there are violations, rather than expire the password.  This prevents accounts from being expired unless explicitly configured to do so.  Note that the actual behavior is limited by the maximum of both the account-specific and global configuration; if the account is configured with EXPIRE but the global setting is to report only (“1”), the account will be reported (but not expired) if found to be in violation.  Because the default is REPORT for all, newly-created accounts will get reported, but not expired.

Added support for external reporting

The password_policy.user_password_policy_violation table now has a violation_resolved column, allowing violations to be marked as resolved (non-zero value).  Unresolved violations prevent further action for violation of the same policy, while marking the violation as resolved causes the account to be checked for violation of the relevant policy again.

This also is an enabler for MySQL Enterprise Monitor, for which I’ve written some custom data collections and rules to push report of violations via email or SNMP.   I’ll cover the MEM integration in a subsequent post.

Notes

The package has three .SQL files.  The account_policies_grants.sql file creates the user account and grants the necessary privileges for required for the stored procedures to execute successfully – execute this first.  The second file, account_policies.sql, contains the actual table and stored procedure definitions.  I’ve included two more files -account_policies_clean.sql for removing tables and stored programs and account_policies_test.sql for my testing (not exactly complete).

I hope these tools prove useful.  If you have other password policy needs that you would like to see incorporated into these tools, just leave a comment.

4 thoughts on “Improved password policy utility for MySQL 5.6

  1. oh! this article is so useful !!!

    But, a little problem, how can we make the new users to change their passwords at the first time login?

    I come from china. My English is poor…. Waiting your feedback!

    1. Glad to hear this was useful, Jack! Creation of a new user account and setting their password to require change on next login can be done, but they are two separate steps:

      CREATE USER newuser@somehost IDENTIFIED BY ‘ch4ng3M3!’;
      ALTER USER newuser@somehost PASSWORD EXPIRE;

      I’ll look at ways to incorporate initial user account provisioning into the password utility.

  2. Thank you very much for your quick response!
    I will test the steps.
    little confuse..
    when the account’s password_expired status is “Y” , how can he/she to log in and to set the new password by them-self?

    in my guess, when the account is expired. the user can not log in and can not to set new password anymore….

    1. Hi Jack,

      Actually, the ALTER USER … PASSWORD EXPIRE causes the user to be restricted from performing any operations after successfully logging in (using the “expired” password) until they issue a SET PASSWORD statement to change their password. This post will help explain better how this new MySQL 5.6 feature works.

Leave a Reply

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

Time limit is exhausted. Please reload CAPTCHA.