Debian Stretch

Thanks for the report.
I think it's even more questionable why CREATE USER .. IDENTIFIED WITH and ALTER USER .. IDENTIFIED WITH have different results.
Reproducible on 10.2 as well.

I'm setting it to confirmed, although given the task MDEV-12715 for 10.4, it might be not worth fixing (if everything works okay with both variations of the user row).

Right, this is an oversight. Privileges still work properly, it's a "cosmetic" thing.

When checking if a user has a password or not we will use password column with mysql_native_password (or the old auth if password is in short format) if plugin and authentication_string are not set. SET PASSWORD will play with plugin and authentication string values and clear the password column while alter user will use the password column if it can.

Inconsistent, but it won't affect server behaviour with regards to passwords and authentication. If you don't look at mysql.user table, you won't notice a change

serg what do you think? Should we update the code in 10.2 or just scrap the password column altogether in 10.4 and leave this behaviour as is?

Just for your information:
I stumbled upon this while using https://forge.puppet.com/puppetlabs/mysql and opened a issue there too: https://tickets.puppetlabs.com/browse/MODULES-7487

This is, probably, caused by my fix for MDEV-16238.

In that fix I made SET PASSWORD to use the authentication_string and clear the password column. It was to avoid the cases when both are set making the behavior ambiguous.

Apparently, CREATE USER does the opposite, prefers password column. I don't think it matters much, as long as authentication_string and password are never set both at the same time.

This affects MaxScale and also needs to be fixed there (MXS-2111).

According to the info in this issue, this is fixed in 10.3.11. However I'm testing it and i still have doubts about the behavior.

When creating a user with:

create user foo2 identified with mysql_native_password as '*E8D46CE25265E545D225A8A6F1BAF642FEBEE5CB';

Shouldn't this populate authentication_string with the password and mysql_native_password in plugin. what I actually find is:

MariaDB [(none)]> create user foo identified by 'bar';

Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> create user foo2 identified with mysql_native_password as '*E8D46CE25265E545D225A8A6F1BAF642FEBEE5CB';

Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> select user, host, password, plugin, authentication_string from mysql.user where user like 'foo%';

+------+------+-------------------------------------------+--------+-----------------------+

| user | host | password                                  | plugin | authentication_string |

+------+------+-------------------------------------------+--------+-----------------------+

| foo  | %    | *E8D46CE25265E545D225A8A6F1BAF642FEBEE5CB |        |                       |

| foo2 | %    | *E8D46CE25265E545D225A8A6F1BAF642FEBEE5CB |        |                       |

+------+------+-------------------------------------------+--------+-----------------------+

If i change the password using alter user:

MariaDB [(none)]> alter user foo2 IDENTIFIED with mysql_native_password as '*6AF1DDD48878E32D13C07A707FAA1E7A4CD516DA';

Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> select user, host, password, plugin, authentication_string from mysql.user where user like 'foo%';

+------+------+-------------------------------------------+--------+-----------------------+

| user | host | password                                  | plugin | authentication_string |

+------+------+-------------------------------------------+--------+-----------------------+

| foo  | %    | *6AF1DDD48878E32D13C07A707FAA1E7A4CD516DA |        |                       |

| foo2 | %    | *6AF1DDD48878E32D13C07A707FAA1E7A4CD516DA |        |                       |

+------+------+-------------------------------------------+--------+-----------------------+

2 rows in set (0.000 sec)

Only using set password updates authentication_string:

MariaDB [(none)]> SET PASSWORD FOR 'foo2'@'%' = '*6AF1DDD48878E32D13C07A707FAA1E7A4CD516DA';

Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> select user, host, password, plugin, authentication_string from mysql.user where user like 'foo%';

+------+------+-------------------------------------------+-----------------------+-------------------------------------------+

| user | host | password                                  | plugin                | authentication_string                     |

+------+------+-------------------------------------------+-----------------------+-------------------------------------------+

| foo  | %    | *6AF1DDD48878E32D13C07A707FAA1E7A4CD516DA |                       |                                           |

| foo2 | %    | *6AF1DDD48878E32D13C07A707FAA1E7A4CD516DA | mysql_native_password | *6AF1DDD48878E32D13C07A707FAA1E7A4CD516DA |

+------+------+-------------------------------------------+-----------------------+-------------------------------------------+

2 rows in set (0.000 sec)

My understanding is that this behavior is not the expected one. I'm using 10.3.12:

1. mysqld --version
   mysqld Ver 10.3.12-MariaDB-1:10.3.12+maria~bionic for debian-linux-gnu on x86_64 (mariadb.org binary distribution)

I agree with @Alfredo Moralejo , the issue is still present on 10.3.12 from debian buster. please consider making create user, alter user and set password strongly consistent, otherwise it would break many configuration management engines.

Not consistent as above test.

The fix in the commit dd6e74c62a2 made sure that the password field is always set, because that was the original issue that broke third-party tools.

Other differences between password and authentication_string were removed in 10.4.