1# 2# Test user account locking 3# 4 5--source include/not_embedded.inc 6 7create user user1@localhost; 8create user user2@localhost; 9 10--echo # 11--echo # Only privileged users should be able to lock/unlock. 12--echo # 13alter user user1@localhost account lock; 14alter user user1@localhost account unlock; 15create user user3@localhost account lock; 16drop user user3@localhost; 17 18connect(con1,localhost,user1); 19connection con1; 20--error ER_SPECIFIC_ACCESS_DENIED_ERROR 21alter user user2@localhost account lock; 22disconnect con1; 23connection default; 24 25--echo # 26--echo # ALTER USER USER1 ACCOUNT LOCK should deny the connection of user1, 27--echo # but it should allow user2 to connect. 28--echo # 29 30alter user user1@localhost account lock; 31--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK 32--error ER_ACCOUNT_HAS_BEEN_LOCKED 33connect(con1,localhost,user1); 34connect(con2,localhost,user2); 35disconnect con2; 36connection default; 37alter user user1@localhost account unlock; 38 39--echo # 40--echo # Passing an incorrect user should return an error unless 41--echo # IF EXISTS is used 42--echo # 43 44--error ER_CANNOT_USER 45alter user inexistentUser@localhost account lock; 46 47alter user if exists inexistentUser@localhost account lock; 48 49--echo # 50--echo # Passing an existing user to CREATE should not be allowed 51--echo # and it should not change the locking state of the current user 52--echo # 53 54show create user user1@localhost; 55--error ER_CANNOT_USER 56create user user1@localhost account lock; 57show create user user1@localhost; 58 59--echo # 60--echo # Passing multiple users should lock them all 61--echo # 62 63alter user user1@localhost, user2@localhost account lock; 64--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK 65--error ER_ACCOUNT_HAS_BEEN_LOCKED 66connect(con1,localhost,user1); 67--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK 68--error ER_ACCOUNT_HAS_BEEN_LOCKED 69connect(con2,localhost,user2); 70alter user user1@localhost, user2@localhost account unlock; 71 72--echo # 73--echo # The locking state is preserved after acl reload 74--echo # 75 76alter user user1@localhost account lock; 77flush privileges; 78--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK 79--error ER_ACCOUNT_HAS_BEEN_LOCKED 80connect(con1,localhost,user1); 81alter user user1@localhost account unlock; 82 83--echo # 84--echo # JSON functions on global_priv reflect the locking state of an account 85--echo # 86 87alter user user1@localhost account lock; 88select host, user, JSON_VALUE(Priv, '$.account_locked') from mysql.global_priv where user='user1'; 89alter user user1@localhost account unlock; 90select host, user, JSON_VALUE(Priv, '$.account_locked') from mysql.global_priv where user='user1'; 91 92--echo # 93--echo # SHOW CREATE USER correctly displays the locking state of an user 94--echo # 95 96show create user user1@localhost; 97alter user user1@localhost account lock; 98show create user user1@localhost; 99alter user user1@localhost account unlock; 100show create user user1@localhost; 101create user newuser@localhost account lock; 102show create user newuser@localhost; 103drop user newuser@localhost; 104 105--echo # 106--echo # Users should be able to lock themselves 107--echo # 108grant CREATE USER on *.* to user1@localhost; 109connect(con1,localhost,user1); 110connection con1; 111alter user user1@localhost account lock; 112disconnect con1; 113connection default; 114--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK 115--error ER_ACCOUNT_HAS_BEEN_LOCKED 116connect(con1,localhost,user1); 117alter user user1@localhost account unlock; 118 119--echo # 120--echo # Users should be able to unlock themselves if the connections 121--echo # had been established before the accounts were locked 122--echo # 123grant CREATE USER on *.* to user1@localhost; 124connect(con1,localhost,user1); 125alter user user1@localhost account lock; 126connection con1; 127alter user user1@localhost account unlock; 128show create user user1@localhost; 129disconnect con1; 130connection default; 131 132--echo # 133--echo # COM_CHANGE_USER should return error if the destination 134--echo # account is locked 135--echo # 136alter user user1@localhost account lock; 137--error ER_ACCOUNT_HAS_BEEN_LOCKED 138--change_user user1 139 140--echo # 141--echo # MDEV-24098 SHOW CREATE USER invalid for both PASSWORD EXPIRE and 142--echo # and LOCKED 143--echo # 144alter user user1@localhost PASSWORD EXPIRE; 145show create user user1@localhost; 146drop user user1@localhost; 147 148--echo # 149--echo # MDEV-24098 CREATE USER/ALTER USER PASSWORD EXPIRE/LOCK in 150--echo # either order. 151--echo # 152create user user1@localhost PASSWORD EXPIRE ACCOUNT LOCK; 153show create user user1@localhost; 154drop user user1@localhost; 155create user user1@localhost ACCOUNT LOCK PASSWORD EXPIRE; 156show create user user1@localhost; 157alter user user1@localhost PASSWORD EXPIRE NEVER ACCOUNT UNLOCK ; 158show create user user1@localhost; 159alter user user1@localhost ACCOUNT LOCK PASSWORD EXPIRE DEFAULT; 160show create user user1@localhost; 161# note output needs to be corrected by MDEV-24114: password expire users cannot be unexpired 162alter user user1@localhost PASSWORD EXPIRE INTERVAL 60 DAY ACCOUNT UNLOCK; 163--replace_regex /"version_id":[0-9]*,/"version_id":XXX,/ 164select * from mysql.global_priv where user='user1'; 165show create user user1@localhost; 166 167drop user user1@localhost; 168drop user user2@localhost; 169 170