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