diff options
| author | Robert Bindar <robert@mariadb.org> | 2019-01-16 19:44:30 +0200 |
|---|---|---|
| committer | Sergei Golubchik <serg@mariadb.org> | 2019-02-21 15:04:03 +0100 |
| commit | 90ad4dbd17a44c64cfaf8cb81588d3f999efd40b (patch) | |
| tree | b6e40bca750c251a563999fc19b8510bb836e605 | |
| parent | 83de75d66dc40fedc4cb762584eed3e0121609bd (diff) | |
| download | mariadb-git-90ad4dbd17a44c64cfaf8cb81588d3f999efd40b.tar.gz | |
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
34 files changed, 1259 insertions, 99 deletions
diff --git a/client/mysql.cc b/client/mysql.cc index a20a1665071..f3125c1eb42 100644 --- a/client/mysql.cc +++ b/client/mysql.cc @@ -160,6 +160,7 @@ static uint my_end_arg; static char * opt_mysql_unix_port=0; static int connect_flag=CLIENT_INTERACTIVE; static my_bool opt_binary_mode= FALSE; +static my_bool opt_connect_expired_password= FALSE; static int interrupted_query= 0; static char *current_host,*current_db,*current_user=0,*opt_password=0, *current_prompt=0, *delimiter_str= 0, @@ -1686,6 +1687,11 @@ static struct my_option my_long_options[] = "piped to mysql or loaded using the 'source' command). This is necessary " "when processing output from mysqlbinlog that may contain blobs.", &opt_binary_mode, &opt_binary_mode, 0, GET_BOOL, NO_ARG, 0, 0, 0, 0, 0, 0}, + {"connect-expired-password", 0, + "Notify the server that this client is prepared to handle expired " + "password sandbox mode even if --batch was specified.", + &opt_connect_expired_password, &opt_connect_expired_password, 0, GET_BOOL, + NO_ARG, 0, 0, 0, 0, 0, 0}, { 0, 0, 0, 0, 0, 0, GET_NO_ARG, NO_ARG, 0, 0, 0, 0, 0, 0} }; @@ -4684,6 +4690,9 @@ sql_real_connect(char *host,char *database,char *user,char *password, mysql_options(&mysql, MYSQL_SET_CHARSET_NAME, default_charset); + my_bool can_handle_expired= opt_connect_expired_password || !status.batch; + mysql_options(&mysql, MYSQL_OPT_CAN_HANDLE_EXPIRED_PASSWORDS, &can_handle_expired); + if (!do_connect(&mysql, host, user, password, database, connect_flag | CLIENT_MULTI_STATEMENTS)) { diff --git a/include/mysql_com.h b/include/mysql_com.h index 8b0847ab399..3135a623efb 100644 --- a/include/mysql_com.h +++ b/include/mysql_com.h @@ -333,12 +333,8 @@ enum enum_indicator_type CLIENT_DEPRECATE_EOF |\ CLIENT_CONNECT_ATTRS |\ MARIADB_CLIENT_COM_MULTI |\ - MARIADB_CLIENT_STMT_BULK_OPERATIONS) - -/* - To be added later: - CLIENT_CAN_HANDLE_EXPIRED_PASSWORDS -*/ + MARIADB_CLIENT_STMT_BULK_OPERATIONS |\ + CLIENT_CAN_HANDLE_EXPIRED_PASSWORDS) /* Switch off the flags that are optional and depending on build flags 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 diff --git a/scripts/mysql_system_tables_fix.sql b/scripts/mysql_system_tables_fix.sql index 381f5356575..20198f60f17 100644 --- a/scripts/mysql_system_tables_fix.sql +++ b/scripts/mysql_system_tables_fix.sql @@ -643,7 +643,9 @@ ALTER TABLE user ADD plugin char(64) CHARACTER SET latin1 DEFAULT '' NOT NULL, ALTER TABLE user MODIFY plugin char(64) CHARACTER SET latin1 DEFAULT '' NOT NULL, MODIFY authentication_string TEXT NOT NULL; ALTER TABLE user ADD password_expired ENUM('N', 'Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL; -ALTER TABLE user ADD account_locked enum('N', 'Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL after password_expired; +ALTER TABLE user ADD password_last_changed timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL after password_expired; +ALTER TABLE user ADD password_lifetime smallint unsigned DEFAULT NULL after password_last_changed; +ALTER TABLE user ADD account_locked enum('N', 'Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL after password_lifetime; ALTER TABLE user ADD is_role enum('N', 'Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL; ALTER TABLE user ADD default_role char(80) binary DEFAULT '' NOT NULL; ALTER TABLE user ADD max_statement_time decimal(12,6) DEFAULT 0 NOT NULL; @@ -805,6 +807,8 @@ IF 'BASE TABLE' = (select table_type from information_schema.tables where table_ 'max_statement_time', max_statement_time, 'plugin', if(plugin>'',plugin,if(length(password)=16,'mysql_old_password','mysql_native_password')), 'authentication_string', if(plugin>'' and authentication_string>'',authentication_string,password), + 'password_last_changed', if(password_expired='Y', 0, UNIX_TIMESTAMP(password_last_changed)), + 'password_lifetime', ifnull(password_lifetime, -1), 'account_locked', 'Y'=account_locked, 'default_role', default_role, 'is_role', 'Y'=is_role)) as Priv diff --git a/sql/lex.h b/sql/lex.h index 044e21a81f3..bb57f308475 100644 --- a/sql/lex.h +++ b/sql/lex.h @@ -231,6 +231,7 @@ static SYMBOL symbols[] = { { "EXISTS", SYM(EXISTS)}, { "EXIT", SYM(EXIT_MARIADB_SYM)}, { "EXPANSION", SYM(EXPANSION_SYM)}, + { "EXPIRE", SYM(EXPIRE_SYM)}, { "EXPORT", SYM(EXPORT_SYM)}, { "EXPLAIN", SYM(DESCRIBE)}, { "EXTENDED", SYM(EXTENDED_SYM)}, @@ -419,6 +420,7 @@ static SYMBOL symbols[] = { { "NATIONAL", SYM(NATIONAL_SYM)}, { "NATURAL", SYM(NATURAL)}, { "NCHAR", SYM(NCHAR_SYM)}, + { "NEVER", SYM(NEVER_SYM)}, { "NEW", SYM(NEW_SYM)}, { "NEXT", SYM(NEXT_SYM)}, { "NEXTVAL", SYM(NEXTVAL_SYM)}, diff --git a/sql/mysqld.cc b/sql/mysqld.cc index b43f0772602..f47b2464509 100644 --- a/sql/mysqld.cc +++ b/sql/mysqld.cc @@ -511,6 +511,8 @@ ulong feature_files_opened_with_delayed_keys= 0, feature_check_constraint= 0; ulonglong denied_connections; my_decimal decimal_zero; long opt_secure_timestamp; +ulong default_password_lifetime; +my_bool disconnect_on_expired_password; /* Maximum length of parameter value which can be set through @@ -7060,7 +7062,6 @@ struct my_option my_long_options[]= MYSQL_COMPATIBILITY_OPTION("slave-checkpoint-period"), // HAVE_REPLICATION MYSQL_COMPATIBILITY_OPTION("slave-checkpoint-group"), // HAVE_REPLICATION MYSQL_SUGGEST_ANALOG_OPTION("slave-pending-jobs-size-max", "--slave-parallel-max-queued"), // HAVE_REPLICATION - MYSQL_TO_BE_IMPLEMENTED_OPTION("disconnect-on-expired-password"), MYSQL_TO_BE_IMPLEMENTED_OPTION("sha256-password-private-key-path"), // HAVE_OPENSSL && !HAVE_YASSL MYSQL_TO_BE_IMPLEMENTED_OPTION("sha256-password-public-key-path"), // HAVE_OPENSSL && !HAVE_YASSL diff --git a/sql/mysqld.h b/sql/mysqld.h index a05b4726c64..89fa686a37d 100644 --- a/sql/mysqld.h +++ b/sql/mysqld.h @@ -310,6 +310,8 @@ extern my_bool encrypt_tmp_disk_tables, encrypt_tmp_files; extern ulong encryption_algorithm; extern const char *encryption_algorithm_names[]; extern long opt_secure_timestamp; +extern ulong default_password_lifetime; +extern my_bool disconnect_on_expired_password; enum secure_timestamp { SECTIME_NO, SECTIME_SUPER, SECTIME_REPL, SECTIME_YES }; diff --git a/sql/share/errmsg-utf8.txt b/sql/share/errmsg-utf8.txt index d79f3aa12c2..990eeed3ce3 100644 --- a/sql/share/errmsg-utf8.txt +++ b/sql/share/errmsg-utf8.txt @@ -6942,6 +6942,7 @@ ER_NOT_VALID_PASSWORD ER_MUST_CHANGE_PASSWORD eng "You must SET PASSWORD before executing this statement" bgn "Трябва първо да си смените паролата със SET PASSWORD за да можете да изпълните тази команда" + rum "Trebuie sa iti schimbi parola folosind SET PASSWORD inainte de a executa aceasta comanda" ER_FK_NO_INDEX_CHILD eng "Failed to add the foreign key constaint. Missing index for constraint '%s' in the foreign table '%s'" @@ -7094,6 +7095,7 @@ ER_ALTER_OPERATION_NOT_SUPPORTED_REASON_NOT_NULL ER_MUST_CHANGE_PASSWORD_LOGIN eng "Your password has expired. To log in you must change it using a client that supports expired passwords" bgn "Паролата ви е изтекла. За да влезете трябва да я смените използвайки клиент който поддрържа такива пароли" + rum "Parola ta a expirat. Pentru a te loga, trebuie sa o schimbi folosind un client ce suporta parole expirate" ER_ROW_IN_WRONG_PARTITION eng "Found a row in wrong partition %s" diff --git a/sql/sp_head.cc b/sql/sp_head.cc index 11c1234e2db..53d65549574 100644 --- a/sql/sp_head.cc +++ b/sql/sp_head.cc @@ -538,6 +538,7 @@ sp_head::sp_head(sp_package *parent, const Sp_handler *sph) my_hash_init(&m_sptabs, system_charset_info, 0, 0, 0, sp_table_key, 0, 0); my_hash_init(&m_sroutines, system_charset_info, 0, 0, 0, sp_sroutine_key, 0, 0); + m_security_ctx.init(); DBUG_VOID_RETURN; } diff --git a/sql/sql_acl.cc b/sql/sql_acl.cc index 05e522e6595..2221a742ac2 100644 --- a/sql/sql_acl.cc +++ b/sql/sql_acl.cc @@ -153,6 +153,9 @@ public: struct AUTH { LEX_CSTRING plugin, auth_string, salt; } *auth; uint nauth; bool account_locked; + bool password_expired; + my_time_t password_last_changed; + longlong password_lifetime; bool alloc_auth(MEM_ROOT *root, uint n) { @@ -645,7 +648,7 @@ static ACL_ROLE *find_acl_role(const char *user); static ROLE_GRANT_PAIR *find_role_grant_pair(const LEX_CSTRING *u, const LEX_CSTRING *h, const LEX_CSTRING *r); static ACL_USER_BASE *find_acl_user_base(const char *user, const char *host); static bool update_user_table_password(THD *, const User_table&, - const ACL_USER &); + ACL_USER*); static bool acl_load(THD *thd, const Grant_tables& grant_tables); static inline void get_grantor(THD *thd, char* grantor); static bool add_role_user_mapping(const char *uname, const char *hname, const char *rname); @@ -867,6 +870,12 @@ class User_table: public Grant_table_base virtual int set_default_role (const char *s, size_t l) const = 0; virtual bool get_account_locked () const = 0; virtual int set_account_locked (bool x) const = 0; + virtual bool get_password_expired () const = 0; + virtual int set_password_expired (bool x) const = 0; + virtual my_time_t get_password_last_changed () const = 0; + virtual int set_password_last_changed (const my_time_t &x) const = 0; + virtual longlong get_password_lifetime () const = 0; + virtual int set_password_lifetime (longlong x) const = 0; virtual ~User_table() {} private: @@ -1139,8 +1148,70 @@ class User_table_tabular: public User_table { if (Field *f= get_field(end_priv_columns + 13, MYSQL_TYPE_ENUM)) return f->store(x+1, 0); - else - return 1; + + return 1; + } + + bool get_password_expired () const + { + uint field_num= end_priv_columns + 10; + + Field *f= get_field(field_num, MYSQL_TYPE_ENUM); + return f ? f->val_int()-1 : 0; + } + int set_password_expired (bool x) const + { + uint field_num= end_priv_columns + 10; + + if (Field *f= get_field(field_num, MYSQL_TYPE_ENUM)) + return f->store(x+1, 0); + + return 1; + } + my_time_t get_password_last_changed () const + { + ulong unused_dec; + if (Field *f= get_field(end_priv_columns + 11, MYSQL_TYPE_TIMESTAMP2)) + return f->get_timestamp(&unused_dec); + + return 0; + } + int set_password_last_changed (const my_time_t &x) const + { + if (Field *f= get_field(end_priv_columns + 11, MYSQL_TYPE_TIMESTAMP2)) + { + f->set_notnull(); + return f->store_timestamp(x, 0); + } + + return 1; + } + longlong get_password_lifetime () const + { + if (Field *f= get_field(end_priv_columns + 12, MYSQL_TYPE_SHORT)) + { + if (f->is_null()) + return -1; + + return f->val_int(); + } + + return 0; + } + int set_password_lifetime (longlong x) const + { + if (Field *f= get_field(end_priv_columns + 12, MYSQL_TYPE_SHORT)) + { + if (x < 0) + { + f->set_null(); + return 0; + } + f->set_notnull(); + return f->store(x, 0); + } + + return 1; } virtual ~User_table_tabular() {} @@ -1438,6 +1509,37 @@ class User_table_json: public User_table { return get_bool_value("account_locked"); } int set_account_locked (bool x) const { return set_bool_value("account_locked", x); } + my_time_t get_password_last_changed () const + { return static_cast<my_time_t>(get_int_value("password_last_changed")); } + int set_password_last_changed (const my_time_t &x) const + { return set_int_value("password_last_changed", static_cast<longlong>(x)); } + int set_password_lifetime (longlong x) const + { return set_int_value("password_lifetime", x); } + longlong get_password_lifetime () const + { + size_t value_len; + const char *value_start; + const char *key= "password_lifetime"; + if (get_value(key, JSV_NUMBER, &value_start, &value_len)) + return -1; + return get_int_value(key); + } + /* + password_last_changed=0 means the password is manually expired. + In MySQL 5.7+ this state is described using the password_expired column + in mysql.user + */ + bool get_password_expired () const + { + size_t value_len; + const char *value_start; + const char *key= "password_last_changed"; + if (get_value(key, JSV_NUMBER, &value_start, &value_len)) + return false; + return get_password_last_changed() == 0; + } + int set_password_expired (bool x) const + { return x ? set_password_last_changed(0) : 0; } ~User_table_json() {} private: @@ -2284,6 +2386,10 @@ static bool acl_load(THD *thd, const Grant_tables& tables) user.account_locked= user_table.get_account_locked(); + user.password_expired= user_table.get_password_expired(); + user.password_last_changed= user_table.get_password_last_changed(); + user.password_lifetime= user_table.get_password_lifetime(); + if (is_role) { if (is_invalid_role_name(username)) @@ -2865,6 +2971,7 @@ bool acl_getroot(Security_context *sctx, const char *user, const char *host, DBUG_PRINT("enter", ("Host: '%s', Ip: '%s', User: '%s', db: '%s'", host, ip, user, db)); + sctx->init(); sctx->user= *user ? user : NULL; sctx->host= host; sctx->ip= ip; @@ -2881,9 +2988,7 @@ bool acl_getroot(Security_context *sctx, const char *user, const char *host, mysql_mutex_lock(&acl_cache->lock); - sctx->master_access= 0; sctx->db_access= 0; - *sctx->priv_user= *sctx->priv_host= *sctx->priv_role= 0; if (host[0]) // User, not Role { @@ -3545,10 +3650,13 @@ static int check_alter_user(THD *thd, const char *host, const char *user) if (!thd->slave_thread && IF_WSREP((!WSREP(thd) || !thd->wsrep_applier),1) && - (strcmp(thd->security_ctx->priv_user, user) || - my_strcasecmp(system_charset_info, host, - thd->security_ctx->priv_host))) + !thd->security_ctx->is_priv_user(user, host)) { + if (thd->security_ctx->password_expired) + { + my_error(ER_MUST_CHANGE_PASSWORD, MYF(0)); + goto end; + } if (check_access(thd, UPDATE_ACL, "mysql", NULL, NULL, 1, 0)) goto end; } @@ -3659,7 +3767,7 @@ bool change_password(THD *thd, LEX_USER *user) goto end; } - if (update_user_table_password(thd, tables.user_table(), *acl_user)) + if (update_user_table_password(thd, tables.user_table(), acl_user)) goto end; acl_cache->clear(1); // Clear locked hostname cache @@ -4103,7 +4211,7 @@ bool hostname_requires_resolving(const char *hostname) */ static bool update_user_table_password(THD *thd, const User_table& user_table, - const ACL_USER &user) + ACL_USER *user) { char user_key[MAX_KEY_LENGTH]; int error; @@ -4111,8 +4219,8 @@ static bool update_user_table_password(THD *thd, const User_table& user_table, TABLE *table= user_table.table(); table->use_all_columns(); - user_table.set_host(user.host.hostname, user.hostname_length); - user_table.set_user(user.user.str, user.user.length); + user_table.set_host(user->host.hostname, user->hostname_length); + user_table.set_user(user->user.str, user->user.length); key_copy((uchar *) user_key, table->record[0], table->key_info, table->key_info->key_length); @@ -4126,7 +4234,7 @@ static bool update_user_table_password(THD *thd, const User_table& user_table, } store_record(table, record[1]); - if (user_table.set_auth(user)) + if (user_table.set_auth(*user)) { my_error(ER_COL_COUNT_DOESNT_MATCH_PLEASE_UPDATE, MYF(0), user_table.name().str, 3, user_table.num_fields(), @@ -4134,6 +4242,11 @@ static bool update_user_table_password(THD *thd, const User_table& user_table, DBUG_RETURN(1); } + /* Update the persistent password expired state of user */ + user_table.set_password_expired(false); + my_time_t now= thd->query_start(); + int rv= user_table.set_password_last_changed(now); + if (unlikely(error= table->file->ha_update_row(table->record[1], table->record[0])) && error != HA_ERR_RECORD_IS_THE_SAME) @@ -4141,6 +4254,17 @@ static bool update_user_table_password(THD *thd, const User_table& user_table, table->file->print_error(error,MYF(0)); DBUG_RETURN(1); } + + /* Update the acl password expired state of user */ + if (!rv) + user->password_last_changed= now; + user->password_expired= false; + + /* If user is the connected user, reset the password expired field on sctx + and allow the user to exit sandbox mode */ + if (thd->security_ctx->is_priv_user(user->user.str, user->host.hostname)) + thd->security_ctx->password_expired= false; + DBUG_RETURN(0); } @@ -4357,6 +4481,46 @@ static int replace_user_table(THD *thd, const User_table &user_table, if (lex->account_options.account_locked != ACCOUNTLOCK_UNSPECIFIED) user_table.set_account_locked(new_acl_user.account_locked); + + my_time_t now= thd->query_start(); + if (!old_row_exists) + { + if (!user_table.set_password_last_changed(now)) + new_acl_user.password_last_changed= now; + if (!user_table.set_password_lifetime(-1)) + new_acl_user.password_lifetime= -1; + } + + /* Unexpire the user password */ + if (combo->is_changing_password) + { + user_table.set_password_expired(false); + new_acl_user.password_expired= false; + if (user_table.set_password_last_changed(now)) + new_acl_user.password_last_changed= now; + } + + switch (lex->account_options.password_expire) { + case PASSWORD_EXPIRE_UNSPECIFIED: + break; + case PASSWORD_EXPIRE_NOW: + user_table.set_password_expired(true); + new_acl_user.password_expired= true; + break; + case PASSWORD_EXPIRE_NEVER: + if (!user_table.set_password_lifetime(0)) + new_acl_user.password_lifetime= 0; + break; + case PASSWORD_EXPIRE_DEFAULT: + if (!user_table.set_password_lifetime(-1)) + new_acl_user.password_lifetime= -1; + break; + case PASSWORD_EXPIRE_INTERVAL: + longlong interval= lex->account_options.num_expiration_days; + if (!user_table.set_password_lifetime(interval)) + new_acl_user.password_lifetime= interval; + break; + } } if (old_row_exists) @@ -8813,6 +8977,19 @@ bool mysql_show_create_user(THD *thd, LEX_USER *lex_user) add_user_parameters(&result, acl_user, false); + if (acl_user->password_expired) + result.append(STRING_WITH_LEN(" PASSWORD EXPIRE")); + else if (!acl_user->password_lifetime) + result.append(STRING_WITH_LEN(" PASSWORD EXPIRE NEVER")); + else if (acl_user->password_lifetime > 0) + { + result.append(STRING_WITH_LEN(" PASSWORD EXPIRE INTERVAL ")); + char days[MAX_BIGINT_WIDTH + 1]; + my_snprintf(days, sizeof(days), "%lu", acl_user->password_lifetime); + result.append(days); + result.append(STRING_WITH_LEN(" DAY")); + } + if (acl_user->account_locked) result.append(STRING_WITH_LEN(" ACCOUNT LOCK")); @@ -10745,6 +10922,7 @@ int mysql_alter_user(THD* thd, List<LEX_USER> &users_list) LEX_USER *tmp_lex_user; List_iterator<LEX_USER> users_list_iterator(users_list); + while ((tmp_lex_user= users_list_iterator++)) { LEX_USER* lex_user= get_current_user(thd, tmp_lex_user, false); @@ -11329,9 +11507,7 @@ acl_check_proxy_grant_access(THD *thd, const char *host, const char *user, or revoking proxy privilege, user is expected to provide entries mentioned in mysql.user table. */ - if (!strcmp(thd->security_ctx->priv_user, user) && - !my_strcasecmp(system_charset_info, host, - thd->security_ctx->priv_host)) + if (thd->security_ctx->is_priv_user(user, host)) { DBUG_PRINT("info", ("strcmp (%s, %s) my_casestrcmp (%s, %s) equal", thd->security_ctx->priv_user, user, @@ -11702,7 +11878,6 @@ int fill_schema_user_privileges(THD *thd, TABLE_LIST *tables, COND *cond) TABLE *table= tables->table; bool no_global_access= check_access(thd, SELECT_ACL, "mysql", NULL, NULL, 1, 1); - char *curr_host= thd->security_ctx->priv_host_name(); DBUG_ENTER("fill_schema_user_privileges"); if (!initialized) @@ -11717,8 +11892,7 @@ int fill_schema_user_privileges(THD *thd, TABLE_LIST *tables, COND *cond) host= safe_str(acl_user->host.hostname); if (no_global_access && - (strcmp(thd->security_ctx->priv_user, user) || - my_strcasecmp(system_charset_info, curr_host, host))) + !thd->security_ctx->is_priv_user(user, host)) continue; want_access= acl_user->access; @@ -11775,7 +11949,6 @@ int fill_schema_schema_privileges(THD *thd, TABLE_LIST *tables, COND *cond) TABLE *table= tables->table; bool no_global_access= check_access(thd, SELECT_ACL, "mysql", NULL, NULL, 1, 1); - char *curr_host= thd->security_ctx->priv_host_name(); DBUG_ENTER("fill_schema_schema_privileges"); if (!initialized) @@ -11791,8 +11964,7 @@ int fill_schema_schema_privileges(THD *thd, TABLE_LIST *tables, COND *cond) host= safe_str(acl_db->host.hostname); if (no_global_access && - (strcmp(thd->security_ctx->priv_user, user) || - my_strcasecmp(system_charset_info, curr_host, host))) + !thd->security_ctx->is_priv_user(user, host)) continue; want_access=acl_db->access; @@ -11849,7 +12021,6 @@ int fill_schema_table_privileges(THD *thd, TABLE_LIST *tables, COND *cond) TABLE *table= tables->table; bool no_global_access= check_access(thd, SELECT_ACL, "mysql", NULL, NULL, 1, 1); - char *curr_host= thd->security_ctx->priv_host_name(); DBUG_ENTER("fill_schema_table_privileges"); mysql_rwlock_rdlock(&LOCK_grant); @@ -11863,8 +12034,7 @@ int fill_schema_table_privileges(THD *thd, TABLE_LIST *tables, COND *cond) host= safe_str(grant_table->host.hostname); if (no_global_access && - (strcmp(thd->security_ctx->priv_user, user) || - my_strcasecmp(system_charset_info, curr_host, host))) + !thd->security_ctx->is_priv_user(user, host)) continue; ulong table_access= grant_table->privs; @@ -11931,7 +12101,6 @@ int fill_schema_column_privileges(THD *thd, TABLE_LIST *tables, COND *cond) TABLE *table= tables->table; bool no_global_access= check_access(thd, SELECT_ACL, "mysql", NULL, NULL, 1, 1); - char *curr_host= thd->security_ctx->priv_host_name(); DBUG_ENTER("fill_schema_table_privileges"); mysql_rwlock_rdlock(&LOCK_grant); @@ -11945,8 +12114,7 @@ int fill_schema_column_privileges(THD *thd, TABLE_LIST *tables, COND *cond) host= safe_str(grant_table->host.hostname); if (no_global_access && - (strcmp(thd->security_ctx->priv_user, user) || - my_strcasecmp(system_charset_info, curr_host, host))) + !thd->security_ctx->is_priv_user(user, host)) continue; ulong table_access= grant_table->cols; @@ -13459,6 +13627,33 @@ static void handle_password_errors(const char *user, const char *hostname, PASSW #endif } +bool check_password_lifetime(THD *thd, const ACL_USER *acl_user) +{ + /* the password should never expire */ + if (!acl_user->password_lifetime) + return false; + + longlong interval= acl_user->password_lifetime; + if (acl_user->password_lifetime < 0) + { + interval= default_password_lifetime; + + /* default global policy applies, and that is password never expires */ + if (!interval) + return false; + } + + thd->set_time(); + longlong interval_sec= 3600 * 24 * interval; + + /* this helps test set a testable password lifetime in seconds not days */ + DBUG_EXECUTE_IF("password_expiration_interval_sec", { interval_sec= interval; }); + + if (thd->query_start() - acl_user->password_last_changed > interval_sec) + return true; + + return false; +} /** Perform the handshake, authorize the client and update thd sctx variables. @@ -13681,6 +13876,21 @@ bool acl_authenticate(THD *thd, uint com_change_user_pkt_len) DBUG_RETURN(1); } + bool client_can_handle_exp_pass= thd->client_capabilities & + CLIENT_CAN_HANDLE_EXPIRED_PASSWORDS; + bool password_lifetime_due= check_password_lifetime(thd, acl_user); + + if (!client_can_handle_exp_pass && disconnect_on_expired_password && + (acl_user->password_expired || password_lifetime_due)) + { + status_var_increment(denied_connections); + my_error(ER_MUST_CHANGE_PASSWORD_LOGIN, MYF(0)); + DBUG_RETURN(1); + } + + sctx->password_expired= acl_user->password_expired || + password_lifetime_due; + /* Don't allow the user to connect if he has done too many queries. As we are testing max_user_connections == 0 here, it means that we diff --git a/sql/sql_class.cc b/sql/sql_class.cc index 7a623ce6697..05534eb717e 100644 --- a/sql/sql_class.cc +++ b/sql/sql_class.cc @@ -4297,6 +4297,7 @@ void Security_context::init() host_or_ip= "connecting host"; priv_user[0]= priv_host[0]= proxy_user[0]= priv_role[0]= '\0'; master_access= 0; + password_expired= false; #ifndef NO_EMBEDDED_ACCESS_CHECKS db_access= NO_ACCESS; #endif @@ -4335,6 +4336,7 @@ void Security_context::skip_grants() host_or_ip= (char *)""; master_access= ~NO_ACCESS; *priv_user= *priv_host= '\0'; + password_expired= false; } @@ -4453,6 +4455,13 @@ bool Security_context::user_matches(Security_context *them) !strcmp(user, them->user)); } +bool Security_context::is_priv_user(const char *user, const char *host) +{ + return ((user != NULL) && (host != NULL) && + !strcmp(user, priv_user) && + !my_strcasecmp(system_charset_info, host,priv_host)); +} + /**************************************************************************** Handling of open and locked tables states. diff --git a/sql/sql_class.h b/sql/sql_class.h index 6df1e58c95f..4e846d3f169 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -1334,6 +1334,8 @@ public: ulong master_access; /* Global privileges from mysql.user */ ulong db_access; /* Privileges for current db */ + bool password_expired; + void init(); void destroy(); void skip_grants(); @@ -1364,6 +1366,7 @@ public: @return True if the security context fulfills the access requirements. */ bool check_access(ulong want_access, bool match_any = false); + bool is_priv_user(const char *user, const char *host); }; diff --git a/sql/sql_lex.h b/sql/sql_lex.h index 1a0e7f56b5a..b261cb43103 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -2960,10 +2960,21 @@ enum account_lock_type ACCOUNTLOCK_UNLOCKED }; +enum password_exp_type +{ + PASSWORD_EXPIRE_UNSPECIFIED, + PASSWORD_EXPIRE_NOW, + PASSWORD_EXPIRE_NEVER, + PASSWORD_EXPIRE_DEFAULT, + PASSWORD_EXPIRE_INTERVAL +}; + struct Account_options: public USER_RESOURCES { Account_options() : account_locked(ACCOUNTLOCK_UNSPECIFIED) + , password_expire(PASSWORD_EXPIRE_UNSPECIFIED) + , num_expiration_days(0) { } void reset() @@ -2975,9 +2986,10 @@ struct Account_options: public USER_RESOURCES enum SSL_type ssl_type; // defined in violite.h LEX_CSTRING x509_subject, x509_issuer, ssl_cipher; account_lock_type account_locked; + password_exp_type password_expire; + longlong num_expiration_days; }; - class Query_arena_memroot; /* The state of the lex parsing. This is saved in the THD struct */ diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index 8880c5fe9a9..fecd889381c 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -1626,6 +1626,15 @@ bool dispatch_command(enum enum_server_command command, THD *thd, thd->get_stmt_da()->set_skip_flush(); } + if (unlikely(thd->security_ctx->password_expired && + command != COM_QUERY && + command != COM_PING && + command != COM_QUIT)) + { + my_error(ER_MUST_CHANGE_PASSWORD, MYF(0)); + goto dispatch_end; + } + switch (command) { case COM_INIT_DB: { @@ -2357,8 +2366,8 @@ com_multi_end: break; } +dispatch_end: #ifdef WITH_WSREP - dispatch_end: /* BF aborted before sending response back to client */ @@ -3252,6 +3261,13 @@ mysql_execute_command(THD *thd) #endif DBUG_ENTER("mysql_execute_command"); + if (thd->security_ctx->password_expired && + lex->sql_command != SQLCOM_SET_OPTION) + { + my_error(ER_MUST_CHANGE_PASSWORD, MYF(0)); + DBUG_RETURN(1); + } + DBUG_ASSERT(thd->transaction.stmt.is_empty() || thd->in_sub_stmt); /* Each statement or replication event which might produce deadlock diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc index 6c3ad9c6924..0cb9be44636 100644 --- a/sql/sql_prepare.cc +++ b/sql/sql_prepare.cc @@ -4200,15 +4200,6 @@ Prepared_statement::execute_loop(String *expanded_query, if (set_parameters(expanded_query, packet, packet_end)) return TRUE; -#ifdef NOT_YET_FROM_MYSQL_5_6 - if (unlikely(thd->security_ctx->password_expired && - !lex->is_change_password)) - { - my_error(ER_MUST_CHANGE_PASSWORD, MYF(0)); - return true; - } -#endif - reexecute: // Make sure that reprepare() did not create any new Items. DBUG_ASSERT(thd->free_list == NULL); @@ -4349,16 +4340,6 @@ Prepared_statement::execute_bulk_loop(String *expanded_query, } read_types= FALSE; -#ifdef NOT_YET_FROM_MYSQL_5_6 - if (unlikely(thd->security_ctx->password_expired && - !lex->is_change_password)) - { - my_error(ER_MUST_CHANGE_PASSWORD, MYF(0)); - thd->set_bulk_execution(0); - return true; - } -#endif - // iterations changed by set_bulk_parameters while ((iterations || start_param) && !error && !thd->is_error()) { diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index c67dbcfed6f..8500bca8671 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -1271,6 +1271,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize); %token <kwd> EXIT_MARIADB_SYM /* PLSQL-R */ %token <kwd> EXIT_ORACLE_SYM /* PLSQL-R */ %token <kwd> EXPANSION_SYM +%token <kwd> EXPIRE_SYM /* MySQL */ %token <kwd> EXPORT_SYM %token <kwd> EXTENDED_SYM %token <kwd> EXTENT_SIZE_SYM @@ -1386,6 +1387,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize); %token <kwd> NAME_SYM /* SQL-2003-N */ %token <kwd> NATIONAL_SYM /* SQL-2003-R */ %token <kwd> NCHAR_SYM /* SQL-2003-R */ +%token <kwd> NEVER_SYM /* MySQL */ %token <kwd> NEW_SYM /* SQL-2003-R */ %token <kwd> NEXT_SYM /* SQL-2003-N */ %token <kwd> NEXTVAL_SYM /* PostgreSQL sequence function */ @@ -2917,7 +2919,7 @@ create: Lex->pop_select(); //main select } | create_or_replace USER_SYM opt_if_not_exists clear_privileges - grant_list opt_require_clause opt_resource_options opt_account_locking + grant_list opt_require_clause opt_resource_options opt_account_locking opt_password_expiration { if (unlikely(Lex->set_command_with_check(SQLCOM_CREATE_USER, $1 | $3))) @@ -7993,7 +7995,7 @@ alter: } OPTIONS_SYM '(' server_options_list ')' { } /* ALTER USER foo is allowed for MySQL compatibility. */ | ALTER opt_if_exists USER_SYM clear_privileges grant_list - opt_require_clause opt_resource_options opt_account_locking + opt_require_clause opt_resource_options opt_account_locking opt_password_expiration { Lex->create_info.set($2); Lex->sql_command= SQLCOM_ALTER_USER; @@ -8043,6 +8045,35 @@ opt_account_locking: Lex->account_options.account_locked= ACCOUNTLOCK_UNLOCKED; } ; +opt_password_expiration: + /* Nothing */ {} + | PASSWORD_SYM EXPIRE_SYM + { + Lex->account_options.password_expire= PASSWORD_EXPIRE_NOW; + } + | PASSWORD_SYM EXPIRE_SYM NEVER_SYM + { + Lex->account_options.password_expire= PASSWORD_EXPIRE_NEVER; + } + | PASSWORD_SYM EXPIRE_SYM DEFAULT + { + Lex->account_options.password_expire= PASSWORD_EXPIRE_DEFAULT; + } + | PASSWORD_SYM EXPIRE_SYM INTERVAL_SYM NUM DAY_SYM + { + int error; + longlong interval= my_strtoll10($4.str, (char**) 0, &error); + if (!interval) + { + char num[MAX_BIGINT_WIDTH + 1]; + my_snprintf(num, sizeof(num), "%lu", interval); + my_yyabort_error((ER_WRONG_VALUE, MYF(0), "DAY", num)); + } + + Lex->account_options.password_expire= PASSWORD_EXPIRE_INTERVAL; + Lex->account_options.num_expiration_days= interval; + } + ; ev_alter_on_schedule_completion: /* empty */ { $$= 0;} @@ -16041,6 +16072,7 @@ keyword_sp_var_and_label: | EXCEPTION_MARIADB_SYM | EXCHANGE_SYM | EXPANSION_SYM + | EXPIRE_SYM | EXPORT_SYM | EXTENDED_SYM | EXTENT_SIZE_SYM @@ -16131,6 +16163,7 @@ keyword_sp_var_and_label: | MYSQL_SYM | MYSQL_ERRNO_SYM | NAME_SYM + | NEVER_SYM | NEXT_SYM %prec PREC_BELOW_CONTRACTION_TOKEN2 | NEXTVAL_SYM | NEW_SYM @@ -17267,20 +17300,26 @@ grant_user: $$= $1; $1->auth= new (thd->mem_root) USER_AUTH(); $1->auth->pwtext= $4; + $1->is_changing_password= true; } | user IDENTIFIED_SYM BY PASSWORD_SYM TEXT_STRING { $$= $1; $1->auth= new (thd->mem_root) USER_AUTH(); $1->auth->auth_str= $5; + $1->is_changing_password= true; } | user IDENTIFIED_SYM via_or_with auth_expression { $$= $1; $1->auth= $4; + $1->is_changing_password= false; } | user_or_role - { $$= $1; } + { + $$= $1; + $1->is_changing_password= false; + } ; auth_expression: diff --git a/sql/sql_yacc_ora.yy b/sql/sql_yacc_ora.yy index 17188da3d2d..4ae4cca4865 100644 --- a/sql/sql_yacc_ora.yy +++ b/sql/sql_yacc_ora.yy @@ -766,6 +766,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize); %token <kwd> EXIT_MARIADB_SYM /* PLSQL-R */ %token <kwd> EXIT_ORACLE_SYM /* PLSQL-R */ %token <kwd> EXPANSION_SYM +%token <kwd> EXPIRE_SYM /* MySQL */ %token <kwd> EXPORT_SYM %token <kwd> EXTENDED_SYM %token <kwd> EXTENT_SIZE_SYM @@ -881,6 +882,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize); %token <kwd> NAME_SYM /* SQL-2003-N */ %token <kwd> NATIONAL_SYM /* SQL-2003-R */ %token <kwd> NCHAR_SYM /* SQL-2003-R */ +%token <kwd> NEVER_SYM /* MySQL */ %token <kwd> NEW_SYM /* SQL-2003-R */ %token <kwd> NEXT_SYM /* SQL-2003-N */ %token <kwd> NEXTVAL_SYM /* PostgreSQL sequence function */ @@ -2423,7 +2425,7 @@ create: Lex->pop_select(); //main select } | create_or_replace USER_SYM opt_if_not_exists clear_privileges - grant_list opt_require_clause opt_resource_options opt_account_locking + grant_list opt_require_clause opt_resource_options opt_account_locking opt_password_expiration { if (unlikely(Lex->set_command_with_check(SQLCOM_CREATE_USER, $1 | $3))) @@ -8023,7 +8025,7 @@ alter: } OPTIONS_SYM '(' server_options_list ')' { } /* ALTER USER foo is allowed for MySQL compatibility. */ | ALTER opt_if_exists USER_SYM clear_privileges grant_list - opt_require_clause opt_resource_options opt_account_locking + opt_require_clause opt_resource_options opt_account_locking opt_password_expiration { Lex->create_info.set($2); Lex->sql_command= SQLCOM_ALTER_USER; @@ -8073,6 +8075,35 @@ opt_account_locking: Lex->account_options.account_locked= ACCOUNTLOCK_UNLOCKED; } ; +opt_password_expiration: + /* Nothing */ {} + | PASSWORD_SYM EXPIRE_SYM + { + Lex->account_options.password_expire= PASSWORD_EXPIRE_NOW; + } + | PASSWORD_SYM EXPIRE_SYM NEVER_SYM + { + Lex->account_options.password_expire= PASSWORD_EXPIRE_NEVER; + } + | PASSWORD_SYM EXPIRE_SYM DEFAULT + { + Lex->account_options.password_expire= PASSWORD_EXPIRE_DEFAULT; + } + | PASSWORD_SYM EXPIRE_SYM INTERVAL_SYM NUM DAY_SYM + { + int error; + longlong interval= my_strtoll10($4.str, (char**) 0, &error); + if (!interval) + { + char num[MAX_BIGINT_WIDTH + 1]; + my_snprintf(num, sizeof(num), "%lu", interval); + my_yyabort_error((ER_WRONG_VALUE, MYF(0), "DAY", num)); + } + + Lex->account_options.password_expire= PASSWORD_EXPIRE_INTERVAL; + Lex->account_options.num_expiration_days= interval; + } + ; ev_alter_on_schedule_completion: /* empty */ { $$= 0;} @@ -16130,6 +16161,7 @@ keyword_sp_var_and_label: | EXCEPTION_MARIADB_SYM | EXCHANGE_SYM | EXPANSION_SYM + | EXPIRE_SYM | EXPORT_SYM | EXTENDED_SYM | EXTENT_SIZE_SYM @@ -16222,6 +16254,7 @@ keyword_sp_var_and_label: | NAME_SYM | NEXT_SYM %prec PREC_BELOW_CONTRACTION_TOKEN2 | NEXTVAL_SYM + | NEVER_SYM | NEW_SYM | NOCACHE_SYM | NOCYCLE_SYM @@ -17405,20 +17438,26 @@ grant_user: $$= $1; $1->auth= new (thd->mem_root) USER_AUTH(); $1->auth->pwtext= $4; + $1->is_changing_password= true; } | user IDENTIFIED_SYM BY PASSWORD_SYM TEXT_STRING { $$= $1; $1->auth= new (thd->mem_root) USER_AUTH(); $1->auth->auth_str= $5; + $1->is_changing_password= true; } | user IDENTIFIED_SYM via_or_with auth_expression { $$= $1; $1->auth= $4; + $1->is_changing_password= false; } | user_or_role - { $$= $1; } + { + $$= $1; + $1->is_changing_password= false; + } ; auth_expression: diff --git a/sql/structs.h b/sql/structs.h index 1eac31d8659..5ae07b8b754 100644 --- a/sql/structs.h +++ b/sql/structs.h @@ -255,6 +255,7 @@ struct AUTHID struct LEX_USER: public AUTHID { USER_AUTH *auth; + bool is_changing_password; bool has_auth() { return auth && (auth->plugin.length || auth->auth_str.length || auth->pwtext.length); diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc index 64a8b0c17b5..595d02c81c9 100644 --- a/sql/sys_vars.cc +++ b/sql/sys_vars.cc @@ -1517,6 +1517,24 @@ static Sys_var_ulong Sys_max_connections( DEFAULT(MAX_CONNECTIONS_DEFAULT), BLOCK_SIZE(1), NO_MUTEX_GUARD, NOT_IN_BINLOG, ON_CHECK(0), ON_UPDATE(fix_max_connections)); +static Sys_var_ulong Sys_default_password_lifetime( + "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.", + GLOBAL_VAR(default_password_lifetime), CMD_LINE(REQUIRED_ARG), + VALID_RANGE(0, UINT_MAX), DEFAULT(0), BLOCK_SIZE(1)); + +static Sys_var_mybool Sys_disconnect_on_expired_password( + "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.", + GLOBAL_VAR(disconnect_on_expired_password), CMD_LINE(OPT_ARG), + DEFAULT(FALSE)); + static Sys_var_ulong Sys_max_connect_errors( "max_connect_errors", "If there is more than this number of interrupted connections from " |