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
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
|
include/save_binlog_position.inc
# ----------------------------------------------------------------------
# Begin : Tests for CREATE USER
CREATE USER userX;
SELECT user FROM mysql.user WHERE user LIKE 'user%';
user
userX
# Case 1 : Execute CREATE USER for existing user
CREATE USER userY, userZ, userX;
ERROR HY000: Operation CREATE USER failed for 'userX'@'%'
# Must not show entries for userY and userZ
SELECT user FROM mysql.user WHERE user LIKE 'user%';
user
userX
# This event sequence pattern MUST be present in binlog: !Q(CREATE USER .userX.*)
include/assert_binlog_events.inc
# This event sequence pattern MUST NOT be present in binlog: !Q(CREATE USER .*userY.*userZ.*)
include/assert_binlog_events.inc
include/save_binlog_position.inc
# Case 2 : Execute CREATE USER for non-existing users
CREATE USER userY, userZ;
# Must show entries for userX, userY and userZ
SELECT user FROM mysql.user WHERE user LIKE 'user%';
user
userX
userY
userZ
# This event sequence pattern MUST be present in binlog: !Q(CREATE USER .userY.*userZ.*)
include/assert_binlog_events.inc
include/save_binlog_position.inc
# Case 3 : Try creating anonymous user with expired password
CREATE USER ''@localhost,
userA IDENTIFIED BY 'abcd'
PASSWORD EXPIRE;
ERROR HY000: Operation CREATE USER failed for anonymous user
CREATE USER ''@'' PASSWORD EXPIRE;
ERROR HY000: Operation CREATE USER failed for anonymous user
# Must not show ''@localhost or ''@''
SELECT user FROM mysql.user WHERE user LIKE 'user%';
user
userX
userY
userZ
# Case 4 : Try creating user with invalid plugin
CREATE USER userA IDENTIFIED BY 'abcd', userB IDENTIFIED WITH 'blahblah';
ERROR HY000: Plugin 'blahblah' is not loaded
# Case 5 : Try creating user with invalid hash
CREATE USER userA IDENTIFIED BY 'abcd',
userB IDENTIFIED WITH 'mysql_native_password' AS 'hahaha';
ERROR HY000: The password hash doesn't have the expected format.
# Must not show userA, userB
SELECT user FROM mysql.user WHERE user LIKE 'user%';
user
userX
userY
userZ
# Check binlog for Cases 3, 4 and 5 above
# This event sequence pattern MUST NOT be present in binlog: !Q(CREATE USER.*)
include/assert_binlog_events.inc
# Case 6 : Add a row in mysql.user without FLUSH PRIVILEGES and
# execute CREATE USER for same user
# Insert an entry in mysql.user table but do not execute FLUSH PRIVILEGES
CREATE TABLE mysql.tmp_user LIKE mysql.user;
INSERT INTO mysql.tmp_user SELECT * FROM mysql.user WHERE user LIKE 'userX';
UPDATE mysql.tmp_user SET user='userW' WHERE user LIKE 'userX';
INSERT INTO mysql.user SELECT * FROM mysql.tmp_user;
DROP TABLE mysql.tmp_user;
include/save_binlog_position.inc
# Though userW is not present in cache, CREATE USER should fail
# because we will encounter duplicate key error while inserting entry
# in mysql.user table for userW.
CREATE USER userW IDENTIFIED BY 'abcd', userV IDENTIFIED BY 'haha';
ERROR HY000: Operation CREATE USER failed for 'userW'@'%'
# Must not show userV
SELECT user FROM mysql.user WHERE user LIKE 'user%';
user
userW
userX
userY
userZ
# This event sequence pattern MUST NOT be present in binlog: !Q(CREATE USER.*)
include/assert_binlog_events.inc
FLUSH PRIVILEGES;
DROP USER userW, userX, userY, userZ;
include/save_binlog_position.inc
# End : Tests for CREATE USER
# ----------------------------------------------------------------------
# BEGIN : Tests for CREATE USER IF NOT EXISTS
CALL mtr.add_suppression("Following users were specified in CREATE USER IF NOT EXISTS but they already exist.");
CREATE USER userX;
include/save_binlog_position.inc
# Case 1 : Execute CREATE USER IF NOT EXISTS for existing user
CREATE USER IF NOT EXISTS userX, userY, userZ;
Warnings:
Note 3163 Authorization ID 'userX'@'%' already exists.
# Must show entries for userX, userY, userZ
SELECT user FROM mysql.user WHERE user LIKE 'user%';
user
userX
userY
userZ
# This event sequence pattern MUST be present in binlog: !Q(CREATE USER IF NOT EXISTS.*userX.*userY.*userZ.*)
include/assert_binlog_events.inc
include/save_binlog_position.inc
# Must show warnings about userX, userY and userZ
CREATE USER IF NOT EXISTS userX, userY, userZ;
Warnings:
Note 3163 Authorization ID 'userX'@'%' already exists.
Note 3163 Authorization ID 'userY'@'%' already exists.
Note 3163 Authorization ID 'userZ'@'%' already exists.
# This event sequence pattern MUST be present in binlog: !Q(CREATE USER IF NOT EXISTS.*userX.*userY.*userZ.*)
include/assert_binlog_events.inc
include/save_binlog_position.inc
# Case 2 : Try creating anonymous user with expired password
# using CREATE USER IF NOT EXISTS
CREATE USER IF NOT EXISTS ''@'' PASSWORD EXPIRE;
ERROR HY000: Operation CREATE USER failed for anonymous user
CREATE USER IF NOT EXISTS ''@localhost PASSWORD EXPIRE;
ERROR HY000: Operation CREATE USER failed for anonymous user
# Case 3 : Try creating user with invalid plugin
CREATE USER IF NOT EXISTS userA IDENTIFIED BY 'abcd', userB IDENTIFIED WITH 'blahblah', userX;
ERROR HY000: Plugin 'blahblah' is not loaded
# Case 4 : Try creating user with invalid hash
CREATE USER IF NOT EXISTS userA IDENTIFIED BY 'abcd',
userB IDENTIFIED WITH 'mysql_native_password' AS 'hahaha';
ERROR HY000: The password hash doesn't have the expected format.
# Must not show userA, userB
SELECT user FROM mysql.user WHERE user LIKE 'user%';
user
userX
userY
userZ
# This event sequence pattern MUST NOT be present in binlog: !Q(CREATE USER.*)
include/assert_binlog_events.inc
# Case 6 : Add a row in mysql.user without FLUSH PRIVILEGES and
# execute CREATE USER IF NOT for same user
# Insert an entry in mysql.user table but do not execute FLUSH PRIVILEGES
CREATE TABLE mysql.tmp_user LIKE mysql.user;
INSERT INTO mysql.tmp_user SELECT * FROM mysql.user WHERE user LIKE 'userX';
UPDATE mysql.tmp_user SET user='userW' WHERE user LIKE 'userX';
INSERT INTO mysql.user SELECT * FROM mysql.tmp_user;
DROP TABLE mysql.tmp_user;
include/save_binlog_position.inc
# Though userW is not present in cache, CREATE USER IF NOT EXISTS
# will skip it because we will encounter duplicate key error while
# inserting entry in mysql.user table for userW.
# Must show warning about userW@%.
CREATE USER IF NOT EXISTS userW IDENTIFIED BY 'abcd',
userV IDENTIFIED BY 'haha';
Warnings:
Note 3163 Authorization ID 'userW'@'%' already exists.
# Must show userV, userW, userX, userY, userZ
SELECT user FROM mysql.user WHERE user LIKE 'user%';
user
userV
userW
userX
userY
userZ
# This event sequence pattern MUST be present in binlog: !Q(CREATE USER.*userW.*userV.*)
include/assert_binlog_events.inc
DROP USER userV, userW, userX, userY, userZ;
include/save_binlog_position.inc
# END : Tests for CREATE USER IF NOT EXISTS
# ----------------------------------------------------------------------
# Begin : Tests for CREATE USER with DEFAULT ROLE
CREATE USER userX;
CREATE ROLE 'roleX', 'roleY', 'roleZ';
SELECT user FROM mysql.user WHERE user LIKE 'user%';
user
userX
# Case 1 : Execute CREATE USER for existing user
CREATE USER userY, userZ, userX DEFAULT ROLE 'roleX';
ERROR HY000: Operation CREATE USER failed for 'userX'@'%'
# Must not show entries for userY and userZ
SELECT user FROM mysql.user WHERE user LIKE 'user%';
user
userX
SELECT user FROM mysql.default_roles where user LIKE 'user%';
user
# This event sequence pattern MUST NOT be present in binlog: !Q(CREATE USER .*userY.*userZ.*.userX)
include/assert_binlog_events.inc
include/save_binlog_position.inc
# Case 2 : Execute CREATE USER for non-existing users
CREATE USER userY, userZ DEFAULT ROLE 'roleX', 'roleY';
# Must show entries for userX, userY and userZ
SELECT user FROM mysql.user WHERE user LIKE 'user%';
user
userX
userY
userZ
SELECT user FROM mysql.default_roles where user LIKE 'user%';
user
userY
userY
userZ
userZ
# This event sequence pattern MUST be present in binlog: !Q(CREATE USER .userY.*userZ.*)
include/assert_binlog_events.inc
include/save_binlog_position.inc
# Case 3 : Try creating user with invalid hash
CREATE USER userA IDENTIFIED BY 'abcd',
userB IDENTIFIED WITH 'mysql_native_password' AS 'hahaha'
DEFAULT ROLE roleX;
ERROR HY000: The password hash doesn't have the expected format.
# Must not show userA, userB
SELECT user FROM mysql.user WHERE user LIKE 'user%';
user
userX
userY
userZ
SELECT user FROM mysql.default_roles WHERE user LIKE 'user%';
user
userY
userY
userZ
userZ
# Check binlog for Cases 3
# This event sequence pattern MUST NOT be present in binlog: !Q(CREATE USER.*)
include/assert_binlog_events.inc
DROP USER userX, userY, userZ;
DROP ROLE roleX, roleY, roleZ;
include/save_binlog_position.inc
# ----------------------------------------------------------------------
|
