MDEV-26363: support password_last_changed from 5.7bb-10.2-danielblack-MDEV-26363

The password_last_changed will only exist in datadir
that was previously 5.7. If this is the case we maintain the
password_last_changed so that when a user upgrades to 10.4+
it contains a valid value. At this point the password
expiry will work like it did in 5.7, taking into account
the user changes of the password in the mean time.

Unlike MySQL-5.7, we aren't password unlocking on
GRANT USAGE ON *.* TO user IDENTIFIED BY 'pass';

3 files changed, 99 insertions, 0 deletions

diff --git a/mysql-test/r/set_password.result b/mysql-test/r/set_password.result
index 733d9c96187..8546e203c54 100644
--- a/mysql-test/r/set_password.result
+++ b/mysql-test/r/set_password.result
@@ -179,3 +179,44 @@ select user,host,password,plugin,authentication_string from mysql.user where use
 user	host	password	plugin	authentication_string
 foo	localhost		mysql_native_password	
 drop user foo@localhost;
+set global secure_auth=0;
+ALTER TABLE mysql.user ADD password_last_changed timestamp NULL AFTER max_statement_time;
+flush privileges;
+create user expnatauth@localhost identified via 'mysql_native_password' using '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29';
+create user expnewpass@localhost identified by password '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29';
+create user expnewpassnat@localhost identified via 'mysql_native_password';
+create user expoldauth@localhost identified with 'mysql_old_password' using '378b243e220ca493';
+create user expoldpass@localhost identified by password '378b243e220ca493';
+create user expoldpassold@localhost identified with 'mysql_old_password';
+select user,password_expired, password_last_changed IS NULL
+from mysql.user
+where user like 'exp%' order by user;
+user	password_expired	password_last_changed IS NULL
+expnatauth	N	0
+expnewpass	N	0
+expnewpassnat	N	1
+expoldauth	N	0
+expoldpass	N	0
+expoldpassold	N	1
+update mysql.user set password_expired='Y',password_last_changed=0 where user like 'exp%';
+flush privileges;
+set @before_setpassword=NOW();
+set password for expnatauth@localhost = '*C4BDCEBE19083CE2A1F959FD02F964C7AF4CFC2C';
+set password for expnewpass@localhost = '*C4BDCEBE19083CE2A1F959FD02F964C7AF4CFC2C';
+set password for expnewpassnat@localhost = '*C4BDCEBE19083CE2A1F959FD02F964C7AF4CFC2C';
+set password for expoldauth@localhost = 'C78b243e220ca49C';
+set password for expoldpass@localhost = 'C78b243e220ca49C';
+set password for expoldpassold@localhost = 'C78b243e220ca49C';
+select user,password_expired,password_last_changed >= @before_setpassword FROM mysql.user WHERE user like 'exp%' order by user;
+user	password_expired	password_last_changed >= @before_setpassword
+expnatauth	N	1
+expnewpass	N	1
+expnewpassnat	N	1
+expoldauth	N	1
+expoldpass	N	1
+expoldpassold	N	1
+ALTER TABLE mysql.user DROP COLUMN password_last_changed;
+flush privileges;
+drop user expnatauth@localhost, expnewpass@localhost, expnewpassnat@localhost;
+drop user expoldauth@localhost, expoldpass@localhost, expoldpassold@localhost;
+set global secure_auth=default;
diff --git a/mysql-test/t/set_password.test b/mysql-test/t/set_password.test
index fc1ecb5ef5c..0ad0f5caaec 100644
--- a/mysql-test/t/set_password.test
+++ b/mysql-test/t/set_password.test
@@ -145,3 +145,42 @@ select user,host,password,plugin,authentication_string from mysql.user where use
 set password for 'foo'@'localhost' = '';
 select user,host,password,plugin,authentication_string from mysql.user where user='foo';
 drop user foo@localhost;
