mirror of
https://github.com/MariaDB/server.git
synced 2025-08-16 07:21:32 +02:00
ce7ab467db
DROP USER looks for sessions by the do-be-dropped user and if found: * fails with ER_CANNOT_USER in Oracle mode * continues with ER_ACTIVE_CONNECTIONS_FOR_USER_TO_DROP warning otherwise Every user being dropped is marked with flag that disallow establishing a new connections on behalf this user.
269 lines
8.5 KiB
Text
269 lines
8.5 KiB
Text
#
|
|
# Test password expiration
|
|
#
|
|
|
|
--source include/not_embedded.inc
|
|
--source include/count_sessions.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('');"
|
|
--source include/wait_until_count_sessions.inc
|
|
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);
|
|
--source include/wait_until_count_sessions.inc
|
|
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);
|
|
--source include/wait_until_count_sessions.inc
|
|
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);
|
|
--source include/wait_until_count_sessions.inc
|
|
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);
|
|
--source include/wait_until_count_sessions.inc
|
|
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;
|