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

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

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.5.12
    • 10.4.22, 10.5.13, 10.6.5
    • Authentication and Privilege System
    • 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

        Issue Links

          causes

          Bug - A problem which impairs or prevents the functions of the product. MDEV-33726 Moving from MariaDB 10.5 to 10.6 mysql_upgrade is not updating some system tables

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            hborresen Hans Borresen added a comment -

            Just to note – I tested only on 10.5.12, not 10.4.x. However, I suspect you'd encounter the issue on 10.4 as well.

            hborresen Hans Borresen added a comment - Just to note – I tested only on 10.5.12, not 10.4.x. However, I suspect you'd encounter the issue on 10.4 as well.
            danblack Daniel Black added a comment -

            Confirmed. In commit 59e6d14c47aa the 'MODIFY IF EXISTS password_last_changed timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER max_statement_time' has absolutely no effect made worse by the default value ending up as 0000-00-00 00:00:00 as described.

            Correct 10.2, 10.3 behavior to actually have the value as described.

            10.4+ have the mysql_upgrade detect this false behavior and just give the user a valid password_last_changed like

            patch against 10.4

            		 
            diff --git a/scripts/mysql_system_tables_fix.sql b/scripts/mysql_system_tables_fix.sql
            		 
            index 9dd775aaf30..5623d73f93f 100644
            		 
            --- a/scripts/mysql_system_tables_fix.sql
            		 
            +++ b/scripts/mysql_system_tables_fix.sql
            		 
            @@ -815,7 +815,7 @@ IF 'BASE TABLE' = (select table_type from information_schema.tables where table_
            		 
                                 'max_statement_time', max_statement_time,
            		 
                                 'plugin', if(plugin>'',plugin,if(length(password)=16,'mysql_old_password','mysql_native_password')),
            		 
                                 'authentication_string', if(plugin>'' and authentication_string>'',authentication_string,password),
            		 
            -                    'password_last_changed', if(password_expired='Y', 0, UNIX_TIMESTAMP(password_last_changed)),
            		 
            +                    'password_last_changed', if(password_expired='Y', 0, if(password_last_changed, UNIX_TIMESTAMP(password_last_changed), NOW())),
            		 
                                 'password_lifetime', ifnull(password_lifetime, -1),
            		 
                                 'account_locked', 'Y'=account_locked,
            		 
                                 'default_role', default_role,

            Tested the above and it prevents mysql_upgrade from locking the users created.

            danblack Daniel Black added a comment - Confirmed. In commit 59e6d14c47aa the 'MODIFY IF EXISTS password_last_changed timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER max_statement_time' has absolutely no effect made worse by the default value ending up as 0000-00-00 00:00:00 as described. Correct 10.2, 10.3 behavior to actually have the value as described. 10.4+ have the mysql_upgrade detect this false behavior and just give the user a valid password_last_changed like patch against 10.4 diff --git a/scripts/mysql_system_tables_fix.sql b/scripts/mysql_system_tables_fix.sql index 9dd775aaf30..5623d73f93f 100644 --- a/scripts/mysql_system_tables_fix.sql +++ b/scripts/mysql_system_tables_fix.sql @@ -815,7 +815,7 @@ IF 'BASE TABLE' = (select table_type from information_schema.tables where table_ 'max_statement_time', max_statement_time, 'plugin', if(plugin>'',plugin,if(length(password)=16,'mysql_old_password','mysql_native_password')), 'authentication_string', if(plugin>'' and authentication_string>'',authentication_string,password), - 'password_last_changed', if(password_expired='Y', 0, UNIX_TIMESTAMP(password_last_changed)), + 'password_last_changed', if(password_expired='Y', 0, if(password_last_changed, UNIX_TIMESTAMP(password_last_changed), NOW())), 'password_lifetime', ifnull(password_lifetime, -1), 'account_locked', 'Y'=account_locked, 'default_role', default_role, Tested the above and it prevents mysql_upgrade from locking the users created.
            danblack Daniel Black added a comment - - edited

            Sergei,

            Can I get a review on both bb-10.2-danielblack-MDEV-26363 and bb-10.4-MDEV-26363-danielblack-zero_last_password_changed

            The 10.2 change (not to be merged to 10.4, similar exists there) implements that password expired account in 5.7 get the chance to be unlocked by set password= in 10.2,10.3 (without passwords being enforced), so by the time a 10.4 mysql_upgrade is done, the password_last_changed and password_expired reflect current values.

            The MySQL-5.7 behavior leaves password_last_changed as NULL on account creation, and will change its value on GRANT ... IDENTIFIED BY/VIA, ALTER USER .. IDENTIFIED which I currently haven't implemented strictly. As both CREATE USER and GRANT both utilize the sql/sql_acl.cc:replace_user_table method it becomes harder to implement both consistently without more restructuring.

            Guidance as to how far to take this 10.2 implementation (if any) appreciated.

            10.4 mysql_upgrade checks, fairly basic - a 0 value is treated as it was updated/created sometime. I don't think its possible to distinguish which, so assume it was an update and record the current time and give users some extra time.

            danblack Daniel Black added a comment - - edited Sergei, Can I get a review on both bb-10.2-danielblack- MDEV-26363 and bb-10.4- MDEV-26363 -danielblack-zero_last_password_changed The 10.2 change (not to be merged to 10.4, similar exists there) implements that password expired account in 5.7 get the chance to be unlocked by set password= in 10.2,10.3 (without passwords being enforced), so by the time a 10.4 mysql_upgrade is done, the password_last_changed and password_expired reflect current values. The MySQL-5.7 behavior leaves password_last_changed as NULL on account creation, and will change its value on GRANT ... IDENTIFIED BY/VIA , ALTER USER .. IDENTIFIED which I currently haven't implemented strictly. As both CREATE USER and GRANT both utilize the sql/sql_acl.cc:replace_user_table method it becomes harder to implement both consistently without more restructuring. Guidance as to how far to take this 10.2 implementation (if any) appreciated. 10.4 mysql_upgrade checks, fairly basic - a 0 value is treated as it was updated/created sometime. I don't think its possible to distinguish which, so assume it was an update and record the current time and give users some extra time.
            serg Sergei Golubchik added a comment -

            commit 2fad51d7915 in 10.4 is ok to push.

            10.2 fix — I don't think it's needed, 10.2 and 10.3 don't update fields they don't know about, it's by no means a bug. Adding a new feature to 10.2 and 10.3 is kind of late now, I'd say

            serg Sergei Golubchik added a comment - commit 2fad51d7915 in 10.4 is ok to push. 10.2 fix — I don't think it's needed, 10.2 and 10.3 don't update fields they don't know about, it's by no means a bug. Adding a new feature to 10.2 and 10.3 is kind of late now, I'd say
            danblack Daniel Black added a comment -

            Thanks serg.

            danblack Daniel Black added a comment - Thanks serg .

            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.