Are your MySQL users using ‘password’, ‘s3cr3t’, or ‘thebossisajerk’ as their passwords? Easy-to-guess passwords can be disastrous to the security of your data, but there is a way to exclude inappropriate words or phrases from being used. The first step is to compile a list of words and phrases you want to exclude, and that may be the hardest part of this process. By the way, put in project names, business addresses, toll-free phone numbers, mottos, and other common items that could be used for a password that would be easy to guess. List all these in a text file and save it as /etc/mysql/badpasswords.
Next, edit the MySQL configuration file to add the badpasswords file’s full location under the validate_password.dictionary_file entry.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | root@test1:/etc/mysql/mysql.conf.d# cat mysqld.cnf # # The Percona Server 8.0 configuration file. # # For explanations see # http://dev.mysql.com/doc/mysql/en/server-system-variables.html [mysqld] pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock datadir = /var/lib/mysql log-error = /var/log/mysql/error.log validate_password.dictionary_file = /etc/mysql/badpasswords root@test1:/etc/mysql/mysql.conf.d |
You will have to ensure that you have the component_validate_password plug-in installed on the system. Here, you have to find out where the directory with the plugins is located.
1 2 3 4 5 6 7 | mysql> select @@plugin_dir; +------------------------+ | @@plugin_dir | +------------------------+ | /usr/lib/mysql/plugin/ | +------------------------+ 1 row in set (0.01 sec) |
Install the component_validate_password plug_in.
1 2 | mysql> install component 'file://component_validate_password'; Query OK, 0 rows affected (0.05 sec) |
Check the settings
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | mysql> show variables like 'validate_password.%'; +-------------------------------------------------+--------+ | Variable_name | Value | +-------------------------------------------------+--------+ | validate_password.changed_characters_percentage | 0 | | 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 | +-------------------------------------------------+--------+ 8 rows in set (0.01 sec) |
You will want to change that validate_password_policy from MEDIUM to STRONG. Set that, too, in your config file. This setting invokes the use use of the base password file.
Restart the server and test. You may need to adjust the length, mixed_case_count, and special_character_count to meet your requirements.
And finally, you need to test. You want to see this message when you run through your list of bad passwords.
1 2 | mysql> create user 'baspass'@'localhost' identified by 'password'; ERROR 1819 (HY000): Your password does not satisfy the current policy requirements |
Do you need to test all the passwords from your bad password test? A spot check of a percentage of your list should suffice. Your security mavens will tsk-tsk at spot checks and insist on a full check.
And, be sure to update your bad password file for new project names, catchphrases, and other things you do not want to have used as a password.
Percona Distribution for MySQL is the most complete, stable, scalable, and secure open source MySQL solution available, delivering enterprise-grade database environments for your most critical business applications… and it’s free to use!
Try Percona Distribution for MySQL today!
