MySQL 8.0 and user password management

I already covered some information related to users password recently (see this post and this one).

Today, let’s have a look at the password management features available in MySQL 8.0.

We will cover the password management in MySQL in 5 sections:

• validation
• expiration
• reuse policy
• verification policy
• dual active passwords

Validation

Password validation means that a new password must comply with some policy to be sure the password is not weak. This job is handled by a component in MySQL 8.0: validate_password.

To be able to use that component, you need to install it. You can verify if it’s installed by checking the mysql.component table (that information will be soon available via Information_Schema too):

 mysql> select * from mysql.component;
 Empty set (0.02 sec

As it’s not yet installed, it’s very simple to install it:

 mysql> INSTALL COMPONENT 'file://component_validate_password';
 Query OK, 0 rows affected (0.12 sec)
 mysql> select * from mysql.component;
 +--------------+--------------------+------------------------------------+
 | component_id | component_group_id | component_urn                      |
 +--------------+--------------------+------------------------------------+
 |            1 |                  1 | file://component_validate_password |
 +--------------+--------------------+------------------------------------+
 1 row in set (0.01 sec)

Once installed, you have access to new validation password variables:

 mysql> SHOW VARIABLES LIKE 'validate_password.%';
 +--------------------------------------+--------+
 | Variable_name                        | Value  |
 +--------------------------------------+--------+
 | validate_password.check_user_name    | ON     |
 | validate_password.dictionary_file    |        |
 | validate_password.length             | 8      |
 | validate_password.mixed_case_count   | 1      |
 | validate_password.number_count       | 1      |
 | validate_password.policy             | MEDIUM |
 | validate_password.special_char_count | 1      |
 +--------------------------------------+--------+
 7 rows in set (0.44 sec)

As you can see, it’s possible to have several options defined, please check the manual for more details.

Let’s test the default options:

 mysql> create user 'lefred' identified by 'lefred';
 ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
 mysql> create user 'lefred' identified by 'lefreD1#';
 Query OK, 0 rows affected (0.02 sec)

Any character is supported, accents for example:

 mysql> set password for 'lefred'='Lefréd#1';
 Query OK, 0 rows affected (0.17 sec)

Or even better (or at least more funny than the Azerty vs Qwerty keyboard fight when login on the server console 😜):

Expiration

It’s possible for the MySQL administrators to expire account passwords. This can be set by default or per user account.

Let’s make our last account created to expire its password in 10 days:

 mysql> ALTER USER 'lefred' PASSWORD EXPIRE INTERVAL 90 DAY;
 Query OK, 0 rows affected (0.04 sec

Let’s verify with the query from this post:

 mysql> select user, password_last_changed,
     -> concat(
     -> cast(
     ->  IFNULL(password_lifetime, @@default_password_lifetime) as signed)
     ->  + cast(datediff(password_last_changed, now()) as signed), " days") expires_in
     -> from mysql.user
     -> where 
     -> cast(
     ->  IFNULL(password_lifetime, @@default_password_lifetime) as signed)
     ->  + cast(datediff(password_last_changed, now()) as signed) >= 0 
     ->  and user not like 'mysql.%';
 +--------+-----------------------+------------+
 | user   | password_last_changed | expires_in |
 +--------+-----------------------+------------+
 | lefred | 2019-01-11 15:15:31   | 90 days    |
 | root   | 2019-01-11 14:21:33   | 0 days     |
 +--------+-----------------------+------------+
 2 rows in set (0.03 sec)

Reuse Policy

MySQL administrators have also the possibility to prevent an user from reusing his old password(s).

This policy can be global or per account (see the manual).

The DBA can decide the amount of password changes before reuse (PASSWORD HISTORY). Or the minimum delay before permitting reuse (PASSWORD REUSE INTERVAL). It is also possible to mix both.

 mysql> ALTER USER 'lefred' PASSWORD HISTORY 2;
 Query OK, 0 rows affected (0.00 sec)
 mysql> set password for 'lefred'='Lefréd#1';
 ERROR 3638 (HY000): Cannot use these credentials for 'lefred@%' because they contradict 
the password history polic

Verification Policy

Since MySQL 8.0.13, it is possible to require that attempts to change an account password be verified by specifying the current password to be replaced.

This setting is managed by the keywords PASSWORD REQUIRE (see manual).

It allows the following values:

• PASSWORD REQUIRE CURRENT: password must be specified at change password attempt
• PASSWORD REQUIRE CURRENT OPTIONAL: password may be specified at change password attempt
• PASSWORD REQUIRE CURRENT DEFAULT: it follows the value of the system variable password_require_current

Let’s try it:

 mysql> ALTER user 'lefred' PASSWORD REQUIRE CURRENT;
 Query OK, 0 rows affected (0.00 sec)

Now we can connect with the user lefred and try to change the password:

 mysql> set password='Lefred#2';
 ERROR 13226 (HY000): Current password needs to be specified in the REPLACE clause in order 
to change it.
 mysql> set password='Lefred#2' replace 'Lefred#1';
 Query OK, 0 rows affected (0.02 sec)

As you could see, the replacekeyword was required.

Dual Active Passwords

In MySQL 8.0.14 (coming soon) user accounts are permitted to have dual passwords, designated as primary and secondary passwords. Dual-password capability makes it possible to perform credentials changes smoothly in production. This was a request from Booking.com (thank you for the feature request).

The change can be done in two steps:

• change the password specifying to retain the current password
• discard the old password when not needed anymore

Let’s illustrate this:

 mysql> alter user 'lefred' identified by 'Lefred#3' retain current password;
 Query OK, 0 rows affected (0.03 sec)

Let’s try to connect again with the old password and the new one:

 $ mysql -ulefred -pLefred#2 -e 'select now()';
 mysql: [Warning] Using a password on the command line interface can be insecure.
 +---------------------+
 | now()               |
 +---------------------+
 | 2019-01-11 21:57:34 |
 +---------------------+
 $ mysql -ulefred -pLefred#3 -e 'select now()';
 mysql: [Warning] Using a password on the command line interface can be insecure.
 +---------------------+
 | now()               |
 +---------------------+
 | 2019-01-11 21:57:40 |
 +---------------------+

Great !! It works as expected.

We can see in the mysql.user table if a user has an old password active by checking the value of User_attributes:

 mysql> select User_attributes from mysql.user where user='lefred'\G
 * 1. row *
 User_attributes: {"additional_password": 
"$A$005$\f>v1,@=g{u\u0019_%!4G\nYTzinrMiGWldNLrJVqI.7XTfUD5tOfw/s8EMLu8WokukD"}
 1 row in set (0.00 sec)

Now it’s time to discard the old password:

 mysql> ALTER USER 'lefred' DISCARD OLD PASSWORD;
 Query OK, 0 rows affected (0.04 sec)

Now, it’s not possible to connect with the old password anymore:

 $ mysql -ulefred -pLefred#2 -e 'select now()';
 ERROR 1045 (28000): Access denied for user 'lefred'@'localhost' (using password: YES)

Locking Account

And finally, don’t forget that it’s always possible to Lock or Unlock an account:

 mysql> ALTER USER 'lefred' ACCOUNT LOCK;
 Query OK, 0 rows affected (0.01 sec)

And if you try to connect again with a locked account, you will see the following error:

 $ mysql -ulefred -pLefred#3 
 ERROR 3118 (HY000): Access denied for user 'lefred'@'localhost'. Account is locked.

I hope this post gives you a better overview of what is possible to manage account passwords in MySQL 8.0.