create user user1@localhost;
create user user2@localhost;
#
# Only privileged users should be able to lock/unlock.
#
alter user user1@localhost account lock;
alter user user1@localhost account unlock;
create user user3@localhost account lock;
drop user user3@localhost;
connect con1,localhost,user1;
connection con1;
alter user user2@localhost account lock;
ERROR 42000: Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation
disconnect con1;
connection default;
#
# ALTER USER USER1 ACCOUNT LOCK should deny the connection of user1,
# but it should allow user2 to connect.
#
alter user user1@localhost account lock;
connect(localhost,user1,,test,MYSQL_PORT,MYSQL_SOCK);
connect con1,localhost,user1;
ERROR HY000: Access denied, this account is locked
connect con2,localhost,user2;
disconnect con2;
connection default;
alter user user1@localhost account unlock;
#
# Passing an incorrect user should return an error unless
# IF EXISTS is used
#
alter user inexistentUser@localhost account lock;
ERROR HY000: Operation ALTER USER failed for 'inexistentUser'@'localhost'
alter user if exists inexistentUser@localhost account lock;
Warnings:
Error	1133	Can't find any matching row in the user table
Note	1396	Operation ALTER USER failed for 'inexistentUser'@'localhost'
#
# Passing an existing user to CREATE should not be allowed
# and it should not change the locking state of the current user
#
show create user user1@localhost;
CREATE USER for user1@localhost
CREATE USER `user1`@`localhost`
create user user1@localhost account lock;
ERROR HY000: Operation CREATE USER failed for 'user1'@'localhost'
show create user user1@localhost;
CREATE USER for user1@localhost
CREATE USER `user1`@`localhost`
#
# Passing multiple users should lock them all
#
alter user user1@localhost, user2@localhost account lock;
connect(localhost,user1,,test,MYSQL_PORT,MYSQL_SOCK);
connect con1,localhost,user1;
ERROR HY000: Access denied, this account is locked
connect(localhost,user2,,test,MYSQL_PORT,MYSQL_SOCK);
connect con2,localhost,user2;
ERROR HY000: Access denied, this account is locked
alter user user1@localhost, user2@localhost account unlock;
#
# The locking state is preserved after acl reload
#
alter user user1@localhost account lock;
flush privileges;
connect(localhost,user1,,test,MYSQL_PORT,MYSQL_SOCK);
connect con1,localhost,user1;
ERROR HY000: Access denied, this account is locked
alter user user1@localhost account unlock;
#
# JSON functions on global_priv reflect the locking state of an account
#
alter user user1@localhost account lock;
select host, user, JSON_VALUE(Priv, '$.account_locked') from mysql.global_priv where user='user1';
host	user	JSON_VALUE(Priv, '$.account_locked')
localhost	user1	1
alter user user1@localhost account unlock;
select host, user, JSON_VALUE(Priv, '$.account_locked') from mysql.global_priv where user='user1';
host	user	JSON_VALUE(Priv, '$.account_locked')
localhost	user1	0
#
# SHOW CREATE USER correctly displays the locking state of an user
#
show create user user1@localhost;
CREATE USER for user1@localhost
CREATE USER `user1`@`localhost`
alter user user1@localhost account lock;
show create user user1@localhost;
CREATE USER for user1@localhost
CREATE USER `user1`@`localhost` ACCOUNT LOCK
alter user user1@localhost account unlock;
show create user user1@localhost;
CREATE USER for user1@localhost
CREATE USER `user1`@`localhost`
create user newuser@localhost account lock;
show create user newuser@localhost;
CREATE USER for newuser@localhost
CREATE USER `newuser`@`localhost` ACCOUNT LOCK
drop user newuser@localhost;
#
# Users should be able to lock themselves
#
grant CREATE USER on *.* to user1@localhost;
connect con1,localhost,user1;
connection con1;
alter user user1@localhost account lock;
disconnect con1;
connection default;
connect(localhost,user1,,test,MYSQL_PORT,MYSQL_SOCK);
connect con1,localhost,user1;
ERROR HY000: Access denied, this account is locked
alter user user1@localhost account unlock;
#
# Users should be able to unlock themselves if the connections
# had been established before the accounts were locked
#
grant CREATE USER on *.* to user1@localhost;
connect con1,localhost,user1;
alter user user1@localhost account lock;
connection con1;
alter user user1@localhost account unlock;
show create user user1@localhost;
CREATE USER for user1@localhost
CREATE USER `user1`@`localhost`
disconnect con1;
connection default;
#
# COM_CHANGE_USER should return error if the destination
# account is locked
#
alter user user1@localhost account lock;
ERROR HY000: Access denied, this account is locked
#
# MDEV-24098 SHOW CREATE USER invalid for both PASSWORD EXPIRE and
# and LOCKED
#
alter user user1@localhost PASSWORD EXPIRE;
show create user user1@localhost;
CREATE USER for user1@localhost
CREATE USER `user1`@`localhost` ACCOUNT LOCK PASSWORD EXPIRE
drop user user1@localhost;
#
# MDEV-24098 CREATE USER/ALTER USER PASSWORD EXPIRE/LOCK in
# either order.
#
create user user1@localhost PASSWORD EXPIRE ACCOUNT LOCK;
show create user user1@localhost;
CREATE USER for user1@localhost
CREATE USER `user1`@`localhost` ACCOUNT LOCK PASSWORD EXPIRE
drop user user1@localhost;
create user user1@localhost ACCOUNT LOCK PASSWORD EXPIRE;
show create user user1@localhost;
CREATE USER for user1@localhost
CREATE USER `user1`@`localhost` ACCOUNT LOCK PASSWORD EXPIRE
alter user user1@localhost  PASSWORD EXPIRE NEVER ACCOUNT UNLOCK ;
show create user user1@localhost;
CREATE USER for user1@localhost
CREATE USER `user1`@`localhost` PASSWORD EXPIRE
ALTER USER `user1`@`localhost` PASSWORD EXPIRE NEVER
alter user user1@localhost  ACCOUNT LOCK PASSWORD EXPIRE DEFAULT;
show create user user1@localhost;
CREATE USER for user1@localhost
CREATE USER `user1`@`localhost` ACCOUNT LOCK PASSWORD EXPIRE
alter user user1@localhost  PASSWORD EXPIRE INTERVAL 60 DAY ACCOUNT UNLOCK;
select * from mysql.global_priv where user='user1';
Host	User	Priv
localhost	user1	{"access":0,"version_id":XXX,"plugin":"mysql_native_password","authentication_string":"","account_locked":false,"password_last_changed":0,"password_lifetime":60}
show create user user1@localhost;
CREATE USER for user1@localhost
CREATE USER `user1`@`localhost` PASSWORD EXPIRE
ALTER USER `user1`@`localhost` PASSWORD EXPIRE INTERVAL 60 DAY
drop user user1@localhost;
drop user user2@localhost;