MDEV-7597 Expiration of user passwords

This patch adds support for expiring user passwords.
The following statements are extended:
  CREATE USER user@localhost PASSWORD EXPIRE [option]
  ALTER USER user@localhost PASSWORD EXPIRE [option]
If no option is specified, the password is expired with immediate
effect. If option is DEFAULT, global policy applies according to
the default_password_lifetime system var (if 0, password never
expires, if N, password expires every N days). If option is NEVER,
the password never expires and if option is INTERVAL N DAY, the
password expires every N days.
The feature also supports the disconnect_on_expired_password system
var and the --connect-expired-password client option.

Closes #1166

2 files changed, 110 insertions, 33 deletions

diff --git a/mysql-test/suite/funcs_1/r/is_user_privileges.result b/mysql-test/suite/funcs_1/r/is_user_privileges.result
index fe181fbf069..28c409019d8 100644
--- a/mysql-test/suite/funcs_1/r/is_user_privileges.result
+++ b/mysql-test/suite/funcs_1/r/is_user_privileges.result
@@ -91,21 +91,27 @@ user	testuser1
 json_detailed(priv)	{
     "access": 0,
     "plugin": "mysql_native_password",
-    "authentication_string": ""
+    "authentication_string": "",
+    "password_last_changed": 0,
+    "password_lifetime": -1
 }
 host	localhost
 user	testuser2
 json_detailed(priv)	{
     "access": 6,
     "plugin": "mysql_native_password",
-    "authentication_string": ""
+    "authentication_string": "",
+    "password_last_changed": 0,
+    "password_lifetime": -1
 }
 host	localhost
 user	testuser3
 json_detailed(priv)	{
     "access": 0,
     "plugin": "mysql_native_password",
-    "authentication_string": ""
+    "authentication_string": "",
+    "password_last_changed": 0,
+    "password_lifetime": -1
 }
 #
 # Add GRANT OPTION db_datadict.* to testuser1;
@@ -136,21 +142,27 @@ user	testuser1
 json_detailed(priv)	{
     "access": 0,
     "plugin": "mysql_native_password",
-    "authentication_string": ""
+    "authentication_string": "",
+    "password_last_changed": 0,
+    "password_lifetime": -1
 }
 host	localhost
 user	testuser2
 json_detailed(priv)	{
     "access": 6,
     "plugin": "mysql_native_password",
-    "authentication_string": ""
+    "authentication_string": "",
+    "password_last_changed": 0,
+    "password_lifetime": -1
 }
 host	localhost
 user	testuser3
 json_detailed(priv)	{
     "access": 0,
     "plugin": "mysql_native_password",
-    "authentication_string": ""
+    "authentication_string": "",
+    "password_last_changed": 0,
+    "password_lifetime": -1
 }
 connect  testuser1, localhost, testuser1, , db_datadict;
 SELECT * FROM information_schema.user_privileges
@@ -167,21 +179,27 @@ user	testuser1
 json_detailed(priv)	{
     "access": 0,
     "plugin": "mysql_native_password",
-    "authentication_string": ""
+    "authentication_string": "",
+    "password_last_changed": 0,
+    "password_lifetime": -1
 }
 host	localhost
 user	testuser2
 json_detailed(priv)	{
     "access": 6,
     "plugin": "mysql_native_password",
-    "authentication_string": ""
+    "authentication_string": "",
+    "password_last_changed": 0,
+    "password_lifetime": -1
 }
 host	localhost
 user	testuser3
 json_detailed(priv)	{
     "access": 0,
     "plugin": "mysql_native_password",
-    "authentication_string": ""
+    "authentication_string": "",
+    "password_last_changed": 0,
+    "password_lifetime": -1
 }
 SHOW GRANTS;
 Grants for testuser1@localhost
@@ -220,21 +238,27 @@ user	testuser1
 json_detailed(priv)	{
     "access": 1,
     "plugin": "mysql_native_password",
-    "authentication_string": ""
+    "authentication_string": "",
+    "password_last_changed": 0,
+    "password_lifetime": -1
 }
 host	localhost
 user	testuser2
 json_detailed(priv)	{
     "access": 6,
     "plugin": "mysql_native_password",
-    "authentication_string": ""
+    "authentication_string": "",
+    "password_last_changed": 0,
+    "password_lifetime": -1
 }
 host	localhost
 user	testuser3
 json_detailed(priv)	{
     "access": 0,
     "plugin": "mysql_native_password",
-    "authentication_string": ""
+    "authentication_string": "",
+    "password_last_changed": 0,
+    "password_lifetime": -1
 }
 GRANT SELECT ON *.* TO 'testuser1'@'localhost' WITH GRANT OPTION;
 #
