Mysql user password validation. validate_password plugin.

Mysql user password validation. validate_password plugin.

Colleagues hello to all.

In today's article, we will talk about such a component in Mysql called validate_password. This component is designed to enhance password security, the component will verify account passwords and ensure strength password.

Mysql version I will use 8.0.29

 

 Article content:

  1. Checking if the component is installed validate_password.
  2. Descriptions of validate_password plugin variables and their assignments.
  3. Rules for changing variable values.
  4. Summary

 

1. Checking if the validate_password component is installed.

In the latest versions of Mysql DBMS, the password validation plugin is already installed, but if you have an old DBMS version, then you will have to install it manually. To install the password validation plugin, use the command:

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

But if you don't need the plugin, you can remove it. To remove the plugin, use the command:

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

 

But since I have the latest version of the DBMS, I already have this plugin installed. Let's check it out.

1.1. The first is let's find out the directory where we have all the plugins available to us. To do this, use the command:

mysql> SHOW VARIABLES LIKE 'plugin_dir';

 mysql_validate_password

As you can see from the screenshot, the  /usr/lib64/mysql/plugin/ directory will contain all the plugins that we install.

 

1.2. The second is let's go to this directory and make sure that we actually have the plugin file component_validate_password.so.

mysql_validate_password

As you can see, the  component_validate_password.so file is present in the directory. Let's move on.


 

2. Descriptions of validate_password plugin variables and their purposes.

So, colleagues, we have already made sure that we have the password validation plugin installed, and now let's look at its variables and their designations.

 

2.1. First of all, let's look at these variables. To find out which variables apply to this plugin, use the command:

mysql> SHOW VARIABLES LIKE 'validate_password.%';

mysql_validate_password

As you can see, this plugin has 7 variables.

 

2.2. Variable validate_password.check_user_name.

If the password of an account matches the account name, or vice versa, then a match occurs and the password is rejected. Password and account name values ​​are compared as binary strings byte by byte and case sensitive.

But to be honest, this variable doesn't work at all.

 

2.3. Variable validate_password.dictionary_file.

This variable uses a password check against the dictionary that you specify, but to be honest, I have never used this variable and do not advise you, as it is of little use.

 

2.4. Variable validate_password.length.

This variable controls the length of the password. By default, it has a value of 8, which means that the minimum password length must be at least 8 characters. Let's check it out.

mysql_validate_password

In the first option, I wanted to create an account with passwords that were equal to 7 characters, and I got an error. And in the second option, I already specified a password equal to 8 characters and the account was successfully created.

 

2.5. Variable validate_password.mixed_case_count.

This variable controls the minimum number of lowercase and uppercase characters. By default, it is set to 1, which means that you must have one lowercase character and one uppercase character in your password. Let's check it out.

mysql_validate_password

In the first option, I wanted to create an account with passwords that are all lowercase and got an error. In the second variant, all characters were indicated in uppercase and also received an error. And in the third option, I specified one character in lowercase and one character in uppercase and everything turned out.

 

2.6. Variable validate_password.number_count.

This variable controls the minimum number of numeric characters in your password. By default, it has 1, which means that you must have at least one digit in your password. Let's check it out.

mysql_validate_password

In the first option, I wanted to create an account for which I did not specify any numbers and got an error, and in the second option, I specified one number and I managed to create an account.

 

2.7. Variable validate_password.special_char_count.

This variable controls the minimum number of special characters such as the % sign or $. By default, it has 1, which means that you must have at least one special character in your password. Let's check it out.

mysql_validate_password

In the first option, I wanted to create an account for which I did not specify any special characters and got an error, and in the second option, I specified one special character and I managed to create an account.

 

2.8. Variable validate_password.policy.

This variable is responsible for what parameters will be taken into account when checking the password. It defaults to MEDIUM.

  • LOW - Only the length of the password will be taken into account.
  • MEDIUM - The length will be taken into account; numeric, lowercase/uppercase, and special characters in the password.
  • STRONG - Length will be taken into account; numeric, lowercase/uppercase and special characters; dictionary file.

I recommend that you leave the default values MEDIUM.


 

3. Rules for changing variable values.

You can change values ​​of variables to your own values. To do this, use the SET GLOBAL command.

mysql_validate_password

 

But after restarting the database server, all your changes that you changed will be lost. In order for all your changes to remain, you also need to add these variables to the configuration file.

mysql_validate_password

mysql_validate_password


 

4. Results.

Colleagues, today we have analyzed a cool plugin for validating account passwords. I recommend that you always use it, especially if you are a database administrator in some company.


 

Thank you all, I hope that my article helped you in some way.

SIMILAR ARTICLES

MySQL - Test Data Generation
MySQL - Test Data Generation
MYSQL
Read more
MySQL / MariaDB How to get the id of the last inserted record in a table - LAST_INSERT_ID
MySQL / MariaDB How to get the id of the last inserted record in a table - LAST_INSERT_ID
MYSQL
Read more
MySQL/MariaDB How to convert character case - UPPER and LOWER functions
MySQL/MariaDB How to convert character case - UPPER and LOWER functions
MYSQL
Read more