mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 19:06:14 +01:00
1e6210161d
post-merge changes: * handle password expiration on old tables like everything else - make changes in memory, even if they cannot be done on disk * merge "debug" tests with non-debug tests, they don't use dbug anyway * only run rpl password expiration in MIXED mode, it doesn't replicate anything, so no need to repeat it thrice * restore update_user_table_password() prototype, it should not change ACL_USER, this is done in acl_user_update() * don't parse json twice in get_password_lifetime and get_password_expired * remove LEX_USER::is_changing_password, see if there was any auth instead * avoid overflow in expiration calculations * don't initialize Account_options in the constructor, it's bzero-ed later * don't create ulong sysvars - they're not portable, prefer uint or ulonglong * misc simplifications
263 lines
8.3 KiB
Text
263 lines
8.3 KiB
Text
#
|
|
# Test password expiration
|
|
#
|
|
|
|
--source include/not_embedded.inc
|
|
|
|
--echo #
|
|
--echo # Only privileged users should be able to expire passwords
|
|
--echo #
|
|
create user user1@localhost;
|
|
alter user user1@localhost password expire;
|
|
|
|
create user user2@localhost;
|
|
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
|
|
connect(con2,localhost,user2);
|
|
connection con2;
|
|
--error ER_SPECIFIC_ACCESS_DENIED_ERROR
|
|
alter user user1@localhost password expire;
|
|
|
|
disconnect con2;
|
|
connection default;
|
|
drop user user1@localhost;
|
|
drop user user2@localhost;
|
|
|
|
--echo #
|
|
--echo # disconnect_on_expired_password=ON should deny a clients's connection
|
|
--echo # when the password is expired or put the client in sandbox mode if OFF
|
|
--echo #
|
|
create user user1@localhost password expire;
|
|
set global disconnect_on_expired_password=ON;
|
|
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
|
|
--error ER_MUST_CHANGE_PASSWORD_LOGIN
|
|
connect(con1,localhost,user1);
|
|
|
|
# should allow the client to enter sandbox mode
|
|
set global disconnect_on_expired_password=OFF;
|
|
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
|
|
connect(con1,localhost,user1);
|
|
connection con1;
|
|
--error ER_MUST_CHANGE_PASSWORD
|
|
select 1;
|
|
disconnect con1;
|
|
connection default;
|
|
drop user user1@localhost;
|
|
|
|
--echo #
|
|
--echo # connect-expired-password option passed to client should override
|
|
--echo # the behavior of disconnect_on_expired_password server system var.
|
|
--echo #
|
|
create user user1@localhost password expire;
|
|
set global disconnect_on_expired_password=ON;
|
|
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
|
|
--error ER_MUST_CHANGE_PASSWORD_LOGIN
|
|
connect(con1,localhost,user1);
|
|
|
|
--exec $MYSQL --connect-expired-password -u user1 -e "set password=password('');"
|
|
drop user user1@localhost;
|
|
|
|
--echo #
|
|
--echo # Manually expiring a password should have immediate effect
|
|
--echo #
|
|
create user user1@localhost;
|
|
alter user user1@localhost password expire;
|
|
set global disconnect_on_expired_password=ON;
|
|
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
|
|
--error ER_MUST_CHANGE_PASSWORD_LOGIN
|
|
connect(con1,localhost,user1);
|
|
drop user user1@localhost;
|
|
|
|
--echo #
|
|
--echo # Sandbox mode should only allow change password statements
|
|
--echo #
|
|
create user user1@localhost password expire;
|
|
grant create user on *.* to user1@localhost;
|
|
set global disconnect_on_expired_password=OFF;
|
|
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
|
|
connect(con1,localhost,user1);
|
|
connection con1;
|
|
--error ER_MUST_CHANGE_PASSWORD
|
|
select 1;
|
|
set password=password('');
|
|
select 1;
|
|
disconnect con1;
|
|
connection default;
|
|
|
|
drop user user1@localhost;
|
|
|
|
--echo #
|
|
--echo # Passwords are still expired after acl reload
|
|
--echo #
|
|
set global disconnect_on_expired_password=ON;
|
|
create user user1@localhost password expire;
|
|
flush privileges;
|
|
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
|
|
--error ER_MUST_CHANGE_PASSWORD_LOGIN
|
|
connect(con1,localhost,user1);
|
|
drop user user1@localhost;
|
|
|
|
--echo #
|
|
--echo # JSON functions on global_priv reflect the correct state
|
|
--echo # of the password expiration columns
|
|
--echo #
|
|
|
|
create user user1@localhost password expire;
|
|
select host, user, JSON_VALUE(Priv, '$.password_last_changed') from mysql.global_priv where user='user1';
|
|
alter user user1@localhost password expire never;
|
|
select host, user, JSON_VALUE(Priv, '$.password_lifetime') from mysql.global_priv where user='user1';
|
|
alter user user1@localhost password expire default;
|
|
select host, user, JSON_VALUE(Priv, '$.password_lifetime') from mysql.global_priv where user='user1';
|
|
alter user user1@localhost password expire interval 123 day;
|
|
select host, user, JSON_VALUE(Priv, '$.password_lifetime') from mysql.global_priv where user='user1';
|
|
drop user user1@localhost;
|
|
|
|
--echo #
|
|
--echo # SHOW CREATE USER correctly displays the locking state of an user
|
|
--echo #
|
|
|
|
create user user1@localhost;
|
|
show create user user1@localhost;
|
|
alter user user1@localhost password expire;
|
|
show create user user1@localhost;
|
|
set password for user1@localhost= password('');
|
|
alter user user1@localhost password expire default;
|
|
show create user user1@localhost;
|
|
alter user user1@localhost password expire never;
|
|
show create user user1@localhost;
|
|
alter user user1@localhost password expire interval 123 day;
|
|
show create user user1@localhost;
|
|
alter user user1@localhost password expire;
|
|
show create user user1@localhost;
|
|
set password for user1@localhost= password('');
|
|
show create user user1@localhost;
|
|
drop user user1@localhost;
|
|
|
|
--echo #
|
|
--echo # Incorrect INTERVAL values should be rejected
|
|
--echo #
|
|
--error ER_WRONG_VALUE
|
|
create user user1@localhost password expire interval 0 day;
|
|
|
|
--echo #
|
|
--echo # Password expiration fields are loaded properly on 10.3 tables
|
|
--echo #
|
|
--source include/switch_to_mysql_user.inc
|
|
create user user1@localhost;
|
|
show create user user1@localhost;
|
|
flush privileges;
|
|
show create user user1@localhost;
|
|
|
|
alter user user1@localhost password expire;
|
|
show create user user1@localhost;
|
|
flush privileges;
|
|
show create user user1@localhost;
|
|
set password for user1@localhost= password('');
|
|
|
|
alter user user1@localhost password expire default;
|
|
show create user user1@localhost;
|
|
flush privileges;
|
|
show create user user1@localhost;
|
|
|
|
alter user user1@localhost password expire never;
|
|
show create user user1@localhost;
|
|
flush privileges;
|
|
show create user user1@localhost;
|
|
|
|
alter user user1@localhost password expire interval 123 day;
|
|
show create user user1@localhost;
|
|
flush privileges;
|
|
show create user user1@localhost;
|
|
|
|
alter user user1@localhost password expire;
|
|
show create user user1@localhost;
|
|
flush privileges;
|
|
show create user user1@localhost;
|
|
|
|
set global disconnect_on_expired_password=ON;
|
|
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
|
|
--error ER_MUST_CHANGE_PASSWORD_LOGIN
|
|
connect(con1,localhost,user1);
|
|
|
|
set global disconnect_on_expired_password=OFF;
|
|
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
|
|
connect(con1,localhost,user1);
|
|
connection con1;
|
|
--error ER_MUST_CHANGE_PASSWORD
|
|
select 1;
|
|
set password=password('');
|
|
select 1;
|
|
disconnect con1;
|
|
connection default;
|
|
drop user user1@localhost;
|
|
|
|
set global disconnect_on_expired_password=default;
|
|
set global default_password_lifetime=default;
|
|
--source include/switch_to_mysql_global_priv.inc
|
|
|
|
#
|
|
# Test password expiration INTERVAL and default_password_lifetime options
|
|
#
|
|
|
|
--echo #
|
|
--echo # PASSWORD EXPIRE DEFAULT should use the default_password_lifetime
|
|
--echo # system var to set the number of days till expiration
|
|
--echo #
|
|
set global disconnect_on_expired_password= ON;
|
|
set global default_password_lifetime= 2;
|
|
create user user1@localhost password expire default;
|
|
|
|
set @tstamp_expired= UNIX_TIMESTAMP(NOW() - INTERVAL 3 DAY);
|
|
update mysql.global_priv set
|
|
priv=json_set(priv, '$.password_last_changed', @tstamp_expired)
|
|
where user='user1';
|
|
flush privileges;
|
|
|
|
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
|
|
--error ER_MUST_CHANGE_PASSWORD_LOGIN
|
|
connect(con1,localhost,user1);
|
|
drop user user1@localhost;
|
|
|
|
--echo #
|
|
--echo # PASSWORD EXPIRE INTERVAL should expire a client's password after
|
|
--echo # X days and not before
|
|
--echo #
|
|
set global disconnect_on_expired_password= ON;
|
|
create user user1@localhost password expire interval 2 day;
|
|
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
|
|
connect(con1,localhost,user1);
|
|
disconnect con1;
|
|
connection default;
|
|
|
|
set @tstamp_expired= UNIX_TIMESTAMP(NOW() - INTERVAL 3 DAY);
|
|
update mysql.global_priv set
|
|
priv=json_set(priv, '$.password_last_changed', @tstamp_expired)
|
|
where user='user1';
|
|
flush privileges;
|
|
|
|
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
|
|
--error ER_MUST_CHANGE_PASSWORD_LOGIN
|
|
connect(con1,localhost,user1);
|
|
drop user user1@localhost;
|
|
|
|
--echo #
|
|
--echo # PASSWORD EXPIRE NEVER should override the other policies and never
|
|
--echo # expire a client's password
|
|
--echo #
|
|
set global disconnect_on_expired_password= ON;
|
|
create user user1@localhost password expire interval 2 day;
|
|
alter user user1@localhost password expire never;
|
|
|
|
set @tstamp_expired= UNIX_TIMESTAMP() - 3;
|
|
update mysql.global_priv set
|
|
priv=json_set(priv, '$.password_last_changed', @tstamp_expired)
|
|
where user='user1';
|
|
flush privileges;
|
|
|
|
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
|
|
connect(con1,localhost,user1);
|
|
disconnect con1;
|
|
connection default;
|
|
drop user user1@localhost;
|
|
|
|
set global disconnect_on_expired_password= default;
|
|
set global default_password_lifetime= default;
|