@@ -265,21 +289,27 @@ user	testuser1
 json_detailed(priv)	{
     "access": 1025,
     "plugin": "mysql_native_password",
-    "authentication_string": ""
+    "authentication_string": "",
+    "password_last_changed": 0,
+    "password_lifetime": -1
 }
 host	localhost
 user	testuser2
 json_detailed(priv)	{
     "access": 6,
     "plugin": "mysql_native_password",
-    "authentication_string": ""
+    "authentication_string": "",
+    "password_last_changed": 0,
+    "password_lifetime": -1
 }
 host	localhost
 user	testuser3
 json_detailed(priv)	{
     "access": 0,
     "plugin": "mysql_native_password",
-    "authentication_string": ""
+    "authentication_string": "",
+    "password_last_changed": 0,
+    "password_lifetime": -1
 }
 connection testuser1;
 SELECT * FROM information_schema.user_privileges
@@ -296,21 +326,27 @@ user	testuser1
 json_detailed(priv)	{
     "access": 1025,
     "plugin": "mysql_native_password",
-    "authentication_string": ""
+    "authentication_string": "",
+    "password_last_changed": 0,
+    "password_lifetime": -1
 }
 host	localhost
 user	testuser2
 json_detailed(priv)	{
     "access": 6,
     "plugin": "mysql_native_password",
-    "authentication_string": ""
+    "authentication_string": "",
+    "password_last_changed": 0,
+    "password_lifetime": -1
 }
 host	localhost
 user	testuser3
 json_detailed(priv)	{
     "access": 0,
     "plugin": "mysql_native_password",
-    "authentication_string": ""
+    "authentication_string": "",
+    "password_last_changed": 0,
+    "password_lifetime": -1
 }
 SHOW GRANTS;
 Grants for testuser1@localhost
@@ -379,21 +415,27 @@ user	testuser1
 json_detailed(priv)	{
     "access": 0,
     "plugin": "mysql_native_password",
-    "authentication_string": ""
+    "authentication_string": "",
+    "password_last_changed": 0,
+    "password_lifetime": -1
 }
 host	localhost
 user	testuser2
 json_detailed(priv)	{
     "access": 6,
     "plugin": "mysql_native_password",
-    "authentication_string": ""
+    "authentication_string": "",
+    "password_last_changed": 0,
+    "password_lifetime": -1
 }
 host	localhost
 user	testuser3
 json_detailed(priv)	{
     "access": 0,
     "plugin": "mysql_native_password",
-    "authentication_string": ""
+    "authentication_string": "",
+    "password_last_changed": 0,
+    "password_lifetime": -1
 }
 connection testuser1;
 SELECT * FROM information_schema.user_privileges
@@ -457,21 +499,27 @@ user	testuser1
 json_detailed(priv)	{
     "access": 0,
     "plugin": "mysql_native_password",
-    "authentication_string": ""
+    "authentication_string": "",
+    "password_last_changed": 0,
+    "password_lifetime": -1
 }
 host	localhost
 user	testuser2
 json_detailed(priv)	{
     "access": 6,
     "plugin": "mysql_native_password",
-    "authentication_string": ""
+    "authentication_string": "",
+    "password_last_changed": 0,
+    "password_lifetime": -1
 }
 host	localhost
 user	testuser3
 json_detailed(priv)	{
     "access": 0,
     "plugin": "mysql_native_password",
-    "authentication_string": ""
+    "authentication_string": "",
+    "password_last_changed": 0,
+    "password_lifetime": -1
 }
 connection testuser1;
 SELECT * FROM information_schema.user_privileges
@@ -488,21 +536,27 @@ user	testuser1
 json_detailed(priv)	{
     "access": 0,
     "plugin": "mysql_native_password",
-    "authentication_string": ""
+    "authentication_string": "",
+    "password_last_changed": 0,
+    "password_lifetime": -1
 }
 host	localhost
 user	testuser2
 json_detailed(priv)	{
     "access": 6,
     "plugin": "mysql_native_password",
-    "authentication_string": ""
+    "authentication_string": "",
+    "password_last_changed": 0,
+    "password_lifetime": -1
 }
 host	localhost
 user	testuser3
 json_detailed(priv)	{
     "access": 0,
     "plugin": "mysql_native_password",
-    "authentication_string": ""
+    "authentication_string": "",
+    "password_last_changed": 0,
+    "password_lifetime": -1
 }
 SHOW GRANTS;
 Grants for testuser1@localhost
@@ -526,21 +580,27 @@ user	testuser1
 json_detailed(priv)	{
     "access": 0,
     "plugin": "mysql_native_password",
-    "authentication_string": ""
+    "authentication_string": "",
+    "password_last_changed": 0,
+    "password_lifetime": -1
 }
 host	localhost
 user	testuser2
 json_detailed(priv)	{
     "access": 6,
     "plugin": "mysql_native_password",
-    "authentication_string": ""
+    "authentication_string": "",
+    "password_last_changed": 0,
+    "password_lifetime": -1
 }
 host	localhost
 user	testuser3
 json_detailed(priv)	{
     "access": 0,
     "plugin": "mysql_native_password",
-    "authentication_string": ""
+    "authentication_string": "",
+    "password_last_changed": 0,
+    "password_lifetime": -1
 }
 SHOW GRANTS;
 Grants for testuser1@localhost
