Properly planned MySQL Password Management is the first step to protect your database

---

We at MinervaDB provide MySQL and  MariaDB Database Security Audit (Please read about our Security Practice at MinervaDB Consulting Page) for our customers globally, We don’t go easy with password management .. Weaker passwords are serious security threats and we highly recommend stronger passwords. Technically Password Validation is about the policies that every new password must comply and this task is taken care by validate_password component in MySQL, You have several system variables to configure validate_password component and status variables to monitor

To use validate_password component you must install it first:

Verify if validate_password component is installed:

mysql> select * from mysql.component;

Empty set (0.00 sec)

We have successfully verified validate_password component is not installed 👆

Install validate_password component next and confirm:

mysql> INSTALL COMPONENT 'file://component_validate_password';

Query OK, 0 rows affected (0.01 sec)

mysql> select * from mysql.component;

+--------------+--------------------+------------------------------------+

| component_id | component_group_id | component_urn                      |

+--------------+--------------------+------------------------------------+

|            4 |                  4 | file://component_validate_password |

+--------------+--------------------+------------------------------------+

1 row in set (0.00 sec)

Validate_component system 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.06 sec)

Validate_component system variables are explained very well in MySQL documentation here – https://dev.mysql.com/doc/refman/8.0/en/validate-password-options-variables.html

Password Validation Component Status Variables

Once validate_password component is enabled, We will have access to MySQL status variables that provide operational information:

mysql> SHOW STATUS LIKE 'validate_password.%';

+-----------------------------------------------+---------------------+

| Variable_name                                 | Value               |

+-----------------------------------------------+---------------------+

| validate_password.dictionary_file_last_parsed | 2019-09-21 01:45:41 |

| validate_password.dictionary_file_words_count | 0                   |

+-----------------------------------------------+---------------------+

2 rows in set (0.02 sec)

Validate_component in action

Simple and easy passwords are not accepted any loner, Let’s test that now:

mysql> create user 'shiv'@'localhost' identified by 'minervadb'; 

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

Works when we set much better password:

mysql> create user 'shiv'@'localhost' identified by 'Ind@19#47';

Query OK, 0 rows affected (0.02 sec)

mysql>

We will make it even better with password expiry management, i.e. user (shiv) should be changing password every 30 days:

mysql> alter user 'shiv'@'localhost' PASSWORD EXPIRE INTERVAL 30 DAY;

Query OK, 0 rows affected (0.01 sec)

Let’s confirm the password expiry of 30 days configured  for user “shiv”:

mysql> select password_last_changed, password_lifetime from mysql.user where user='shiv';

+-----------------------+-------------------+

| password_last_changed | password_lifetime |

+-----------------------+-------------------+

| 2019-09-21 15:46:11   |                30 |

+-----------------------+-------------------+

1 row in set (0.01 sec)

Dual Active passwords (Thank you Booking.com for this feature request)

We can configure MySQL 8.0 (since8.0.14) with Primary and Secondary Passwords (dual password mechanism), This feature is really helpful in production when changing the password, We have explained it below:

mysql> select version();

+-----------+

| version() |

+-----------+

| 8.0.17    |

+-----------+

1 row in set (0.01 sec)

mysql> alter user 'shiv'@'localhost' identified by 'India#19@47' retain current password;

Query OK, 0 rows affected (0.02 sec)

mysql>

You can discard old password of the user “shiv” when appropriate, Let’s do that next:

mysql> ALTER USER 'shiv'@'localhost' DISCARD OLD PASSWORD;

Query OK, 0 rows affected (0.00 sec)

Conclusion

MySQL 8.0 password management is very interesting, Much better and secured database infrastructure operations is made possible without much complications, We have already customers on this feature, At WebScale the database security is very important to protect the best interests of every stakeholder., Hope you enjoyed reading this blog, Thank you !