Detect passwordless Accounts

[isotopp]

Cadfael should detect accounts with no password.

In MySQL 8.0

mysql> create user cookie@"%";
mysql> select user, host, plugin, authentication_string from user where authentication_string = "";
...
| keks             | %         | caching_sha2_password |                                                                        |
...

• Cadfael should flag accounts with an empty or NULL authentication_string field.

In MySQL 5.7

mysql [localhost:5734] {msandbox} (mysql) > select user, host, plugin, authentication_string from user where authentication_string = "";
+------+------+-----------------------+-----------------------+
| user | host | plugin                | authentication_string |
+------+------+-----------------------+-----------------------+
| keks | %    | mysql_native_password |                       |
+------+------+-----------------------+-----------------------+
1 row in set (0.00 sec)

• Cadfael should flag account with an empty or NULL authentication_string field.

In MySQL 5.6

mysql [localhost:5644] {msandbox} (mysql) > select user, host, password, plugin, authentication_string from user where user = "keks";
+------+------+----------+-----------------------+-----------------------+
| user | host | password | plugin                | authentication_string |
+------+------+----------+-----------------------+-----------------------+
| keks | %    |          | mysql_native_password |                       |
+------+------+----------+-----------------------+-----------------------+
1 row in set (0.00 sec)

• Cadfael should flag accounts with an empty Password field, if the plugin field has the value mysql_native_password.

In MySQL 5.5

mysql [localhost:5562] {msandbox} (mysql) > select user, host, password, plugin, authentication_string from user where user = "keks";
+------+------+----------+--------+-----------------------+
| user | host | password | plugin | authentication_string |
+------+------+----------+--------+-----------------------+
| keks | %    |          |        | NULL                  |
+------+------+----------+--------+-----------------------+
1 row in set (0.00 sec)

• Cadfael should flag accounts with an empty Password field, if the plugin field is NULL or empty.

[isotopp]

MySQL 5.5

As of MySQL 5.5.7, the mysql.user table has plugin and authentication_string columns for storing authentication plugin information.

If the plugin column for an account row is empty, the server uses its built-in authentication for connection attempts for the account. Clients must match the password in the Password column of the account row.

If an account row names a plugin in the plugin column, the server uses it to authenticate connection attempts for the account. Whether the plugin uses the value in the Password column is up to the plugin.

http://doc.docs.sk/mysql-refman-5.5/grant-table-structure.html

MySQL 5.6

The user table plugin, Password, and authentication_string columns store authentication plugin and credential information.

If an account row names a plugin in the plugin column, the server uses it to authenticate connection attempts for the account. It is up to the plugin whether it uses the Password and authentication_string column values.

If the plugin column for an account row is empty, the server authenticates the account using the mysql_native_password or mysql_old_password plugin implicitly, depending on the format of the password hash in the Password column. If the Password value is empty or a 4.1 password hash (41 characters), the server uses mysql_native_password. If the password value is a pre-4.1 password hash (16 characters), the server uses mysql_old_password. (For additional information about these hash formats, see Section 6.1.2.4, “Password Hashing in MySQL”.) Clients must match the password in the Password column of the account row.

https://dev.mysql.com/doc/refman/5.6/en/grant-tables.html

MySQL 5.7

https://dev.mysql.com/doc/refman/5.7/en/grant-tables.html

The Password column no longer exists.
It is no longer legal to have empty values in the plugin column (see also).
The mysql_old_password authentication is no longer supported.

MySQL 8.0

https://dev.mysql.com/doc/refman/8.0/en/grant-tables.html

In 8.0, the password storage default changes to caching_sha2_password, but mysql_native_password is still supported.
A lot of client libraries from external vendors do not yet support caching_sha2_password, so use of mysql_native_password is common.

Authentication got really compliated in 8.0:

• Accounts can have multiple (2) passwords for password rotation (this is stupid, you want account rotation).
• Accounts can have multi-factor-authentication (see also).

[xsist10]

Tasks:

• Implement Check (Added Passwordless Account check (#84) #86)
• Add tests (Added Passwordless Account check (#84) #86)
• Add documentation to Wiki