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

Passwords incorrectly expiring after MySQL5.7 -> MariaDB10.3 -> 10.4+ upgrades.

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.5.12
    • 10.4.22, 10.5.13, 10.6.5
    • None

    Description

      Details

      Filing this per the discussion in zulip chat (https://mariadb.zulipchat.com/#narrow/stream/118759-general/topic/Password.20issues.20after.205.2E7.20-.3E.2010.2E3.20-.3E.2010.2E5.20upgrade.20path)

      Password expiration existed in MySQL 5.7, but not in MariaDB 10.3.
      On servers that upgraded from 5.7 to 10.3, there are leftover "cruft" fields in mysql.user relating to password expiry that do not get used.
      When creating users on a 10.3 system, the "password_last_changed" field is initialized as 0000-00-00 00:00:00

      However, password expiration was added in 10.4 – and on such systems, users who had 0000-00-00 00:00:00 as their password_last_changed get their passwords expired – even if password expiration is not enabled.

      I've attached a sample affected 10.3 datadir to this case.


      Step-by-Step Instructions

      The basic premise is to start on MySQL 5.7, upgrade to 10.3, create a user, then upgrade to 10.4+.

      However, I have a sample affected datadir ready.

      1. Extract the contents of maria_103_datadir.tar.xz to your datadir.

      tar -xf maria_103_datadir.tar.xz
      

      2. Start up MariaDB 10.4+

      3. Run mysql_upgrade

      4. Try to authenticate as the sql103 user, observe error:

      [root@10-1-33-101 ~]# mysql -u sql103 -p'TestPassPleaseIgnore' -e 'SHOW DATABASES;'
      ERROR 1820 (HY000) at line 1: You must SET PASSWORD before executing this statement
      

      Just for additional info, here is the global priv entry for the user post-upgrade:

      [root@10-1-33-101 ~]# mysql -BNe 'SELECT Priv from mysql.global_priv WHERE User="sql103" AND Host="localhost";' | python -m json.tool
      {
          "access": 0,
          "account_locked": false,
          "authentication_string": "*CA8C7DC8B54EDC492104531EA6612FC243D816C3",
          "default_role": "",
          "is_role": false,
          "max_connections": 0,
          "max_questions": 0,
          "max_statement_time": 0.0,
          "max_updates": 0,
          "max_user_connections": 0,
          "password_last_changed": 0,
          "password_lifetime": -1,
          "plugin": "mysql_native_password",
          "ssl_cipher": "",
          "ssl_type": 0,
          "x509_issuer": "",
          "x509_subject": ""
      }
      


      Suggested Resolution / Expected Behavior

      In this specific use case (where password expiration was not configured to occur), I would not expect the user's password to expire after upgrading to MariaDB 10.4+.


      Known work around(s)

      Edit: a better workaround fix:

      mysql> update mysql.global_priv set Priv=JSON_SET(Priv, '$.password_last_changed', UNIX_TIMESTAMP()) WHERE JSON_VALUE(Priv, '$.password_last_changed') = '0';
      Query OK, 25 rows affected (0.01 sec)
      Rows matched: 25  Changed: 25  Warnings: 0
       
      mysql> flush privileges;
      Query OK, 0 rows affected (0.00 sec)
      

      Attachments

        1. log.txt
          14 kB
          Daniel Black
        2. maria_103_datadir.tar.xz
          327 kB
          Hans Borresen

        Issue Links

          Activity

            People

              danblack Daniel Black
              hborresen Hans Borresen
              Votes:
              0 Vote for this issue
              Watchers:
              4 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.