mirror/mariadb
1
0
Fork 0
mirror of https://github.com/MariaDB/server.git synced 2025-11-03 20:36:16 +01:00
Code Issues Projects Releases Packages Wiki Activity
mariadb/mysql-test/main/password_expiration.result
Robert Bindar 8b77e6c676 MDEV-24114 SHOW CREATE USER doesnt display correct password expiry status 
Given PASSWORD EXPIRE and PASSWORD EXPIRE [NEVER|INTERVAL x DAY] are
two different mechanisms, SHOW CREATE USER should display all the
information required to restore the state of an account which
includes both a manual expired state and an automatic policy.

The solution proposed here keeps a CREATE USER ... PASSWORD EXPIRE
statement and adds an aditional
ALTER USER .. PASSWORD EXPIRE [NEVER|INTERVAL x DAY] when necessary

This way a tool can restore almost the complete state of an account
as it was before a dump. The only information left still is the
value of the password_last_changed column from mysql.global_priv
2021-02-23 11:05:56 +02:00

266 lines
9.9 KiB
Text
Raw Permalink Blame History

#
# Only privileged users should be able to expire passwords
#
create user user1@localhost;
alter user user1@localhost password expire;
create user user2@localhost;
connect con2,localhost,user2;
connection con2;
alter user user1@localhost password expire;
ERROR 42000: Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation
disconnect con2;
connection default;
drop user user1@localhost;
drop user user2@localhost;
#
# disconnect_on_expired_password=ON should deny a clients's connection
# when the password is expired or put the client in sandbox mode if OFF
#
create user user1@localhost password expire;
set global disconnect_on_expired_password=ON;
connect(localhost,user1,,test,MYSQL_PORT,MYSQL_SOCK);
connect con1,localhost,user1;
ERROR HY000: Your password has expired. To log in you must change it using a client that supports expired passwords
set global disconnect_on_expired_password=OFF;
connect con1,localhost,user1;
connection con1;
select 1;
ERROR HY000: You must SET PASSWORD before executing this statement
disconnect con1;
connection default;
drop user user1@localhost;
#
# connect-expired-password option passed to client should override
# the behavior of disconnect_on_expired_password server system var.
#
create user user1@localhost password expire;
set global disconnect_on_expired_password=ON;
connect(localhost,user1,,test,MYSQL_PORT,MYSQL_SOCK);
connect con1,localhost,user1;
ERROR HY000: Your password has expired. To log in you must change it using a client that supports expired passwords
drop user user1@localhost;
#
# Manually expiring a password should have immediate effect
#
create user user1@localhost;
alter user user1@localhost password expire;
set global disconnect_on_expired_password=ON;
connect(localhost,user1,,test,MYSQL_PORT,MYSQL_SOCK);
connect con1,localhost,user1;
ERROR HY000: Your password has expired. To log in you must change it using a client that supports expired passwords
drop user user1@localhost;
#
# Sandbox mode should only allow change password statements
#
create user user1@localhost password expire;
grant create user on *.* to user1@localhost;
set global disconnect_on_expired_password=OFF;
connect con1,localhost,user1;
connection con1;
select 1;
ERROR HY000: You must SET PASSWORD before executing this statement
set password=password('');
select 1;
1
1
disconnect con1;
connection default;
drop user user1@localhost;
#
# Passwords are still expired after acl reload
#
set global disconnect_on_expired_password=ON;
create user user1@localhost password expire;
flush privileges;
connect(localhost,user1,,test,MYSQL_PORT,MYSQL_SOCK);
connect con1,localhost,user1;
ERROR HY000: Your password has expired. To log in you must change it using a client that supports expired passwords
drop user user1@localhost;
#
# JSON functions on global_priv reflect the correct state
# of the password expiration columns
#
create user user1@localhost password expire;
select host, user, JSON_VALUE(Priv, '$.password_last_changed') from mysql.global_priv where user='user1';
host user JSON_VALUE(Priv, '$.password_last_changed')
localhost user1 0
alter user user1@localhost password expire never;
select host, user, JSON_VALUE(Priv, '$.password_lifetime') from mysql.global_priv where user='user1';
host user JSON_VALUE(Priv, '$.password_lifetime')
localhost user1 0
alter user user1@localhost password expire default;
select host, user, JSON_VALUE(Priv, '$.password_lifetime') from mysql.global_priv where user='user1';
host user JSON_VALUE(Priv, '$.password_lifetime')
localhost user1 -1
alter user user1@localhost password expire interval 123 day;
select host, user, JSON_VALUE(Priv, '$.password_lifetime') from mysql.global_priv where user='user1';
host user JSON_VALUE(Priv, '$.password_lifetime')
localhost user1 123
drop user user1@localhost;
#
# SHOW CREATE USER correctly displays the locking state of an user
#
create user user1@localhost;
show create user user1@localhost;
CREATE USER for user1@localhost
CREATE USER `user1`@`localhost`
alter user user1@localhost password expire;
show create user user1@localhost;
CREATE USER for user1@localhost
CREATE USER `user1`@`localhost` PASSWORD EXPIRE
set password for user1@localhost= password('');
alter user user1@localhost password expire default;
show create user user1@localhost;
CREATE USER for user1@localhost
CREATE USER `user1`@`localhost`
alter user user1@localhost password expire never;
show create user user1@localhost;
CREATE USER for user1@localhost
CREATE USER `user1`@`localhost` PASSWORD EXPIRE NEVER
alter user user1@localhost password expire interval 123 day;
show create user user1@localhost;
CREATE USER for user1@localhost
CREATE USER `user1`@`localhost` PASSWORD EXPIRE INTERVAL 123 DAY
alter user user1@localhost password expire;
show create user user1@localhost;
CREATE USER for user1@localhost
CREATE USER `user1`@`localhost` PASSWORD EXPIRE
ALTER USER `user1`@`localhost` PASSWORD EXPIRE INTERVAL 123 DAY
set password for user1@localhost= password('');
show create user user1@localhost;
CREATE USER for user1@localhost
CREATE USER `user1`@`localhost` PASSWORD EXPIRE INTERVAL 123 DAY
drop user user1@localhost;
#
# Incorrect INTERVAL values should be rejected
#
create user user1@localhost password expire interval 0 day;
ERROR HY000: Incorrect DAY value: '0'
#
# Password expiration fields are loaded properly on 10.3 tables
#
# switching from mysql.global_priv to mysql.user
create user user1@localhost;
show create user user1@localhost;
CREATE USER for user1@localhost
CREATE USER `user1`@`localhost`
flush privileges;
show create user user1@localhost;
CREATE USER for user1@localhost
CREATE USER `user1`@`localhost` PASSWORD EXPIRE NEVER
alter user user1@localhost password expire;
show create user user1@localhost;
CREATE USER for user1@localhost
CREATE USER `user1`@`localhost` PASSWORD EXPIRE
ALTER USER `user1`@`localhost` PASSWORD EXPIRE NEVER
flush privileges;
show create user user1@localhost;
CREATE USER for user1@localhost
CREATE USER `user1`@`localhost` PASSWORD EXPIRE
ALTER USER `user1`@`localhost` PASSWORD EXPIRE NEVER
set password for user1@localhost= password('');
alter user user1@localhost password expire default;
show create user user1@localhost;
CREATE USER for user1@localhost
CREATE USER `user1`@`localhost`
flush privileges;
show create user user1@localhost;
CREATE USER for user1@localhost
CREATE USER `user1`@`localhost` PASSWORD EXPIRE NEVER
alter user user1@localhost password expire never;
show create user user1@localhost;
CREATE USER for user1@localhost
CREATE USER `user1`@`localhost` PASSWORD EXPIRE NEVER
flush privileges;
show create user user1@localhost;
CREATE USER for user1@localhost
CREATE USER `user1`@`localhost` PASSWORD EXPIRE NEVER
alter user user1@localhost password expire interval 123 day;
show create user user1@localhost;
CREATE USER for user1@localhost
CREATE USER `user1`@`localhost` PASSWORD EXPIRE INTERVAL 123 DAY
flush privileges;
show create user user1@localhost;
CREATE USER for user1@localhost
CREATE USER `user1`@`localhost` PASSWORD EXPIRE NEVER
alter user user1@localhost password expire;
show create user user1@localhost;
CREATE USER for user1@localhost
CREATE USER `user1`@`localhost` PASSWORD EXPIRE
ALTER USER `user1`@`localhost` PASSWORD EXPIRE NEVER
flush privileges;
show create user user1@localhost;
CREATE USER for user1@localhost
CREATE USER `user1`@`localhost` PASSWORD EXPIRE
ALTER USER `user1`@`localhost` PASSWORD EXPIRE NEVER
set global disconnect_on_expired_password=ON;
connect(localhost,user1,,test,MYSQL_PORT,MYSQL_SOCK);
connect con1,localhost,user1;
ERROR HY000: Your password has expired. To log in you must change it using a client that supports expired passwords
set global disconnect_on_expired_password=OFF;
connect con1,localhost,user1;
connection con1;
select 1;
ERROR HY000: You must SET PASSWORD before executing this statement
set password=password('');
select 1;
1
1
disconnect con1;
connection default;
drop user user1@localhost;
set global disconnect_on_expired_password=default;
set global default_password_lifetime=default;
# switching back from mysql.user to mysql.global_priv
#
# PASSWORD EXPIRE DEFAULT should use the default_password_lifetime
# system var to set the number of days till expiration
#
set global disconnect_on_expired_password= ON;
set global default_password_lifetime= 2;
create user user1@localhost password expire default;
set @tstamp_expired= UNIX_TIMESTAMP(NOW() - INTERVAL 3 DAY);
update mysql.global_priv set
priv=json_set(priv, '$.password_last_changed', @tstamp_expired)
where user='user1';
flush privileges;
connect(localhost,user1,,test,MYSQL_PORT,MYSQL_SOCK);
connect con1,localhost,user1;
ERROR HY000: Your password has expired. To log in you must change it using a client that supports expired passwords
drop user user1@localhost;
#
# PASSWORD EXPIRE INTERVAL should expire a client's password after
# X days and not before
#
set global disconnect_on_expired_password= ON;
create user user1@localhost password expire interval 2 day;
connect con1,localhost,user1;
disconnect con1;
connection default;
set @tstamp_expired= UNIX_TIMESTAMP(NOW() - INTERVAL 3 DAY);
update mysql.global_priv set
priv=json_set(priv, '$.password_last_changed', @tstamp_expired)
where user='user1';
flush privileges;
connect(localhost,user1,,test,MYSQL_PORT,MYSQL_SOCK);
connect con1,localhost,user1;
ERROR HY000: Your password has expired. To log in you must change it using a client that supports expired passwords
drop user user1@localhost;
#
# PASSWORD EXPIRE NEVER should override the other policies and never
# expire a client's password
#
set global disconnect_on_expired_password= ON;
create user user1@localhost password expire interval 2 day;
alter user user1@localhost password expire never;
set @tstamp_expired= UNIX_TIMESTAMP() - 3;
update mysql.global_priv set
priv=json_set(priv, '$.password_last_changed', @tstamp_expired)
where user='user1';
flush privileges;
connect con1,localhost,user1;
disconnect con1;
connection default;
drop user user1@localhost;
set global disconnect_on_expired_password= default;
set global default_password_lifetime= default;
Reference in a new issue View git blame Copy permalink