[MDEV-16774] SET PASSWORD and ALTER USER with slightly different results - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.3.8, 10.3.11, 10.2(EOL), 10.3(EOL)
Fix Version/s: 10.3.11, 10.2.19
Component/s: Authentication and Privilege System
Labels:
None
Environment:
Debian Stretch

Description

Just create two users:

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

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

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 |        |                       |

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

Edit password with ALTER USER:

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

Edit password with SET PASSWORD:

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

Result:

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 | %    |                                           | mysql_native_password | *6AF1DDD48878E32D13C07A707FAA1E7A4CD516DA |

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

Is this intended behavior?

Attachments

Issue Links

causes

MXS-2111 After SET PASSWORD done on the database, MaxScale not able to connect

Closed

duplicates

MDEV-17136 Set Password command doesn't update Password Column in mysql.user table

Closed

is caused by

MDEV-16238 root/localhost authn prioritizes authentication_string over Password

Closed

relates to

MDEV-12715 remove mysql.user.password column

Closed

Activity

Ascending order - Click to sort in descending order

View 4 older comments

markus makela added a comment - 2018-10-26 11:58

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

markus makela added a comment - 2018-10-26 11:58 This affects MaxScale and also needs to be fixed there ( MXS-2111 ).

Alfredo Moralejo added a comment - 2019-02-19 16:55

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:

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)

Alfredo Moralejo added a comment - 2019-02-19 16:55 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: 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)

Radoslav Bodó added a comment - 2019-02-24 12:38

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.

Radoslav Bodó added a comment - 2019-02-24 12:38 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.

Daniel Black added a comment - 2019-02-28 09:53

Not consistent as above test.

Daniel Black added a comment - 2019-02-28 09:53 Not consistent as above test.

Sergei Golubchik added a comment - 2019-03-04 13:14 - edited

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.

Sergei Golubchik added a comment - 2019-03-04 13:14 - edited 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.

MariaDB Server

SET PASSWORD and ALTER USER with slightly different results

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration