Adding upstream version 1:10.11.6.upstream/1%10.11.6upstream

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 263 insertions, 0 deletions

diff --git a/mysql-test/main/password_expiration.test b/mysql-test/main/password_expiration.test
new file mode 100644
index 00000000..b7d12695
--- /dev/null
+++ b/mysql-test/main/password_expiration.test
@@ -0,0 +1,263 @@
+#
+# Test password expiration
+#
+
+--source include/not_embedded.inc
+
+--echo #
+--echo # Only privileged users should be able to expire passwords
+--echo #
+create user user1@localhost;
+alter user user1@localhost password expire;
+
+create user user2@localhost;
+--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
+connect(con2,localhost,user2);
+connection con2;
+--error ER_SPECIFIC_ACCESS_DENIED_ERROR
+alter user user1@localhost password expire;
+
+disconnect con2;
+connection default;
+drop user user1@localhost;
+drop user user2@localhost;
+
+--echo #
+--echo # disconnect_on_expired_password=ON should deny a clients's connection
+--echo # when the password is expired or put the client in sandbox mode if OFF
+--echo #
+create user user1@localhost password expire;
+set global disconnect_on_expired_password=ON;
+--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
+--error ER_MUST_CHANGE_PASSWORD_LOGIN
+connect(con1,localhost,user1);
+
+# should allow the client to enter sandbox mode
+set global disconnect_on_expired_password=OFF;
+--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
+connect(con1,localhost,user1);
+connection con1;
+--error ER_MUST_CHANGE_PASSWORD
+select 1;
+disconnect con1;
+connection default;
+drop user user1@localhost;
+
+--echo #
+--echo # connect-expired-password option passed to client should override
+--echo # the behavior of disconnect_on_expired_password server system var.
+--echo #
+create user user1@localhost password expire;
+set global disconnect_on_expired_password=ON;
+--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
+--error ER_MUST_CHANGE_PASSWORD_LOGIN
+connect(con1,localhost,user1);
+
+--exec $MYSQL --connect-expired-password -u user1 -e "set password=password('');"
+drop user user1@localhost;
+
+--echo #
+--echo # Manually expiring a password should have immediate effect
+--echo #
+create user user1@localhost;
+alter user user1@localhost password expire;
+set global disconnect_on_expired_password=ON;
+--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
+--error ER_MUST_CHANGE_PASSWORD_LOGIN
+connect(con1,localhost,user1);
+drop user user1@localhost;
+
+--echo #
+--echo # Sandbox mode should only allow change password statements
+--echo #
+create user user1@localhost password expire;
+grant create user on *.* to user1@localhost;
+set global disconnect_on_expired_password=OFF;
+--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
+connect(con1,localhost,user1);
+connection con1;
+--error ER_MUST_CHANGE_PASSWORD
+select 1;
+set password=password('');
+select 1;
+disconnect con1;
+connection default;
+
+drop user user1@localhost;
+
+--echo #
+--echo # Passwords are still expired after acl reload
+--echo #
+set global disconnect_on_expired_password=ON;
+create user user1@localhost password expire;
+flush privileges;
+--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
+--error ER_MUST_CHANGE_PASSWORD_LOGIN
+connect(con1,localhost,user1);
+drop user user1@localhost;
+
+--echo #
+--echo # JSON functions on global_priv reflect the correct state
+--echo # of the password expiration columns
+--echo #
+
+create user user1@localhost password expire;
+select host, user, JSON_VALUE(Priv, '$.password_last_changed') from mysql.global_priv where user='user1';
+alter user user1@localhost password expire never;
+select host, user, JSON_VALUE(Priv, '$.password_lifetime') from mysql.global_priv where user='user1';
+alter user user1@localhost password expire default;
+select host, user, JSON_VALUE(Priv, '$.password_lifetime') from mysql.global_priv where user='user1';
+alter user user1@localhost password expire interval 123 day;
+select host, user, JSON_VALUE(Priv, '$.password_lifetime') from mysql.global_priv where user='user1';
+drop user user1@localhost;
+
+--echo #
+--echo # SHOW CREATE USER correctly displays the locking state of an user
+--echo #
+
+create user user1@localhost;
+show create user user1@localhost;
+alter user user1@localhost password expire;
+show create user user1@localhost;
+set password for user1@localhost= password('');
+alter user user1@localhost password expire default;
+show create user user1@localhost;
+alter user user1@localhost password expire never;
+show create user user1@localhost;
+alter user user1@localhost password expire interval 123 day;
+show create user user1@localhost;
+alter user user1@localhost password expire;
+show create user user1@localhost;
+set password for user1@localhost= password('');
+show create user user1@localhost;
+drop user user1@localhost;
+
+--echo #
+--echo # Incorrect INTERVAL values should be rejected
+--echo #
+--error ER_WRONG_VALUE
+create user user1@localhost password expire interval 0 day;
+
+--echo #
+--echo # Password expiration fields are loaded properly on 10.3 tables
+--echo #
+--source include/switch_to_mysql_user.inc
+create user user1@localhost;
+show create user user1@localhost;
+flush privileges;
+show create user user1@localhost;
+
+alter user user1@localhost password expire;
+show create user user1@localhost;
+flush privileges;
+show create user user1@localhost;
+set password for user1@localhost= password('');
+
+alter user user1@localhost password expire default;
+show create user user1@localhost;
+flush privileges;
+show create user user1@localhost;
+
+alter user user1@localhost password expire never;
+show create user user1@localhost;
+flush privileges;
+show create user user1@localhost;
+
+alter user user1@localhost password expire interval 123 day;
+show create user user1@localhost;
+flush privileges;
+show create user user1@localhost;
+
+alter user user1@localhost password expire;
+show create user user1@localhost;
+flush privileges;
+show create user user1@localhost;
+
+set global disconnect_on_expired_password=ON;
+--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
+--error ER_MUST_CHANGE_PASSWORD_LOGIN
+connect(con1,localhost,user1);
+
+set global disconnect_on_expired_password=OFF;
+--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
+connect(con1,localhost,user1);
+connection con1;
+--error ER_MUST_CHANGE_PASSWORD
+select 1;
+set password=password('');
+select 1;
+disconnect con1;
+connection default;
+drop user user1@localhost;
+
+set global disconnect_on_expired_password=default;
+set global default_password_lifetime=default;
+--source include/switch_to_mysql_global_priv.inc
+
+#
+# Test password expiration INTERVAL and default_password_lifetime options
+#
+
+--echo #
+--echo # PASSWORD EXPIRE DEFAULT should use the default_password_lifetime
+--echo # system var to set the number of days till expiration
+--echo #
+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;
+
+--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
+--error ER_MUST_CHANGE_PASSWORD_LOGIN
+connect(con1,localhost,user1);
+drop user user1@localhost;
+
+--echo #
+--echo # PASSWORD EXPIRE INTERVAL should expire a client's password after
+--echo # X days and not before
+--echo #
+set global disconnect_on_expired_password= ON;
+create user user1@localhost password expire interval 2 day;
+--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
+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;
+
+--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
+--error ER_MUST_CHANGE_PASSWORD_LOGIN
+connect(con1,localhost,user1);
+drop user user1@localhost;
+
+--echo #
+--echo # PASSWORD EXPIRE NEVER should override the other policies and never
+--echo # expire a client's password
+--echo #
+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;
+
+--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
+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;