diff options
Diffstat (limited to 'mysql-test/main/password_expiration.result')
| -rw-r--r-- | mysql-test/main/password_expiration.result | 266 |
1 files changed, 266 insertions, 0 deletions
diff --git a/mysql-test/main/password_expiration.result b/mysql-test/main/password_expiration.result new file mode 100644 index 00000000..897811bb --- /dev/null +++ b/mysql-test/main/password_expiration.result @@ -0,0 +1,266 @@ +# +# Only privileged users should be able to expire passwords +# +create user user1@localhost; +alter user user1@localhost password expire; +create user user2@localhost; +connect con2,localhost,user2; +connection con2; +alter user user1@localhost password expire; +ERROR 42000: Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation +disconnect con2; +connection default; +drop user user1@localhost; +drop user user2@localhost; +# +# disconnect_on_expired_password=ON should deny a clients's connection +# when the password is expired or put the client in sandbox mode if OFF +# +create user user1@localhost password expire; +set global disconnect_on_expired_password=ON; +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 +set global disconnect_on_expired_password=OFF; +connect con1,localhost,user1; +connection con1; +select 1; +ERROR HY000: You must SET PASSWORD before executing this statement +disconnect con1; +connection default; +drop user user1@localhost; +# +# connect-expired-password option passed to client should override +# the behavior of disconnect_on_expired_password server system var. +# +create user user1@localhost password expire; +set global disconnect_on_expired_password=ON; +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; +# +# Manually expiring a password should have immediate effect +# +create user user1@localhost; +alter user user1@localhost password expire; +set global disconnect_on_expired_password=ON; +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; +# +# Sandbox mode should only allow change password statements +# +create user user1@localhost password expire; +grant create user on *.* to user1@localhost; +set global disconnect_on_expired_password=OFF; +connect con1,localhost,user1; +connection con1; +select 1; +ERROR HY000: You must SET PASSWORD before executing this statement +set password=password(''); +select 1; +1 +1 +disconnect con1; +connection default; +drop user user1@localhost; +# +# Passwords are still expired after acl reload +# +set global disconnect_on_expired_password=ON; +create user user1@localhost password expire; +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; +# +# JSON functions on global_priv reflect the correct state +# of the password expiration columns +# +create user user1@localhost password expire; +select host, user, JSON_VALUE(Priv, '$.password_last_changed') from mysql.global_priv where user='user1'; +host user JSON_VALUE(Priv, '$.password_last_changed') +localhost user1 0 +alter user user1@localhost password expire never; +select host, user, JSON_VALUE(Priv, '$.password_lifetime') from mysql.global_priv where user='user1'; +host user JSON_VALUE(Priv, '$.password_lifetime') +localhost user1 0 +alter user user1@localhost password expire default; +select host, user, JSON_VALUE(Priv, '$.password_lifetime') from mysql.global_priv where user='user1'; +host user JSON_VALUE(Priv, '$.password_lifetime') +localhost user1 -1 +alter user user1@localhost password expire interval 123 day; +select host, user, JSON_VALUE(Priv, '$.password_lifetime') from mysql.global_priv where user='user1'; +host user JSON_VALUE(Priv, '$.password_lifetime') +localhost user1 123 +drop user user1@localhost; +# +# SHOW CREATE USER correctly displays the locking state of an user +# +create user user1@localhost; +show create user user1@localhost; +CREATE USER for user1@localhost +CREATE USER `user1`@`localhost` +alter user user1@localhost password expire; +show create user user1@localhost; +CREATE USER for user1@localhost +CREATE USER `user1`@`localhost` PASSWORD EXPIRE +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` +alter user user1@localhost password expire never; +show create user user1@localhost; +CREATE USER for user1@localhost +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 INTERVAL 123 DAY +alter user user1@localhost password expire; +show create user user1@localhost; +CREATE USER for user1@localhost +CREATE USER `user1`@`localhost` PASSWORD EXPIRE +ALTER USER `user1`@`localhost` PASSWORD EXPIRE INTERVAL 123 DAY +set password for user1@localhost= password(''); +show create user user1@localhost; +CREATE USER for user1@localhost +CREATE USER `user1`@`localhost` PASSWORD EXPIRE INTERVAL 123 DAY +drop user user1@localhost; +# +# Incorrect INTERVAL values should be rejected +# +create user user1@localhost password expire interval 0 day; +ERROR HY000: Incorrect DAY value: '0' +# +# Password expiration fields are loaded properly on 10.3 tables +# +# switching from mysql.global_priv to mysql.user +create user user1@localhost; +show create user user1@localhost; +CREATE USER for user1@localhost +CREATE USER `user1`@`localhost` +flush privileges; +show create user user1@localhost; +CREATE USER for user1@localhost +CREATE USER `user1`@`localhost` PASSWORD EXPIRE NEVER +alter user user1@localhost password expire; +show create user user1@localhost; +CREATE USER for user1@localhost +CREATE USER `user1`@`localhost` PASSWORD EXPIRE +ALTER USER `user1`@`localhost` PASSWORD EXPIRE NEVER +flush privileges; +show create user user1@localhost; +CREATE USER for user1@localhost +CREATE USER `user1`@`localhost` PASSWORD EXPIRE +ALTER USER `user1`@`localhost` PASSWORD EXPIRE NEVER +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` +flush privileges; +show create user user1@localhost; +CREATE USER for user1@localhost +CREATE USER `user1`@`localhost` PASSWORD EXPIRE NEVER +alter user user1@localhost password expire never; +show create user user1@localhost; +CREATE USER for user1@localhost +CREATE USER `user1`@`localhost` PASSWORD EXPIRE NEVER +flush privileges; +show create user user1@localhost; +CREATE USER for user1@localhost +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 INTERVAL 123 DAY +flush privileges; +show create user user1@localhost; +CREATE USER for user1@localhost +CREATE USER `user1`@`localhost` PASSWORD EXPIRE NEVER +alter user user1@localhost password expire; +show create user user1@localhost; +CREATE USER for user1@localhost +CREATE USER `user1`@`localhost` PASSWORD EXPIRE +ALTER USER `user1`@`localhost` PASSWORD EXPIRE NEVER +flush privileges; +show create user user1@localhost; +CREATE USER for user1@localhost +CREATE USER `user1`@`localhost` PASSWORD EXPIRE +ALTER USER `user1`@`localhost` PASSWORD EXPIRE NEVER +set global disconnect_on_expired_password=ON; +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 +set global disconnect_on_expired_password=OFF; +connect con1,localhost,user1; +connection con1; +select 1; +ERROR HY000: You must SET PASSWORD before executing this statement +set password=password(''); +select 1; +1 +1 +disconnect con1; +connection default; +drop user user1@localhost; +set global disconnect_on_expired_password=default; +set global default_password_lifetime=default; +# switching back from mysql.user to mysql.global_priv +# +# 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; |