1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
|
--source include/not_group_replication_plugin.inc
--source include/not_have_privilege_checks_user.inc
# BUG#33862 completely failed DROP USER statement gets replicated
--source include/master-slave.inc
#
# remove all users will be used in the test
#
connection master;
set session sql_log_bin=0;
delete from mysql.user where Host='fakehost';
set session sql_log_bin=1;
connection slave;
set session sql_log_bin=0;
delete from mysql.user where Host='fakehost';
set session sql_log_bin=1;
#
# Test create user
#
connection master;
create user 'foo'@'fakehost';
--error ER_CANNOT_USER
create user 'foo'@'fakehost', 'bar'@'fakehost';
create user 'bar'@'fakehost';
# In log event, Plaintext password 'foo1' is replaced by ciphertext.
create user 'foo1'@'fakehost' IDENTIFIED WITH 'mysql_native_password' BY 'foo1',
'foo2'@'fakehost' IDENTIFIED WITH 'mysql_native_password' AS '*1111111111111111111111111111111111111111',
'foo3'@'fakehost';
--source include/sync_slave_sql_with_master.inc
select Host,User from mysql.user where Host='fakehost';
--let $diff_tables= master:mysql.user, slave:mysql.user
source include/diff_tables.inc;
#
# Test rename user
#
connection master;
rename user 'foo'@'fakehost' to 'foofoo'@'fakehost';
--error ER_CANNOT_USER
rename user 'not_exist_user1'@'fakehost' to 'foobar'@'fakehost', 'bar'@'fakehost' to 'barbar'@'fakehost';
--error ER_CANNOT_USER
rename user 'not_exist_user1'@'fakehost' to 'foobar'@'fakehost', 'not_exist_user2'@'fakehost' to 'barfoo'@'fakehost';
--source include/sync_slave_sql_with_master.inc
select Host,User from mysql.user where Host='fakehost';
#
# Test drop user
#
connection master;
drop user 'foofoo'@'fakehost';
drop user 'bar'@'fakehost';
drop user 'foo1'@'fakehost', 'foo2'@'fakehost', 'foo3'@'fakehost';
--error ER_CANNOT_USER
drop user 'not_exist_user1'@'fakehost', 'barbar'@'fakehost';
--error ER_CANNOT_USER
drop user 'not_exist_user1'@'fakehost', 'not_exist_user2'@'fakehost';
--source include/sync_slave_sql_with_master.inc
select Host,User from mysql.user where Host='fakehost';
--echo #
--echo # WL2392: "Change Password at next login" (initial default for root)
--echo #
connection master;
CREATE USER must_change2@localhost IDENTIFIED BY 'aha';
ALTER USER must_change2@localhost PASSWORD EXPIRE;
--source include/sync_slave_sql_with_master.inc
select Host,User,password_expired from mysql.user where user='must_change2';
connect(must_change_con_slave,localhost,must_change2,aha,test,$SLAVE_MYPORT,);
connection must_change_con_slave;
--echo # must throw an error
--error ER_MUST_CHANGE_PASSWORD
SELECT USER();
connection master;
disconnect must_change_con_slave;
connect(must_change_con_master,localhost,must_change2,aha);
connection must_change_con_master;
--echo # setting a password unlocks it
SET PASSWORD = 'aha2';
connection master;
disconnect must_change_con_master;
--source include/sync_slave_sql_with_master.inc
connection master;
connect(must_change_con_slave,localhost,must_change2,aha2,test,$SLAVE_MYPORT,);
connection must_change_con_slave;
--echo # must not throw an error
SELECT USER();
connection master;
disconnect must_change_con_slave;
DROP USER must_change2@localhost;
--source include/sync_slave_sql_with_master.inc
connection master;
--echo #
--echo # WL#9591: Caching sha2 authentication plugin
--echo #
connection master;
CREATE USER 9591_user@localhost IDENTIFIED WITH caching_sha2_password BY 'abcd' REQUIRE NONE;
--source include/sync_slave_sql_with_master.inc
SELECT Host, User, Plugin FROM mysql.user WHERE user='9591_user';
connect(9591_user_con_slave, localhost, 9591_user, abcd, test, $SLAVE_MYPORT,,SSL);
connection 9591_user_con_slave;
SELECT CURRENT_USER();
connection master;
disconnect 9591_user_con_slave;
connect(9591_user_con_master, localhost, 9591_user, abcd,,,,SSL);
connection 9591_user_con_master;
SELECT CURRENT_USER();
connection master;
disconnect 9591_user_con_master;
DROP USER 9591_user@localhost;
--source include/sync_slave_sql_with_master.inc
connection master;
--echo #
--echo # WL#11544: Current password required for SET PASSWORD
--echo # Create users with current password require clauses.
--echo #
connection master;
--echo [connection master]
CREATE USER 11544_u1@localhost REQUIRE NONE PASSWORD REQUIRE CURRENT;
CREATE USER 11544_u2@localhost REQUIRE NONE PASSWORD REQUIRE CURRENT OPTIONAL;
CREATE USER 11544_u3@localhost REQUIRE NONE PASSWORD REQUIRE CURRENT DEFAULT;
--source include/sync_slave_sql_with_master.inc
--echo [connection slave]
SELECT user, Password_require_current FROM mysql.user WHERE user like '11544_u%';
connection master;
--echo [connection master]
DROP USER 11544_u1@localhost;
DROP USER 11544_u2@localhost;
DROP USER 11544_u3@localhost;
#
# show the binlog events on the master
#
connection master;
--let $mask_user_password_events=1
source include/show_binlog_events.inc;
--let $mask_user_password_events=0
--source include/rpl_end.inc
|
