diff options
Diffstat (limited to 'mysql-test/main/password_expiration.test')
| -rw-r--r-- | mysql-test/main/password_expiration.test | 263 |
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 00000000000..b7d1269537a --- /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; |