ALTER USER REQUIRE SSL or X509 clears out user's authentication_string (password)

[zhangguiyu]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

mysql> select user,host,authentication_string from mysql.user;
+-------------+-----------+-------------------------------------------+
| user        | host      | authentication_string                     |
+-------------+-----------+-------------------------------------------+
| root        | localhost |                                           |
| backup      | %         | *9CDD29A76B21FC5186F5D9F0316EB3D7EADC695E |
| remote_root | %         | *2DBC7422585CBFB7E0404FF9E0228C33E22BD92A |
+-------------+-----------+-------------------------------------------+
3 rows in set (0.23 sec)

mysql> alter user backup require SSL;
Query OK, 0 rows affected (1.49 sec)

mysql> select user,host,authentication_string from mysql.user;
+-------------+-----------+-------------------------------------------+
| user        | host      | authentication_string                     |
+-------------+-----------+-------------------------------------------+
| root        | localhost |                                           |
| backup      | %         |                                           |
| remote_root | %         | *2DBC7422585CBFB7E0404FF9E0228C33E22BD92A |
+-------------+-----------+-------------------------------------------+
3 rows in set (0.31 sec)

2. What did you expect to see? (Required)

User require SSL or X509 to login

3. What did you see instead (Required)

Yes, SSL or X509 enabled. but user password also set to blank or some value, basically user can no longer login. the password must then be reset.

4. What is your TiDB version? (Required)

Server version: 5.7.25-TiDB-v5.0.0-rc TiDB Server (Apache License 2.0) Community Edition, MySQL 5.7 compatible

[morgo]

Verified against master:

CREATE USER u1 IDENTIFIED BY 'acdc';
ALTER USER u1 REQUIRE SSL;
select user,host,authentication_string from mysql.user;

..

mysql> CREATE USER u1 IDENTIFIED BY 'acdc';
Query OK, 0 rows affected (0.04 sec)

mysql> select user,host,authentication_string from mysql.user;
+-------------+------+-------------------------------------------+
| user        | host | authentication_string                     |
+-------------+------+-------------------------------------------+
| root        | %    |                                           |
| backupadmin | %    |                                           |
| u1          | %    | *B50FBDB37F1256824274912F2A1CE648082C3F1F |
+-------------+------+-------------------------------------------+
3 rows in set (0.01 sec)

mysql> ALTER USER u1 REQUIRE SSL;
Query OK, 0 rows affected (0.04 sec)

mysql> select user,host,authentication_string from mysql.user;
+-------------+------+-----------------------+
| user        | host | authentication_string |
+-------------+------+-----------------------+
| root        | %    |                       |
| backupadmin | %    |                       |
| u1          | %    |                       |
+-------------+------+-----------------------+
3 rows in set (0.00 sec)

mysql> SELECT tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v5.2.0-alpha-8-gd2c477529
Edition: Community
Git Commit Hash: d2c47752958087c7b7fe229bda4a56af90f5216d
Git Branch: fix-restore-permissions
UTC Build Time: 2021-06-07 20:24:47
GoVersion: go1.16.4
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
1 row in set (0.00 sec)

[ti-srebot]

Please edit this comment or add a new comment to complete the following information

Not a bug

1. Remove the 'type/bug' label
2. Add notes to indicate why it is not a bug

Duplicate bug

1. Add the 'type/duplicate' label
2. Add the link to the original bug

Bug

Note: Make Sure that 'component', and 'severity' labels are added
Example for how to fill out the template: #20100

1. Root Cause Analysis (RCA) (optional)

2. Symptom (optional)

3. All Trigger Conditions (optional)

4. Workaround (optional)

5. Affected versions

6. Fixed versions