[MDEV-21928] ALTER USER doesn't remove excess authentication plugins from mysql.global_priv - Jira

XML

Word

Printable

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 10.4.12
Fix Version/s: 10.4.13
Component/s: Authentication and Privilege System
Labels:
None

Description

If you use ALTER USER to remove unix_socket authentication from root@localhost, it seems to come back after either the server is restarted or FLUSH PRIVILEGES is executed, because the authentication plugin is not removed from the user account's row in mysql.global_priv.

To reproduce:

Check that the user has unix_socket authentication:

MariaDB [(none)]> SHOW CREATE USER 'root'@'localhost';

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

| CREATE USER for root@localhost                                                                     |

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

| CREATE USER 'root'@'localhost' IDENTIFIED VIA mysql_native_password USING 'invalid' OR unix_socket |

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

1 row in set (0.000 sec)

Remove it:

MariaDB [(none)]> ALTER USER 'root'@'localhost' IDENTIFIED VIA mysql_native_password USING PASSWORD('password');

Query OK, 0 rows affected (0.001 sec)

Check again:

MariaDB [(none)]> SHOW CREATE USER 'root'@'localhost';

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

| CREATE USER for root@localhost                                                                    |

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

| CREATE USER 'root'@'localhost' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' |

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

1 row in set (0.000 sec)

Restart the server:

$ sudo systemctl restart mariadb

Check again:

MariaDB [(none)]> SHOW CREATE USER 'root'@'localhost';

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

| CREATE USER for root@localhost                                                                                                       |

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

| CREATE USER 'root'@'localhost' IDENTIFIED VIA mysql_native_password USING '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' OR unix_socket |

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

1 row in set (0.000 sec)

More in-depth analysis shows that the unix_socket entry is not actually removed from mysql.global_priv after the ALTER USER statement:

MariaDB [(none)]> ALTER USER 'root'@'localhost' IDENTIFIED VIA mysql_native_password USING PASSWORD('password');

Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> SELECT JSON_DETAILED(Priv) FROM mysql.global_priv WHERE User='root' AND Host='localhost'\G

*************************** 1. row ***************************

JSON_DETAILED(Priv): {

    "access": 1073741823,

    "plugin": "mysql_native_password",

    "authentication_string": "*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19",

    "auth_or":

},

            "plugin": "unix_socket"

],

    "password_last_changed": 1584045156

1 row in set (0.000 sec)

And the server restart isn't even necessary. The authentication method also comes back with a FLUSH PRIVILEGES:

MariaDB [(none)]> SHOW CREATE USER 'root'@'localhost';

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

| CREATE USER for root@localhost                                                                    |

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

| CREATE USER 'root'@'localhost' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' |

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

1 row in set (0.000 sec)

MariaDB [(none)]> FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> SHOW CREATE USER 'root'@'localhost';

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

| CREATE USER for root@localhost                                                                                                       |

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

| CREATE USER 'root'@'localhost' IDENTIFIED VIA mysql_native_password USING '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19' OR unix_socket |

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

1 row in set (0.000 sec)

Attachments

Issue Links

is caused by

MDEV-11340 Allow multiple alternative authentication methods for the same user

Closed

MDEV-12484 Enable unix socket authentication by default

Closed

relates to

MDEV-23374 ALTER USER documentation needs to reflect multiple authentication methods

Closed

MDEV-21929 Enhance ALTER USER for multiple authentication methods

Open

Activity

People

Assignee:: Sergei Golubchik

Reporter:: Geoff Montee (Inactive)

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2020-03-12 20:31

Updated:: 2020-08-02 21:48

Resolved:: 2020-04-27 07:58

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.