MDEV-7597 Expiration of user passwords

post-merge changes:
* handle password expiration on old tables like everything else -
  make changes in memory, even if they cannot be done on disk
* merge "debug" tests with non-debug tests, they don't use dbug anyway
* only run rpl password expiration in MIXED mode, it doesn't replicate
  anything, so no need to repeat it thrice
* restore update_user_table_password() prototype, it should not change
  ACL_USER, this is done in acl_user_update()
* don't parse json twice in get_password_lifetime and get_password_expired
* remove LEX_USER::is_changing_password, see if there was any auth instead
* avoid overflow in expiration calculations
* don't initialize Account_options in the constructor, it's bzero-ed later
* don't create ulong sysvars - they're not portable, prefer uint or ulonglong
* misc simplifications

1 files changed, 55 insertions, 3 deletions

diff --git a/mysql-test/main/password_expiration.result b/mysql-test/main/password_expiration.result
index 0f655e4330d..73c436aecd2 100644
--- a/mysql-test/main/password_expiration.result
+++ b/mysql-test/main/password_expiration.result
@@ -141,7 +141,7 @@ ERROR HY000: Incorrect DAY value: '0'
 create user user1@localhost;
 show create user user1@localhost;
 CREATE USER for user1@localhost
-CREATE USER 'user1'@'localhost' PASSWORD EXPIRE NEVER
+CREATE USER 'user1'@'localhost'
 flush privileges;
 show create user user1@localhost;
 CREATE USER for user1@localhost
@@ -158,7 +158,7 @@ set password for user1@localhost= password('');
 alter user user1@localhost password expire default;
 show create user user1@localhost;
 CREATE USER for user1@localhost
-CREATE USER 'user1'@'localhost' PASSWORD EXPIRE NEVER
+CREATE USER 'user1'@'localhost'
 flush privileges;
 show create user user1@localhost;
 CREATE USER for user1@localhost
@@ -174,7 +174,7 @@ CREATE USER 'user1'@'localhost' PASSWORD EXPIRE NEVER
 alter user user1@localhost password expire interval 123 day;
 show create user user1@localhost;
 CREATE USER for user1@localhost
-CREATE USER 'user1'@'localhost' PASSWORD EXPIRE NEVER
+CREATE USER 'user1'@'localhost' PASSWORD EXPIRE INTERVAL 123 DAY
 flush privileges;
 show create user user1@localhost;
 CREATE USER for user1@localhost
@@ -205,3 +205,55 @@ connection default;
 drop user user1@localhost;
 set global disconnect_on_expired_password=default;
 set global default_password_lifetime=default;
+#
+# PASSWORD EXPIRE DEFAULT should use the default_password_lifetime
+# system var to set the number of days till expiration
+#
+set global disconnect_on_expired_password= ON;
+set global default_password_lifetime= 2;
+create user user1@localhost password expire default;
+set @tstamp_expired= UNIX_TIMESTAMP(NOW() - INTERVAL 3 DAY);
+update mysql.global_priv set
+priv=json_set(priv, '$.password_last_changed', @tstamp_expired)
+where user='user1';
+flush privileges;
+connect(localhost,user1,,test,MYSQL_PORT,MYSQL_SOCK);
+connect con1,localhost,user1;
+ERROR HY000: Your password has expired. To log in you must change it using a client that supports expired passwords
+drop user user1@localhost;
+#
+# PASSWORD EXPIRE INTERVAL should expire a client's password after
+# X days and not before
+#
+set global disconnect_on_expired_password= ON;
+create user user1@localhost password expire interval 2 day;
+connect con1,localhost,user1;
+disconnect con1;
+connection default;
+set @tstamp_expired= UNIX_TIMESTAMP(NOW() - INTERVAL 3 DAY);
+update mysql.global_priv set
+priv=json_set(priv, '$.password_last_changed', @tstamp_expired)
+where user='user1';
+flush privileges;
+connect(localhost,user1,,test,MYSQL_PORT,MYSQL_SOCK);
+connect con1,localhost,user1;
+ERROR HY000: Your password has expired. To log in you must change it using a client that supports expired passwords
+drop user user1@localhost;
+#
+# PASSWORD EXPIRE NEVER should override the other policies and never
+# expire a client's password
+#
+set global disconnect_on_expired_password= ON;
+create user user1@localhost password expire interval 2 day;
+alter user user1@localhost password expire never;
+set @tstamp_expired= UNIX_TIMESTAMP() - 3;
+update mysql.global_priv set
+priv=json_set(priv, '$.password_last_changed', @tstamp_expired)
+where user='user1';
+flush privileges;
+connect con1,localhost,user1;
+disconnect con1;
+connection default;
+drop user user1@localhost;
+set global disconnect_on_expired_password= default;
+set global default_password_lifetime= default;