Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-21928

ALTER USER doesn't remove excess authentication plugins from mysql.global_priv

    XMLWordPrintable

Details

    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

          Activity

            People

              serg Sergei Golubchik
              GeoffMontee Geoff Montee (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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