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

MySQL users can break if using mysql_native_plugin in version 10.2

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 10.1, 10.2, 10.3
    • Fix Version/s: 10.1.38, 10.2.22, 10.3.13
    • Labels:
      None
    • Environment:
      OS: CloudLinux release 7.6 (Vladimir Lyakhov)
      Kernel: 3.10.0-962.3.2.lve1.5.24.7.el7.x86_64
      Arch: x86_64
      Environment: kvm
      CPU: Intel Xeon E312xx (Sandy Bridge) w/ 4 core(s)

      Description

      Details

      MariaDB 10.2 introduced the 'SHOW CREATE USER' syntax: https://mariadb.com/kb/en/library/show-create-user/

      This was added via MDEV-7978: https://github.com/MariaDB/server/commit/5e873141a57fcf8e0f5bc8dd4e5434073e78f43f#diff-dca2f11b2511ceff9960dc3bcd972d04

      The issue that this commit presents is that neither 'SHOW GRANTS FOR $user' or 'SHOW CREATE USER $user' will show a valid 'IDENTIFIED BY' line for a user if the 'plugin' column is set to 'mysql_native_password' AND the 'authentication_string' column is NULL.

      For example:

      # mysql mysql -e "select User,Host,plugin,Password,authentication_string from user where User='cptest_testuser';"
      +-----------------+-----------+-----------------------+-------------------------------------------+-----------------------+
      | User            | Host      | plugin                | Password                                  | authentication_string |
      +-----------------+-----------+-----------------------+-------------------------------------------+-----------------------+
      | cptest_testuser | localhost | mysql_native_password | *4AAD86FE22D73A14522B16350DAE469EDC645BE0 |                       |
       
      # mysql -e "show grants for 'cptest_testuser'@'localhost';"
      +-----------------------------------------------------------------------------+
      | Grants for cptest_testuser@localhost                                        |
      +-----------------------------------------------------------------------------+
      | GRANT USAGE ON *.* TO 'cptest_testuser'@'localhost'                         |
      | GRANT ALL PRIVILEGES ON `cptest\_testdb`.* TO 'cptest_testuser'@'localhost' |
      +-----------------------------------------------------------------------------+
       
      # mysql -e "show create user 'cptest_testuser'@'localhost';"
      +-------------------------------------------+
      | CREATE USER for cptest_testuser@localhost |
      +-------------------------------------------+
      | CREATE USER 'cptest_testuser'@'localhost' |
      +-------------------------------------------+
      

      • This could easily lead someone to believe that the user does not have a password set when in fact it does have a valid password set
      • It is possible to run into this issue when updating from 10.1 to 10.2

      Suggested Resolution / Expected Behavior
      • either 'SHOW GRANTS FOR' or 'SHOW CREATE USER' should provide the proper 'IDENTIFIED BY' line for the user's password if the user has a valid password
      • add_user_parameters should be looking at the Password column as well as the authentication_string column when the mysql_native_plugin is enabled

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              serg Sergei Golubchik
              Reporter:
              t.holloway Travis
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: