1# 2# Test password expiration 3# 4 5--source include/not_embedded.inc 6 7--echo # 8--echo # Only privileged users should be able to expire passwords 9--echo # 10create user user1@localhost; 11alter user user1@localhost password expire; 12 13create user user2@localhost; 14--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK 15connect(con2,localhost,user2); 16connection con2; 17--error ER_SPECIFIC_ACCESS_DENIED_ERROR 18alter user user1@localhost password expire; 19 20disconnect con2; 21connection default; 22drop user user1@localhost; 23drop user user2@localhost; 24 25--echo # 26--echo # disconnect_on_expired_password=ON should deny a clients's connection 27--echo # when the password is expired or put the client in sandbox mode if OFF 28--echo # 29create user user1@localhost password expire; 30set global disconnect_on_expired_password=ON; 31--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK 32--error ER_MUST_CHANGE_PASSWORD_LOGIN 33connect(con1,localhost,user1); 34 35# should allow the client to enter sandbox mode 36set global disconnect_on_expired_password=OFF; 37--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK 38connect(con1,localhost,user1); 39connection con1; 40--error ER_MUST_CHANGE_PASSWORD 41select 1; 42disconnect con1; 43connection default; 44drop user user1@localhost; 45 46--echo # 47--echo # connect-expired-password option passed to client should override 48--echo # the behavior of disconnect_on_expired_password server system var. 49--echo # 50create user user1@localhost password expire; 51set global disconnect_on_expired_password=ON; 52--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK 53--error ER_MUST_CHANGE_PASSWORD_LOGIN 54connect(con1,localhost,user1); 55 56--exec $MYSQL --connect-expired-password -u user1 -e "set password=password('');" 57drop user user1@localhost; 58 59--echo # 60--echo # Manually expiring a password should have immediate effect 61--echo # 62create user user1@localhost; 63alter user user1@localhost password expire; 64set global disconnect_on_expired_password=ON; 65--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK 66--error ER_MUST_CHANGE_PASSWORD_LOGIN 67connect(con1,localhost,user1); 68drop user user1@localhost; 69 70--echo # 71--echo # Sandbox mode should only allow change password statements 72--echo # 73create user user1@localhost password expire; 74grant create user on *.* to user1@localhost; 75set global disconnect_on_expired_password=OFF; 76--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK 77connect(con1,localhost,user1); 78connection con1; 79--error ER_MUST_CHANGE_PASSWORD 80select 1; 81set password=password(''); 82select 1; 83disconnect con1; 84connection default; 85 86drop user user1@localhost; 87 88--echo # 89--echo # Passwords are still expired after acl reload 90--echo # 91set global disconnect_on_expired_password=ON; 92create user user1@localhost password expire; 93flush privileges; 94--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK 95--error ER_MUST_CHANGE_PASSWORD_LOGIN 96connect(con1,localhost,user1); 97drop user user1@localhost; 98 99--echo # 100--echo # JSON functions on global_priv reflect the correct state 101--echo # of the password expiration columns 102--echo # 103 104create user user1@localhost password expire; 105select host, user, JSON_VALUE(Priv, '$.password_last_changed') from mysql.global_priv where user='user1'; 106alter user user1@localhost password expire never; 107select host, user, JSON_VALUE(Priv, '$.password_lifetime') from mysql.global_priv where user='user1'; 108alter user user1@localhost password expire default; 109select host, user, JSON_VALUE(Priv, '$.password_lifetime') from mysql.global_priv where user='user1'; 110alter user user1@localhost password expire interval 123 day; 111select host, user, JSON_VALUE(Priv, '$.password_lifetime') from mysql.global_priv where user='user1'; 112drop user user1@localhost; 113 114--echo # 115--echo # SHOW CREATE USER correctly displays the locking state of an user 116--echo # 117 118create user user1@localhost; 119show create user user1@localhost; 120alter user user1@localhost password expire; 121show create user user1@localhost; 122set password for user1@localhost= password(''); 123alter user user1@localhost password expire default; 124show create user user1@localhost; 125alter user user1@localhost password expire never; 126show create user user1@localhost; 127alter user user1@localhost password expire interval 123 day; 128show create user user1@localhost; 129alter user user1@localhost password expire; 130show create user user1@localhost; 131set password for user1@localhost= password(''); 132show create user user1@localhost; 133drop user user1@localhost; 134 135--echo # 136--echo # Incorrect INTERVAL values should be rejected 137--echo # 138--error ER_WRONG_VALUE 139create user user1@localhost password expire interval 0 day; 140 141--echo # 142--echo # Password expiration fields are loaded properly on 10.3 tables 143--echo # 144--source include/switch_to_mysql_user.inc 145create user user1@localhost; 146show create user user1@localhost; 147flush privileges; 148show create user user1@localhost; 149 150alter user user1@localhost password expire; 151show create user user1@localhost; 152flush privileges; 153show create user user1@localhost; 154set password for user1@localhost= password(''); 155 156alter user user1@localhost password expire default; 157show create user user1@localhost; 158flush privileges; 159show create user user1@localhost; 160 161alter user user1@localhost password expire never; 162show create user user1@localhost; 163flush privileges; 164show create user user1@localhost; 165 166alter user user1@localhost password expire interval 123 day; 167show create user user1@localhost; 168flush privileges; 169show create user user1@localhost; 170 171alter user user1@localhost password expire; 172show create user user1@localhost; 173flush privileges; 174show create user user1@localhost; 175 176set global disconnect_on_expired_password=ON; 177--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK 178--error ER_MUST_CHANGE_PASSWORD_LOGIN 179connect(con1,localhost,user1); 180 181set global disconnect_on_expired_password=OFF; 182--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK 183connect(con1,localhost,user1); 184connection con1; 185--error ER_MUST_CHANGE_PASSWORD 186select 1; 187set password=password(''); 188select 1; 189disconnect con1; 190connection default; 191drop user user1@localhost; 192 193set global disconnect_on_expired_password=default; 194set global default_password_lifetime=default; 195--source include/switch_to_mysql_global_priv.inc 196 197# 198# Test password expiration INTERVAL and default_password_lifetime options 199# 200 201--echo # 202--echo # PASSWORD EXPIRE DEFAULT should use the default_password_lifetime 203--echo # system var to set the number of days till expiration 204--echo # 205set global disconnect_on_expired_password= ON; 206set global default_password_lifetime= 2; 207create user user1@localhost password expire default; 208 209set @tstamp_expired= UNIX_TIMESTAMP(NOW() - INTERVAL 3 DAY); 210update mysql.global_priv set 211 priv=json_set(priv, '$.password_last_changed', @tstamp_expired) 212 where user='user1'; 213flush privileges; 214 215--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK 216--error ER_MUST_CHANGE_PASSWORD_LOGIN 217connect(con1,localhost,user1); 218drop user user1@localhost; 219 220--echo # 221--echo # PASSWORD EXPIRE INTERVAL should expire a client's password after 222--echo # X days and not before 223--echo # 224set global disconnect_on_expired_password= ON; 225create user user1@localhost password expire interval 2 day; 226--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK 227connect(con1,localhost,user1); 228disconnect con1; 229connection default; 230 231set @tstamp_expired= UNIX_TIMESTAMP(NOW() - INTERVAL 3 DAY); 232update mysql.global_priv set 233 priv=json_set(priv, '$.password_last_changed', @tstamp_expired) 234 where user='user1'; 235flush privileges; 236 237--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK 238--error ER_MUST_CHANGE_PASSWORD_LOGIN 239connect(con1,localhost,user1); 240drop user user1@localhost; 241 242--echo # 243--echo # PASSWORD EXPIRE NEVER should override the other policies and never 244--echo # expire a client's password 245--echo # 246set global disconnect_on_expired_password= ON; 247create user user1@localhost password expire interval 2 day; 248alter user user1@localhost password expire never; 249 250set @tstamp_expired= UNIX_TIMESTAMP() - 3; 251update mysql.global_priv set 252 priv=json_set(priv, '$.password_last_changed', @tstamp_expired) 253 where user='user1'; 254flush privileges; 255 256--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK 257connect(con1,localhost,user1); 258disconnect con1; 259connection default; 260drop user user1@localhost; 261 262set global disconnect_on_expired_password= default; 263set global default_password_lifetime= default; 264