MySQL: How to Determine the Strength (strong or Weak) of Current User Passwords

Question: How to Determine the Strength of Existing Passwords in Order to Identify Users with Weak Passwords? Find accounts with weak passwords, so the passwords can be expired.

As MySQL uses one-way hashes for the passwords, there is no way to retrieve the original password again. Thus it is not possible to determine the strength of the password for a given hash. It is recommended to install the validate_password plugin which will validate passwords are they are created. If you know the password, you can use the VALIDATE_PASSWORD_STRENGTH() function to get the strength of the password. This requires that the validate_password plugin is installed. The function returns a value that will be one of 0, 25, 50, 75, 100 with the following meaning:

Password Test	Return Value
Length < 4	0
Length ≥ 4 and < validate_password_length	25
Satisfies policy 1 (LOW)	50
Satisfies policy 2 (MEDIUM)	75
Satisfies policy 3 (STRONG)	100

For example:

mysql> INSTALL PLUGIN validate_password SONAME 'validate_password.so';
Query OK, 0 rows affected (1.08 sec)

mysql> SELECT @@global.validate_password_length, VALIDATE_PASSWORD_STRENGTH('abc'), VALIDATE_PASSWORD_STRENGTH('qwerty'),
              VALIDATE_PASSWORD_STRENGTH('password'), VALIDATE_PASSWORD_STRENGTH('p@$$w0rd'), VALIDATE_PASSWORD_STRENGTH('vuwyup{Drel2')\G
*************************** 1. row ***************************
        @@global.validate_password_length: 8
        VALIDATE_PASSWORD_STRENGTH('abc'): 0
     VALIDATE_PASSWORD_STRENGTH('qwerty'): 25
   VALIDATE_PASSWORD_STRENGTH('password'): 50
   VALIDATE_PASSWORD_STRENGTH('p@$$w0rd'): 50
VALIDATE_PASSWORD_STRENGTH('vuwyup{Drel2'): 100
1 row in set (0.00 sec)

Some specific tests that are possible to run are:

1. Is the password the same as for the root@localhost user account?

This is possible for accounts using the mysql_native_password authentication plugin (sha1 hashes), by using the following query:

mysql> SELECT User, Host
        FROM mysql.user
       WHERE NOT (User = 'root' AND Host = 'localhost')
             AND plugin = 'mysql_native_password'
             AND authentication_string = (SELECT authentication_string
                                            FROM mysql.user
                                           WHERE User = 'root' AND Host = 'localhost');

However, the comparison is not possible when using the sha256 authentication plugin (this is recommended) as the hashes are salted. For other plugins it depends on the plugin. For example the auth_socket plugin always uses a blank password as the authentication is based on the user being a local operating system user of the same name. For PAM and LDAP authentication the password is not stored inside MySQL, so it is not possible either.

2. The password is some specific value, for example “admin”?

This is possible for authentication plugins storing the password in the mysql.user table. The exact query depends on the password authentication plugin. In short, it is necessary to implement the password hashing algorithm.

For the native password plugin (the default in 5.7 and earlier - the one using sha1 hashes) this is easy:

mysql> SELECT User, Host
        FROM mysql.user
       WHERE plugin = 'mysql_native_password'
             AND authentication_string = CONCAT('*', UPPER(SHA1(UNHEX(SHA1('admin')))));

Replace password in the inner SHA1() function with the password you want to test against. For the sha256 hashes, the algorithm is much more complex.

3. Whether the account has a blank password (no password).

This is simple:

mysql> SELECT User, Host
        FROM mysql.user
       WHERE plugin IN ('mysql_native_password', 'sha256_password')
             AND authentication_string = '';