+
+# MDEV-26363 MySQL-5.7 password expiry update, so when a user gets to 10.4+ it works
+# as expected.
+
+set global secure_auth=0;
+ALTER TABLE mysql.user ADD password_last_changed timestamp NULL AFTER max_statement_time;
+flush privileges;
+
+create user expnatauth@localhost identified via 'mysql_native_password' using '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29';
+create user expnewpass@localhost identified by password '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29';
+create user expnewpassnat@localhost identified via 'mysql_native_password';
+create user expoldauth@localhost identified with 'mysql_old_password' using '378b243e220ca493';
+create user expoldpass@localhost identified by password '378b243e220ca493';
+create user expoldpassold@localhost identified with 'mysql_old_password';
+
+# created users are unexpired and with a password_last_changed is NULL on first creation
+select user,password_expired, password_last_changed IS NULL
+from mysql.user
+where user like 'exp%' order by user;
+# We don't have a ALTER USER .. PASSWORD EXPIRE until 10.4 so fake it. Break(=0) password_last_changed like before MDEV-26363
+update mysql.user set password_expired='Y',password_last_changed=0 where user like 'exp%';
+flush privileges;
+
+set @before_setpassword=NOW();
+
+set password for expnatauth@localhost = '*C4BDCEBE19083CE2A1F959FD02F964C7AF4CFC2C';
+set password for expnewpass@localhost = '*C4BDCEBE19083CE2A1F959FD02F964C7AF4CFC2C';
+set password for expnewpassnat@localhost = '*C4BDCEBE19083CE2A1F959FD02F964C7AF4CFC2C';
+set password for expoldauth@localhost = 'C78b243e220ca49C';
+set password for expoldpass@localhost = 'C78b243e220ca49C';
+set password for expoldpassold@localhost = 'C78b243e220ca49C';
+
+select user,password_expired,password_last_changed >= @before_setpassword FROM mysql.user WHERE user like 'exp%' order by user;
+
+ALTER TABLE mysql.user DROP COLUMN password_last_changed;
+flush privileges;
+drop user expnatauth@localhost, expnewpass@localhost, expnewpassnat@localhost;
+drop user expoldauth@localhost, expoldpass@localhost, expoldpassold@localhost;
+set global secure_auth=default;
diff --git a/sql/sql_acl.cc b/sql/sql_acl.cc
index 89fecc92e9b..ebccca2139b 100644
--- a/sql/sql_acl.cc
+++ b/sql/sql_acl.cc
@@ -971,6 +971,21 @@ class User_table: public Grant_table_base
   { return get_field(start_privilege_column + num_privileges() + 12); }
   Field* max_statement_time() const
   { return get_field(start_privilege_column + num_privileges() + 13); }
+  void set_password_last_changed(my_time_t x) const
+  {
+    Field *f= get_field(start_privilege_column + num_privileges() + 14);
+    if (f && f->real_type() == MYSQL_TYPE_TIMESTAMP2 &&
+        !strcasecmp(f->field_name,"password_last_changed"))
+    {
+       f->set_notnull();
+       f->store_timestamp(x, 0);
+    }
+    f= password_expired();
+    if (f && f->real_type() == MYSQL_TYPE_ENUM &&
+        !strcasecmp(f->field_name,"password_expired"))
+      /* storing first N in enum */
+      f->store(1, 0);
+  }
 
   /*
     Check if a user entry in the user table is marked as being a role entry
@@ -4013,6 +4028,7 @@ static bool update_user_table(THD *thd, const User_table& user_table,
   if (user_table.password())
     user_table.password()->store(new_password, new_password_len, system_charset_info);
 
+  user_table.set_password_last_changed(thd->query_start());
 
   if ((error=table->file->ha_update_row(table->record[1],table->record[0])) &&
       error != HA_ERR_RECORD_IS_THE_SAME)
@@ -4186,8 +4202,11 @@ static int replace_user_table(THD *thd, const User_table &user_table,
   /* If we don't have a password column, we'll use the authentication_string
      column later. */
   if (combo.pwhash.str[0] && user_table.password())
+  {
     user_table.password()->store(combo.pwhash.str, combo.pwhash.length,
                                  system_charset_info);
+    user_table.set_password_last_changed(thd->query_start());
+  }
   /* We either have the password column, the plugin column, or both. Otherwise
      we have a corrupt user table. */
   DBUG_ASSERT(user_table.password() || user_table.plugin());