1--source include/not_embedded.inc 2--echo # 3--echo # MDEV-11170: MariaDB 10.2 cannot start on MySQL 5.7 datadir: 4--echo # Fatal error: mysql.user table is damaged or in 5--echo # unsupported 3.20 format 6--echo # 7 8--source include/switch_to_mysql_user.inc 9 10--echo # 11--echo # Original mysql.user table 12--echo # 13describe mysql.user; 14 15--echo # 16--echo # Drop the password column. 17--echo # 18alter table mysql.user drop column password, 19 drop column is_role, 20 drop column default_role, 21 add column password_last_changed timestamp null default null after password_expired, 22 add column password_lifetime smallint unsigned after password_last_changed, 23 add column account_locked enum('n','y') character set utf8 not null default 'n' after password_lifetime; 24flush privileges; 25 26--echo # 27--echo # Create users without the password column present. 28--echo # 29create user foo; 30create user goo identified by "foo"; 31select OLD_PASSWORD("ioo"); 32create user ioo identified with "mysql_old_password" as "7a8f886d28473e85"; 33 34--echo # 35--echo # Check if users have grants loaded correctly. 36--echo # 37show grants for foo; 38show grants for goo; 39show grants for ioo; 40 41select user, host, select_priv, plugin, authentication_string from mysql.user 42where user like "%oo" 43order by user; 44 45--echo # 46--echo # Test setting password. 47--echo # 48SET PASSWORD FOR foo=PASSWORD("bar"); 49 50show grants for foo; 51show grants for goo; 52show grants for ioo; 53 54select user, host, select_priv, plugin, authentication_string from mysql.user 55where user like "%oo" 56order by user; 57 58--echo # 59--echo # Test flush privileges without password column. 60--echo # 61flush privileges; 62show grants for foo; 63show grants for goo; 64show grants for ioo; 65 66--echo # 67--echo # Test granting of privileges. 68--echo # 69grant select on *.* to foo; 70grant select on *.* to goo; 71grant select on *.* to ioo; 72show grants for foo; 73show grants for goo; 74show grants for ioo; 75 76--echo # 77--echo # Check to see if grants are stable on flush. 78--echo # 79flush privileges; 80show grants for foo; 81show grants for goo; 82show grants for ioo; 83 84--echo # 85--echo # Check internal table representation. 86--echo # 87select user, host, select_priv, plugin, authentication_string from mysql.user 88where user like "%oo" 89order by user; 90 91--echo # 92--echo # Test account locking 93--echo # 94create user user1@localhost account lock; 95--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK 96--error ER_ACCOUNT_HAS_BEEN_LOCKED 97connect(con1,localhost,user1); 98flush privileges; 99--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK 100--error ER_ACCOUNT_HAS_BEEN_LOCKED 101connect(con1,localhost,user1); 102show create user user1@localhost; 103alter user user1@localhost account unlock; 104connect(con1,localhost,user1); 105disconnect con1; 106connection default; 107show create user user1@localhost; 108 109--echo # 110--echo # Test password expiration fields are loaded correctly 111--echo # 112create user user@localhost; 113show create user user@localhost; 114alter user user@localhost password expire; 115show create user user@localhost; 116set password for user@localhost= password(''); 117alter user user@localhost password expire default; 118show create user user@localhost; 119alter user user@localhost password expire never; 120show create user user@localhost; 121alter user user@localhost password expire interval 123 day; 122show create user user@localhost; 123alter user user@localhost password expire; 124show create user user@localhost; 125set password for user@localhost= password(''); 126show create user user@localhost; 127drop user user@localhost; 128 129--echo # 130--echo # Reset to final original state. 131--echo # 132--source include/switch_to_mysql_global_priv.inc 133