@@ -579,21 +639,27 @@ user	testuser1
 json_detailed(priv)	{
     "access": 0,
     "plugin": "mysql_native_password",
-    "authentication_string": ""
+    "authentication_string": "",
+    "password_last_changed": 0,
+    "password_lifetime": -1
 }
 host	localhost
 user	testuser2
 json_detailed(priv)	{
     "access": 6,
     "plugin": "mysql_native_password",
-    "authentication_string": ""
+    "authentication_string": "",
+    "password_last_changed": 0,
+    "password_lifetime": -1
 }
 host	localhost
 user	testuser3
 json_detailed(priv)	{
     "access": 0,
     "plugin": "mysql_native_password",
-    "authentication_string": ""
+    "authentication_string": "",
+    "password_last_changed": 0,
+    "password_lifetime": -1
 }
 connection testuser1;
 SELECT * FROM information_schema.user_privileges
diff --git a/mysql-test/suite/funcs_1/t/is_user_privileges.test b/mysql-test/suite/funcs_1/t/is_user_privileges.test
index 53d46b83f88..fd62f75e479 100644
--- a/mysql-test/suite/funcs_1/t/is_user_privileges.test
+++ b/mysql-test/suite/funcs_1/t/is_user_privileges.test
@@ -103,6 +103,7 @@ WHERE user LIKE 'testuser%' ORDER BY host, user;
 let $my_show= SHOW GRANTS;
 --vertical_results
 eval $my_select1;
+--replace_regex /password_last_changed": [0-9]*/password_last_changed": 0/
 eval $my_select2;
 --horizontal_results
 
@@ -111,6 +112,7 @@ eval $my_select2;
 GRANT UPDATE ON db_datadict.* TO 'testuser1'@'localhost' WITH GRANT OPTION;
 --vertical_results
 eval $my_select1;
+--replace_regex /password_last_changed": [0-9]*/password_last_changed": 0/
 eval $my_select2;
 --horizontal_results
 
@@ -118,6 +120,7 @@ eval $my_select2;
 connect (testuser1, localhost, testuser1, , db_datadict);
 --vertical_results
 eval $my_select1;
+--replace_regex /password_last_changed": [0-9]*/password_last_changed": 0/
 eval $my_select2;
 --horizontal_results
 eval $my_show;
@@ -131,6 +134,7 @@ GRANT SELECT ON *.* TO 'testuser1'@'localhost';
 --echo # Here <SELECT NO> is shown correctly for testuser1;
 --vertical_results
 eval $my_select1;
+--replace_regex /password_last_changed": [0-9]*/password_last_changed": 0/
 eval $my_select2;
 --horizontal_results
 
@@ -139,6 +143,7 @@ GRANT SELECT ON *.* TO 'testuser1'@'localhost' WITH GRANT OPTION;
 --echo # Here <SELECT YES> is shown correctly for testuser1;
 --vertical_results
 eval $my_select1;
+--replace_regex /password_last_changed": [0-9]*/password_last_changed": 0/
 eval $my_select2;
 --horizontal_results
 
@@ -146,6 +151,7 @@ eval $my_select2;
 connection testuser1;
 --vertical_results
 eval $my_select1;
+--replace_regex /password_last_changed": [0-9]*/password_last_changed": 0/
 eval $my_select2;
 --horizontal_results
 eval $my_show;
@@ -174,6 +180,7 @@ connection default;
 REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'testuser1'@'localhost';
 --vertical_results
 eval $my_select1;
+--replace_regex /password_last_changed": [0-9]*/password_last_changed": 0/
 eval $my_select2;
 --horizontal_results
 
@@ -206,12 +213,14 @@ GRANT ALL ON db_datadict.* TO 'testuser1'@'localhost' WITH GRANT OPTION;
 GRANT SELECT ON mysql.global_priv TO 'testuser1'@'localhost';
 --vertical_results
 eval $my_select1;
+--replace_regex /password_last_changed": [0-9]*/password_last_changed": 0/
 eval $my_select2;
 --horizontal_results
 
 connection testuser1;
 --vertical_results
 eval $my_select1;
+--replace_regex /password_last_changed": [0-9]*/password_last_changed": 0/
 eval $my_select2;
 --horizontal_results
 eval $my_show;
@@ -224,6 +233,7 @@ CREATE TABLE db_datadict.tb_56 ( c1 TEXT );
 USE db_datadict;
 --vertical_results
 eval $my_select1;
+--replace_regex /password_last_changed": [0-9]*/password_last_changed": 0/
 eval $my_select2;
 --horizontal_results
 eval $my_show;
@@ -238,6 +248,7 @@ connection default;
 REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'testuser1'@'localhost';
 --vertical_results
 eval $my_select1;
+--replace_regex /password_last_changed": [0-9]*/password_last_changed": 0/
 eval $my_select2;
 --horizontal_results