Detect user passwords in password and authentication_string mixed environments

[pes-soft]

Hi,

Mysqltuner produces "user with no password set" false positives in Security Recommendations section, if it is run in an environment, where MySQL version is not 5.7, there are users, which have mysql_native_password plugin specified and user passwords are stored in "authentication_string" column.

Currently mysqltuner selects a name of password column based on specific database version ( for 5.7 it uses "authentication_string", for the rest the default "password" ), but there are already more versions supporting "authentication_string" is greater.

Also, it is possible to have a mixed environment, where there are users, which have no plugin set and password is in "password" column and other users, which have "mysql_native_password" plugin set and password is in "authentication_string" column.

Would it be possible for mysqltuner to detect passwords in both alternatives?

Tested with: MySQLTuner 1.6.15, MySQL version 10.1.16-MariaDB, [!!] User 'root@localhost' has no password set, (but actually password is set).

[jmrenouard]

Hi @pes-soft

Can you give me more information about SQL request we should perform to check mysql_native_password_plugin ?

What's the exact way we check this on MariaDB 10.1 ?

Thanks for your support
@jmrenouard

[pes-soft]

Table columns "plugin" and "authentication_string" have been introduced in MySQL 5.5.7. So for earlier versions: password hash is always in "password" column and also any query using "plugin" column will fail.

Table column "password" has been removed in MySQL 5.7.6. So for this and later versions: password hash is always stored in "authentication_string" column and also any query using "password" column will fail.

MariaDB (probably since MariaDB 5.2 ) has all the columns ("password", "plugin" and "authentication_string"). At least I've verified this on 5.5.51 and 10.1.16.

So for a generic solution, I suggest that we need to know whether "plugin" column exists at first. If it does not exist, we need to check only "password" column for existing hash. If "plugin" column exists, we need to check if it is empty ( i.e. using default password authentication ) or if it contains plugin with '_password' string in the name - this means that password hash is stored in user table. Then we can check whether password hash is set either in "password" or "authentication_string" column.

[jmrenouard]

Hi @pes-soft

Thanks for this information.

We will try to do something about that.

My main worry about that is that we don't really get enough configurations to be sure it's works perfectly on all kind of MySQL/MAriaDB/PerconaDB versions.

We are hoping a big enterprise can hear this message and help us to deal with this complexity or someone who can help us with travis to enhance test suite which is very poor actually.

Best regards,

[pes-soft]

Hi @jmrenouard

Thanks for your feedback and effort too.

When I needed to execute some tests on more MySQL versions, I used MySQL::Sandbox, which makes it quite easy to setup instances of various MySQL/MariaDB versions and work with them ( create, start, stop, reset, ... ). It might work with PerconaDB as well. Of course real life environments can be quite different from the default, but maybe it will help.

[tholu]

These issues seem related: #373 #427

[jmrenouard]

password checks work perfectly on all MariaDB 10.X