How to Check Password Strength in MySQL

“Passwords are the most basic form of authentication that is widely adopted by a large array of applications and services. It is, therefore, an essential feature to ensure that passwords used by your users are solid and secure.

Hence, one requirement is testing the password strength before hashing and storing it in the database. This is because MySQL stores passwords in one-way hashes, i.e., once a password is hashed, you cannot reverse it.

Luckily, MySQL provides us with a plugin that allows us to check password strength before hashing them. Although this plugin is suitable in small and testing use cases, it is not very applicable in large-scale applications. Implementing password checking on the client side is best before sending it to the database.

Join us in this journey as we explore the process of setting up and configuring simple password strength checks in MySQL.”

MySQL Valida_Password Plugin

As you guessed, the validate_password plugin implements password strength checking in MySQL. This plugin is supported in MySQL version 5.7.

Before using it, it is a good measure to ensure the plugin is installed and activated.

Enable MySQL Validate_plugin

To enable the validate_password plugin, edit the MySQL configuration file and add the entry shown below under the [mysqld] section.

[mysqld]
plugin-load-add=validate_password.so

Save the configuration and reload the server.

You can also include the plugin during runtime by using the INSTALL command.

INSTALL PLUGIN validate_password SONAME 'validate_password.so';

NOTE: In Windows, replace the validate_password.so file with validate_password.dll.

Enable validate_password Component

In MySQL version 8.0 and above, the validate_plugin is deprecated and requires you to use the validate_password component instead.

Run the command:

INSTALL COMPONENT 'file://component_validate_password';

The command output:

MySQL Password Strength Scale

The following table shows the password length and the assigned strength in MySQL.

Length	Strength
< 4	0
>= 4 and < validate_password_length	25
Meets Password Policy 1	50
Meets Password Policy 2	75
Meets Password Policy 3	100

NOTE: You can view the value of the validate_password_length variable with the statement:

show variables like 'validate_password_length';

Output:

|Variable_name           |Value|
|------------------------|-----|
|validate_password.length|8    |
|validate_password_length|8    |

MySQL Check Password Strength

Checking password string in MySQL, use the VALIDATE_PASSWORD_STRENGTH() function. The function takes the password (string) as the parameter and returns the strength based on the MySQL password scale.

The statement syntax is as shown:

SELECT VALIDATE_PASSWORD_STRENGTH(‘str’);

For example:

select validate_password_strength('insto');
select validate_password_strength('IvEYDrat');
select validate_password_strength('lvnDYPW%xP28E5%vt*AOROkg');

The statements above use the validate_password_strength function to check the strengths of three password types.

The result:

|validate_password_strength('insto')|
|-----------------------------------|
|25 |

Second:

|validate_password_strength('IvEYDrat')|
|--------------------------------------|
|50 |

Third:

|validate_password_strength('lvnDYPW%xP28E5%vt*AOROkg')|
|------------------------------------------------------|
|100 |

Termination

In this article, you learned how to use the MySQL validate_password plugin to check if a given password meets the set password strength.

Thanks for reading!