diff options
Diffstat (limited to 'mysql-test')
| -rw-r--r-- | mysql-test/main/mysql_upgrade.result | 4 | ||||
| -rw-r--r-- | mysql-test/main/mysqld--help.result | 13 | ||||
| -rw-r--r-- | mysql-test/main/password_expiration.result | 207 | ||||
| -rw-r--r-- | mysql-test/main/password_expiration.test | 196 | ||||
| -rw-r--r-- | mysql-test/main/password_expiration_dbug.result | 55 | ||||
| -rw-r--r-- | mysql-test/main/password_expiration_dbug.test | 75 | ||||
| -rw-r--r-- | mysql-test/main/rpl_expired_pass.result | 28 | ||||
| -rw-r--r-- | mysql-test/main/rpl_expired_pass.test | 52 | ||||
| -rw-r--r-- | mysql-test/main/system_mysql_db_507.result | 33 | ||||
| -rw-r--r-- | mysql-test/main/system_mysql_db_507.test | 20 | ||||
| -rw-r--r-- | mysql-test/suite/funcs_1/r/is_user_privileges.result | 132 | ||||
| -rw-r--r-- | mysql-test/suite/funcs_1/t/is_user_privileges.test | 11 | ||||
| -rw-r--r-- | mysql-test/suite/plugins/r/multiauth.result | 4 | ||||
| -rw-r--r-- | mysql-test/suite/plugins/t/multiauth.test | 1 | ||||
| -rw-r--r-- | mysql-test/suite/sys_vars/r/sysvars_server_embedded.result | 28 | ||||
| -rw-r--r-- | mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result | 28 |
16 files changed, 851 insertions, 36 deletions
diff --git a/mysql-test/main/mysql_upgrade.result b/mysql-test/main/mysql_upgrade.result index 9fcf76d2b1c..c1dcdd1a1cb 100644 --- a/mysql-test/main/mysql_upgrade.result +++ b/mysql-test/main/mysql_upgrade.result @@ -596,7 +596,7 @@ drop view mysql.user_bak; create user 'user3'@'localhost' identified with mysql_native_password as password('a_password'); show create user user3@localhost; CREATE USER for user3@localhost -CREATE USER 'user3'@'localhost' IDENTIFIED BY PASSWORD '*5DC1D11F45824A9DD613961F05C1EC1E7A1601AA' +CREATE USER 'user3'@'localhost' IDENTIFIED BY PASSWORD '*5DC1D11F45824A9DD613961F05C1EC1E7A1601AA' PASSWORD EXPIRE NEVER update mysql.user set password=authentication_string, authentication_string='' where user='user3'; select password,plugin,authentication_string from mysql.user where user='user3'; password plugin authentication_string @@ -604,7 +604,7 @@ password plugin authentication_string flush privileges; show create user user3@localhost; CREATE USER for user3@localhost -CREATE USER 'user3'@'localhost' IDENTIFIED BY PASSWORD '*5DC1D11F45824A9DD613961F05C1EC1E7A1601AA' +CREATE USER 'user3'@'localhost' IDENTIFIED BY PASSWORD '*5DC1D11F45824A9DD613961F05C1EC1E7A1601AA' PASSWORD EXPIRE NEVER connect con1,localhost,user3,a_password; select current_user(); current_user() diff --git a/mysql-test/main/mysqld--help.result b/mysql-test/main/mysqld--help.result index bff1696d3d9..1f1d80f5460 100644 --- a/mysql-test/main/mysqld--help.result +++ b/mysql-test/main/mysqld--help.result @@ -188,6 +188,12 @@ The following specify which files/extra groups are read (specified before remain --deadlock-timeout-short=# Short timeout for the two-step deadlock detection (in microseconds) + --default-password-lifetime=# + This defines the global password expiration policy. 0 + means automatic password expiration is disabled. If the + value is a positive integer N, the passwords must be + changed every N days. This behavior can be overriden + using the password expiration options in ALTER USER. --default-regex-flags=name Default flags for the regex library. Any combination of: DOTALL, DUPNAMES, EXTENDED, EXTRA, MULTILINE, UNGREEDY @@ -224,6 +230,11 @@ The following specify which files/extra groups are read (specified before remain handling INSERT DELAYED. If the queue becomes full, any client that does INSERT DELAYED will wait until there is room in the queue again + --disconnect-on-expired-password + This variable controls how the server handles clients + that are not aware of the sandbox mode. If enabled, the + server disconnects the client, otherwise the server puts + the client in a sandbox mode. --div-precision-increment=# Precision of the result of '/' operator will be increased on that value @@ -1428,6 +1439,7 @@ deadlock-search-depth-long 15 deadlock-search-depth-short 4 deadlock-timeout-long 50000000 deadlock-timeout-short 10000 +default-password-lifetime 0 default-regex-flags default-storage-engine myisam default-time-zone (No default value) @@ -1437,6 +1449,7 @@ delay-key-write ON delayed-insert-limit 100 delayed-insert-timeout 300 delayed-queue-size 1000 +disconnect-on-expired-password FALSE div-precision-increment 4 encrypt-binlog FALSE encrypt-tmp-disk-tables FALSE diff --git a/mysql-test/main/password_expiration.result b/mysql-test/main/password_expiration.result new file mode 100644 index 00000000000..0f655e4330d --- /dev/null +++ b/mysql-test/main/password_expiration.result @@ -0,0 +1,207 @@ +# +# 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 +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 +# +create user user1@localhost; +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; +show create user user1@localhost; +CREATE USER for user1@localhost +CREATE USER 'user1'@'localhost' PASSWORD EXPIRE +flush privileges; +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' 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 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 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; +show create user user1@localhost; +CREATE USER for user1@localhost +CREATE USER 'user1'@'localhost' PASSWORD EXPIRE +flush privileges; +show create user user1@localhost; +CREATE USER for user1@localhost +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 +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; diff --git a/mysql-test/main/password_expiration.test b/mysql-test/main/password_expiration.test new file mode 100644 index 00000000000..2975da89b9d --- /dev/null +++ b/mysql-test/main/password_expiration.test @@ -0,0 +1,196 @@ +# +# 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 + diff --git a/mysql-test/main/password_expiration_dbug.result b/mysql-test/main/password_expiration_dbug.result new file mode 100644 index 00000000000..d847d687a4b --- /dev/null +++ b/mysql-test/main/password_expiration_dbug.result @@ -0,0 +1,55 @@ +set @old_dbug= @@global.debug_dbug; +set global debug_dbug= "+d,password_expiration_interval_sec"; +# +# 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() - 3; +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 seconds 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() - 3; +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 debug_dbug= @old_dbug; +set global disconnect_on_expired_password= default; +set global default_password_lifetime= default; diff --git a/mysql-test/main/password_expiration_dbug.test b/mysql-test/main/password_expiration_dbug.test new file mode 100644 index 00000000000..01d67ce5f85 --- /dev/null +++ b/mysql-test/main/password_expiration_dbug.test @@ -0,0 +1,75 @@ +# +# Test password expiration INTERVAL and default_password_lifetime options +# + +--source include/have_debug.inc +--source include/not_embedded.inc + +set @old_dbug= @@global.debug_dbug; +set global debug_dbug= "+d,password_expiration_interval_sec"; + +--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() - 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 +--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 seconds 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() - 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 +--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 debug_dbug= @old_dbug; +set global disconnect_on_expired_password= default; +set global default_password_lifetime= default; + diff --git a/mysql-test/main/rpl_expired_pass.result b/mysql-test/main/rpl_expired_pass.result new file mode 100644 index 00000000000..13fc11a3b96 --- /dev/null +++ b/mysql-test/main/rpl_expired_pass.result @@ -0,0 +1,28 @@ +include/master-slave.inc +[connection master] +connection slave; +include/stop_slave.inc +connection master; +create user 'repl_user' password expire; +grant replication slave on *.* to repl_user; +flush privileges; +set global disconnect_on_expired_password=ON; +connection slave; +CHANGE MASTER TO MASTER_USER= 'repl_user'; +START SLAVE; +include/wait_for_slave_io_error.inc [errno=1862] +include/stop_slave_sql.inc +RESET SLAVE; +connection master; +set global disconnect_on_expired_password=OFF; +connection slave; +START SLAVE; +include/wait_for_slave_io_error.inc [errno=1820] +connection master; +DROP USER 'repl_user'; +set global disconnect_on_expired_password=default; +connection slave; +include/stop_slave_sql.inc +CHANGE MASTER TO MASTER_USER='root'; +RESET SLAVE; +include/rpl_end.inc diff --git a/mysql-test/main/rpl_expired_pass.test b/mysql-test/main/rpl_expired_pass.test new file mode 100644 index 00000000000..3aa56058098 --- /dev/null +++ b/mysql-test/main/rpl_expired_pass.test @@ -0,0 +1,52 @@ +# +# Test a slave connection is properly handled when the replication +# user has an expired password +# + +--source include/not_embedded.inc +--source include/master-slave.inc + +--connection slave +--source include/stop_slave.inc + +--connection master +create user 'repl_user' password expire; +grant replication slave on *.* to repl_user; +flush privileges; +set global disconnect_on_expired_password=ON; + +--connection slave +--let $master_user= query_get_value(SHOW SLAVE STATUS, Master_User, 1) +CHANGE MASTER TO MASTER_USER= 'repl_user'; + +START SLAVE; +# ER_MUST_CHANGE_PASSWORD_LOGIN +--let $slave_io_errno= 1862 +--source include/wait_for_slave_io_error.inc + +# restart slave +--source include/stop_slave_sql.inc +RESET SLAVE; + +--connection master +# force sandbox mode for repl_user +set global disconnect_on_expired_password=OFF; + +--connection slave +START SLAVE; +# ER_MUST_CHANGE_PASSWORD +--let $slave_io_errno= 1820 +--source include/wait_for_slave_io_error.inc + +--connection master +DROP USER 'repl_user'; +set global disconnect_on_expired_password=default; + +--connection slave +--source include/stop_slave_sql.inc +eval CHANGE MASTER TO MASTER_USER='$master_user'; +RESET SLAVE; + +--let $rpl_only_running_threads= 1 +--source include/rpl_end.inc + diff --git a/mysql-test/main/system_mysql_db_507.result b/mysql-test/main/system_mysql_db_507.result index bf4d3115da5..767b8fee102 100644 --- a/mysql-test/main/system_mysql_db_507.result +++ b/mysql-test/main/system_mysql_db_507.result @@ -186,5 +186,38 @@ show create user user1@localhost; CREATE USER for user1@localhost CREATE USER 'user1'@'localhost' # +# Test password expiration fields are loaded correctly +# +create user user@localhost; +show create user user@localhost; +CREATE USER for user@localhost +CREATE USER 'user'@'localhost' +alter user user@localhost password expire; +show create user user@localhost; +CREATE USER for user@localhost +CREATE USER 'user'@'localhost' PASSWORD EXPIRE +set password for user@localhost= password(''); +alter user user@localhost password expire default; +show create user user@localhost; +CREATE USER for user@localhost +CREATE USER 'user'@'localhost' +alter user user@localhost password expire never; +show create user user@localhost; +CREATE USER for user@localhost +CREATE USER 'user'@'localhost' PASSWORD EXPIRE NEVER +alter user user@localhost password expire interval 123 day; +show create user user@localhost; +CREATE USER for user@localhost +CREATE USER 'user'@'localhost' PASSWORD EXPIRE INTERVAL 123 DAY +alter user user@localhost password expire; +show create user user@localhost; +CREATE USER for user@localhost +CREATE USER 'user'@'localhost' PASSWORD EXPIRE +set password for user@localhost= password(''); +show create user user@localhost; +CREATE USER for user@localhost +CREATE USER 'user'@'localhost' PASSWORD EXPIRE INTERVAL 123 DAY +drop user user@localhost; +# # Reset to final original state. # diff --git a/mysql-test/main/system_mysql_db_507.test b/mysql-test/main/system_mysql_db_507.test index bb8163f6ebe..cfefcdc602e 100644 --- a/mysql-test/main/system_mysql_db_507.test +++ b/mysql-test/main/system_mysql_db_507.test @@ -107,6 +107,26 @@ connection default; show create user user1@localhost; --echo # +--echo # Test password expiration fields are loaded correctly +--echo # +create user user@localhost; +show create user user@localhost; +alter user user@localhost password expire; +show create user user@localhost; +set password for user@localhost= password(''); +alter user user@localhost password expire default; +show create user user@localhost; +alter user user@localhost password expire never; +show create user user@localhost; +alter user user@localhost password expire interval 123 day; +show create user user@localhost; +alter user user@localhost password expire; +show create user user@localhost; +set password for user@localhost= password(''); +show create user user@localhost; +drop user user@localhost; + +--echo # --echo # Reset to final original state. --echo # --source include/switch_to_mysql_global_priv.inc 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 diff --git a/mysql-test/suite/plugins/r/multiauth.result b/mysql-test/suite/plugins/r/multiauth.result index 2fbea2ca0ee..98f58c7f893 100644 --- a/mysql-test/suite/plugins/r/multiauth.result +++ b/mysql-test/suite/plugins/r/multiauth.result @@ -122,7 +122,9 @@ json_detailed(priv) { } - ] + ], + "password_last_changed": 0, + "password_lifetime": -1 } select password,plugin,authentication_string from mysql.user where user='mysqltest1'; Password plugin authentication_string diff --git a/mysql-test/suite/plugins/t/multiauth.test b/mysql-test/suite/plugins/t/multiauth.test index 166ae30d3c7..46af3f1388b 100644 --- a/mysql-test/suite/plugins/t/multiauth.test +++ b/mysql-test/suite/plugins/t/multiauth.test @@ -130,6 +130,7 @@ drop user mysqltest1; # create user mysqltest1 identified via ed25519 as password("good") OR unix_socket OR mysql_native_password as password("works"); show grants for mysqltest1; +--replace_regex /password_last_changed": [0-9]*/password_last_changed": 0/ select json_detailed(priv) from mysql.global_priv where user='mysqltest1'; select password,plugin,authentication_string from mysql.user where user='mysqltest1'; flush privileges; diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result index 816adba7e59..1d8d143eee7 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result +++ b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result @@ -712,6 +712,20 @@ NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED +VARIABLE_NAME DEFAULT_PASSWORD_LIFETIME +SESSION_VALUE NULL +GLOBAL_VALUE 0 +GLOBAL_VALUE_ORIGIN COMPILE-TIME +DEFAULT_VALUE 0 +VARIABLE_SCOPE GLOBAL +VARIABLE_TYPE BIGINT UNSIGNED +VARIABLE_COMMENT This defines the global password expiration policy. 0 means automatic password expiration is disabled. If the value is a positive integer N, the passwords must be changed every N days. This behavior can be overriden using the password expiration options in ALTER USER. +NUMERIC_MIN_VALUE 0 +NUMERIC_MAX_VALUE 4294967295 +NUMERIC_BLOCK_SIZE 1 +ENUM_VALUE_LIST NULL +READ_ONLY NO +COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME DEFAULT_REGEX_FLAGS SESSION_VALUE GLOBAL_VALUE @@ -824,6 +838,20 @@ NUMERIC_BLOCK_SIZE NULL ENUM_VALUE_LIST OFF,ON,ALL READ_ONLY NO COMMAND_LINE_ARGUMENT OPTIONAL +VARIABLE_NAME DISCONNECT_ON_EXPIRED_PASSWORD +SESSION_VALUE NULL +GLOBAL_VALUE OFF +GLOBAL_VALUE_ORIGIN COMPILE-TIME +DEFAULT_VALUE OFF +VARIABLE_SCOPE GLOBAL +VARIABLE_TYPE BOOLEAN +VARIABLE_COMMENT This variable controls how the server handles clients that are not aware of the sandbox mode. If enabled, the server disconnects the client, otherwise the server puts the client in a sandbox mode. +NUMERIC_MIN_VALUE NULL +NUMERIC_MAX_VALUE NULL +NUMERIC_BLOCK_SIZE NULL +ENUM_VALUE_LIST OFF,ON +READ_ONLY NO +COMMAND_LINE_ARGUMENT OPTIONAL VARIABLE_NAME DIV_PRECISION_INCREMENT SESSION_VALUE 4 GLOBAL_VALUE 5 diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result index ea0f569ab1b..13ebd8d3822 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result +++ b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result @@ -726,6 +726,20 @@ NUMERIC_BLOCK_SIZE NULL ENUM_VALUE_LIST NULL READ_ONLY NO COMMAND_LINE_ARGUMENT NULL +VARIABLE_NAME DEFAULT_PASSWORD_LIFETIME +SESSION_VALUE NULL +GLOBAL_VALUE 0 +GLOBAL_VALUE_ORIGIN COMPILE-TIME +DEFAULT_VALUE 0 +VARIABLE_SCOPE GLOBAL +VARIABLE_TYPE BIGINT UNSIGNED +VARIABLE_COMMENT This defines the global password expiration policy. 0 means automatic password expiration is disabled. If the value is a positive integer N, the passwords must be changed every N days. This behavior can be overriden using the password expiration options in ALTER USER. +NUMERIC_MIN_VALUE 0 +NUMERIC_MAX_VALUE 4294967295 +NUMERIC_BLOCK_SIZE 1 +ENUM_VALUE_LIST NULL +READ_ONLY NO +COMMAND_LINE_ARGUMENT REQUIRED VARIABLE_NAME DEFAULT_REGEX_FLAGS SESSION_VALUE GLOBAL_VALUE @@ -838,6 +852,20 @@ NUMERIC_BLOCK_SIZE NULL ENUM_VALUE_LIST OFF,ON,ALL READ_ONLY NO COMMAND_LINE_ARGUMENT OPTIONAL +VARIABLE_NAME DISCONNECT_ON_EXPIRED_PASSWORD +SESSION_VALUE NULL +GLOBAL_VALUE OFF +GLOBAL_VALUE_ORIGIN COMPILE-TIME +DEFAULT_VALUE OFF +VARIABLE_SCOPE GLOBAL +VARIABLE_TYPE BOOLEAN +VARIABLE_COMMENT This variable controls how the server handles clients that are not aware of the sandbox mode. If enabled, the server disconnects the client, otherwise the server puts the client in a sandbox mode. +NUMERIC_MIN_VALUE NULL +NUMERIC_MAX_VALUE NULL +NUMERIC_BLOCK_SIZE NULL +ENUM_VALUE_LIST OFF,ON +READ_ONLY NO +COMMAND_LINE_ARGUMENT OPTIONAL VARIABLE_NAME DIV_PRECISION_INCREMENT SESSION_VALUE 4 GLOBAL_VALUE 5 |