How can I improve the security of my Amazon RDS MySQL DB instance using the validate_password plugin?

Short description

MySQL provides a validate_password plugin that you can use to improve the security of an RDS MySQL DB instance. The plugin enforces password policies by using parameters in the DB parameter group for your DB instance. The plugin is supported for DB instances running MySQL versions 5.6, 5.7, and 8.0.

Note: The validate_password plugin isn't a part of the default MySQL configuration. Instead, it exists as a separate plugin. When Amazon RDS creates a MySQL DB instance, the plugin isn't installed by default.

Resolution

Enable validate_password plugin for RDS MySQL DB instance

Connect to the RDS MySQL DB instance using master user, and run the following command:

MySQL [(none)]> INSTALL PLUGIN validate_password SONAME 'validate_password.so';

This installs the validate_password plugin. It then runs the plugin with the default parameter values.

Verify that validate_password plugin is installed and active on the RDS MySQL DB instance

Run the following query on your DB instance to check the status of the validate_password plugin:

MySQL [(none)]> SELECT plugin_name, plugin_status, 
plugin_type, plugin_library FROM information_schema.plugins WHERE 
plugin_name='validate_password';

    +-------------------+---------------+-------------------+----------------------+
    | plugin_name       | plugin_status | plugin_type       | plugin_library       |
    +-------------------+---------------+-------------------+----------------------+
    | validate_password | ACTIVE        | VALIDATE PASSWORD | validate_password.so |
    +-------------------+---------------+-------------------+----------------------+

Check default values for the validate_password plugin

Check the default parameter values for the plugin by running the following query:

MySQL [(none)]> SHOW GLOBAL VARIABLES LIKE 'validate_password%';

Below are the descriptions of each parameter:

You can configure these parameters in the custom DB parameter group used by your DB instance, except for validate_password_dictionary_file and validate_password_check_user_name.

Note: If your DB instance is using the default parameter group, you must create a new parameter group, and then attach it to the DB instance. This is because you can't modify the parameter settings of a default parameter group. For more information, see Working with DB parameter groups.

Note: Amazon RDS doesn't validate passwords. If you set a user password with the AWS Management Console, the modify-db-instance AWS Command Line Interface (AWS CLI) command, or the ModifyDBInstance RDS API operation, the change can succeed even if the new password doesn't satisfy your password policies.

Reset existing passwords and create a policy compliant password

After installing and enabling the password_validate plugin, reset your existing passwords to comply with your new validation policies.

First, test the password_validate plugin installed on your DB instance. You can do this by using the default plugin parameters listed above to create a new DB user:

MySQL [(none)]> CREATE USER 'USER123'@'%' identified by 'password';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

Because validate_password_policy is set to MEDIUM, be sure that the password satisfies the criteria described in the MySQL validate_password_policy documentation. Because the above CREATE USER command doesn't satisfy the password policy, it failed with the error "Your password does not satisfy the current policy requirements".

Create a user with a password that satisfies the password policy by running the following command:

MySQL [(none)]> CREATE USER 'USER123'@'%' identified by 'Password@57';
Query OK, 0 rows affected (0.01 sec)

Run the following command to verify that you created the user successfully:

MySQL [(none)]> SELECT user, host FROM mysql.user WHERE ( user='USER123' AND host='%' );
+-------------------+------+
| user              | host |
+-------------------+------+
| validate_password | %    |
+-------------------+------+
1 row in set (0.00 sec)

To change the password of the existing user, run the following command with a policy compliant password, as shown below:

5.6:
mysql> SET PASSWORD FOR 'USER123'@'%' = PASSWORD('Password@2020');
Query OK, 0 rows affected (0.01 sec)

5.7 and 8.0:
mysql> alter user 'USER123'@'%' identified by 'Password@2020';
Query OK, 0 rows affected (0.01 sec)

To learn more about resetting passwords for an existing user, see How to reset the root password in the MySQL documentation.

Disable validate_password plugin for RDS MySQL DB instance

To disable the validate_password plugin from your DB instance, run the following command using the master user:

MySQL [(none)]> UNINSTALL PLUGIN validate_password;

Related information

Using the password validation plugin

The password validation plugin in the MySQL documentation