1--source include/not_embedded.inc
2# Save the initial number of concurrent sessions
3--source include/count_sessions.inc
4call mtr.add_suppression('Corresponding entry in binary log used default authentication.*');
5
6--source include/master-slave.inc
7--connection master
8SHOW GLOBAL VARIABLES LIKE 'log_builtin_as_identified_by_password';
9
10--echo #
11--echo # WL#8540: Support IF [NOT] EXISTS clause in CREATE/DROP USER
12--echo #
13CREATE USER u1@localhost;
14CREATE USER IF NOT EXISTS u1@localhost, u2@localhost;
15ALTER USER IF EXISTS u1@localhost ACCOUNT LOCK;
16DROP USER u1@localhost;
17DROP USER IF EXISTS u2@localhost;
18
19--echo #
20--echo # Bug #21807286: "CREATE USER IF NOT EXISTS" REPORTS AN ERROR
21--echo #
22CREATE USER IF NOT EXISTS b21807286@localhost IDENTIFIED BY 'haha';
23CREATE USER IF NOT EXISTS b21807286@localhost IDENTIFIED BY 'haha2';
24ALTER USER IF EXISTS b21807286@localhost IDENTIFIED BY 'haha3';
25ALTER USER IF EXISTS b21807286_not_exists@localhost IDENTIFIED BY 'haha4';
26DROP USER b21807286@localhost;
27
28--source include/sync_slave_sql_with_master.inc
29--echo [On Slave]
30--source include/show_binlog_events.inc
31
32--echo #
33--echo # Bug #25813089: "CREATE USER IF NOT EXISTS SHOULD ALWAYS
34--echo #                 BE WRITTEN TO THE BINLOGS"
35--echo #
36
37# Scenario 1: Create/Alter user executed on a single user
38
39--echo # ------
40--echo "Scenario 1: CREATE/ALTER USER IF[NOT]EXISTS FOR A SINGLE USER"
41--echo # ------
42--connection master
43# Save master position
44--let $saved_master_pos= query_get_value('SHOW MASTER STATUS', Position, 1)
45--let $binlog_start= $saved_master_pos
46
47CREATE USER u1@localhost IDENTIFIED BY 'pass1';
48CREATE USER IF NOT EXISTS u1@localhost IDENTIFIED BY 'pass2';
49ALTER USER u1@localhost IDENTIFIED BY 'pass3';
50ALTER USER IF EXISTS u1@localhost IDENTIFIED BY 'pass4';
51ALTER USER IF EXISTS u1_not_exists@localhost IDENTIFIED BY 'pass5';
52DROP USER u1@localhost;
53
54--echo [On Master]
55--source include/show_binlog_events.inc
56
57--source include/sync_slave_sql_with_master.inc
58--echo [On Slave]
59--source include/show_binlog_events.inc
60
61--echo "Creating a user only on slave"
62CREATE USER IF NOT EXISTS slave_user@localhost IDENTIFIED BY 'slave_pass';
63
64# Scenario 2: Create/Alter user executed on multiple users
65# irrespective of whether they are present on master or not
66
67--echo # ------
68--echo "Scenario 2: CREATE/ALTER USER IF[NOT]EXISTS FOR MULTIPLE USERS"
69--echo # ------
70--connection master
71# Save master position
72--let $saved_master_pos= query_get_value('SHOW MASTER STATUS', Position, 1)
73--let $binlog_start= $saved_master_pos
74CREATE USER IF NOT EXISTS u1@localhost IDENTIFIED BY 'pass1',
75                          u2@localhost IDENTIFIED BY 'pass2';
76
77CREATE USER IF NOT EXISTS u1@localhost IDENTIFIED BY 'pass1',
78                          u2@localhost IDENTIFIED BY 'pass2',
79                          u3@localhost IDENTIFIED BY 'pass3';
80
81ALTER USER IF EXISTS u1@localhost IDENTIFIED BY 'pass4',
82                          u2@localhost IDENTIFIED BY 'pass5',
83                          u3@localhost IDENTIFIED BY 'pass6',
84                          u4_not_exists@localhost IDENTIFIED BY 'pass7',
85                          slave_user@localhost IDENTIFIED BY 'pass8';
86DROP USER IF EXISTS u1@localhost, u2@localhost, u3@localhost, slave_user@localhost;
87
88--echo [On Master]
89--source include/show_binlog_events.inc
90
91--source include/sync_slave_sql_with_master.inc
92--echo [On Slave]
93--source include/show_binlog_events.inc
94--source include/stop_slave_io.inc
95
96# Scenario 3: Make sure that the default authentication plugin is not used
97# when the plugin is specified explicitly while creating or altering users.
98
99--echo # ------
100--echo "Scenario 3: CREATE/ALTER USER IF[NOT]EXISTS WITH"
101--echo "AUTHENTICATION PLUGIN PROVIDED EXPLICITLY"
102--echo # ------
103--connection master
104--let $rpl_server_number= 1
105--let $rpl_server_parameters= --default_authentication_plugin=sha256_password
106--source include/rpl_restart_server.inc
107
108--connection slave
109--source include/start_slave_io.inc
110
111--connection master
112--disable_warnings
113--source include/rpl_reset.inc
114--enable_warnings
115
116# Save master position
117--let $saved_master_pos= query_get_value('SHOW MASTER STATUS', Position, 1)
118--let $binlog_start= $saved_master_pos
119
120SHOW VARIABLES LIKE 'default_authentication_plugin';
121CREATE USER IF NOT EXISTS u1@localhost IDENTIFIED WITH mysql_native_password by 'pass_native';
122CREATE USER IF NOT EXISTS u1@localhost IDENTIFIED WITH mysql_native_password by 'pass_native2';
123ALTER USER IF EXISTS u1@localhost IDENTIFIED WITH mysql_native_password BY 'pass_native3';
124ALTER USER IF EXISTS u2_not_exists@localhost IDENTIFIED WITH mysql_native_password BY 'pass_native4';
125SELECT user,authentication_string FROM mysql.user WHERE user='u1';
126DROP USER u1@localhost;
127
128--echo [On Master]
129--source include/show_binlog_events.inc
130
131--source include/sync_slave_sql_with_master.inc
132--echo [On Slave]
133--source include/show_binlog_events.inc
134
135################################################################################
136# Bug#26680035  CREATE/ALTER USER ERRORS OUT BUT GETS RECORDED IN THE BINARY LOG
137#
138# Problem: create/alter user when used with invalid plugin errors out,
139#          but still gets recorded in binary log.
140#
141# Steps to reproduce:
142#   1) Scenario 1:
143#       1.1) Create user with invalid plugin.
144#       1.2) Create user having 'if not exists' clause with invalid plugin.
145#   2) Scenario 2:
146#       2.1) Alter user with invalid plugin.
147#       2.2) Alter user having 'if exists' clause with invalid plugin.
148#   3) Create/Alter user having multiple users with invalid plugin.
149#   4) Cleanup
150################################################################################
151--echo #
152--echo # Bug26680035:  CREATE/ALTER USER ERRORS OUT BUT GETS RECORDED IN
153--echo #               THE BINARY LOG
154--echo #
155call mtr.add_suppression("Following users were specified in CREATE USER IF NOT EXISTS but they already exist. *");
156
157# Scenario 1: Create user with invalid plugin
158--echo # ------
159--echo "Scenario 1: CREATE USER WITH INVALID PLUGIN"
160--echo # ------
161
162--let $master_pos_before_create_user= query_get_value(SHOW MASTER STATUS,Position,1)
163--let $binlog_start= $master_pos_before_create_user
164--echo #
165--echo # Step 1.1) Create user with invalid plugin.
166--echo #
167--error ER_PLUGIN_IS_NOT_LOADED
168CREATE USER u1@localhost IDENTIFIED WITH 'plugin1';
169
170--echo #
171--echo # Step 1.2) Create user having 'if not exists' clause with invalid
172--echo #           plugin.
173--echo #
174--error ER_PLUGIN_IS_NOT_LOADED
175CREATE USER IF NOT EXISTS u1@localhost IDENTIFIED WITH 'plugin2';
176
177# Assert that the above statements are not written to binlog.
178--let $master_pos_after_create_user= query_get_value(SHOW MASTER STATUS,Position,1)
179--let $assert_text= assert that the above events are not written to binlog
180--let $assert_cond= $master_pos_before_create_user = $master_pos_after_create_user
181--source include/assert.inc
182
183
184# Scenario 2: Alter user with invalid plugin
185--echo # ------
186--echo "Scenario 2: ALTER USER WITH INVALID PLUGIN"
187--echo # ------
188
189CREATE USER u2@localhost;
190--let $master_pos_before_alter_user= query_get_value(SHOW MASTER STATUS,Position,1)
191--echo #
192--echo # Step 2.1) Alter user with invalid plugin.
193--echo #
194--error ER_PLUGIN_IS_NOT_LOADED
195ALTER USER u2@localhost IDENTIFIED WITH 'plugin3';
196
197--echo #
198--echo # Step 2.2) Alter user having 'if exists' clause with invalid plugin.
199--echo #
200--error ER_PLUGIN_IS_NOT_LOADED
201ALTER USER IF EXISTS u2@localhost IDENTIFIED WITH 'plugin4';
202
203# Assert that the above statements are not written to binlog.
204--let $master_pos_after_alter_user= query_get_value(SHOW MASTER STATUS,Position,1)
205--let $assert_text= assert that the above events are not written to binlog
206--let $assert_cond= $master_pos_before_alter_user = $master_pos_after_alter_user
207--source include/assert.inc
208
209
210# Scenario 3: Create/Alter user with invalid plugin with multiple users
211--echo # ------
212--echo "Scenario 3: CREATE/ALTER USER INVALID PLUGIN WITH MULTIPLE USERS"
213--echo # ------
214
215--echo #
216--echo # Step 3) Create/Alter user having multiple users with invalid plugin.
217--echo #
218
219# CREATE throws ERROR since shsh auth_plugin does not exist
220# but will be written to binlog as,u2 already exists and u3 will be created.
221--error ER_PLUGIN_IS_NOT_LOADED
222CREATE USER IF NOT EXISTS u1@localhost IDENTIFIED WITH 'shsh',
223                          u2@localhost IDENTIFIED BY 'pass',
224                          u3@localhost IDENTIFIED WITH 'sha256_password';
225
226# ALTER throws ERROR since shsh auth_plugin does not exist
227# but will be written to binlog as, u2 is altered.
228--error ER_PLUGIN_IS_NOT_LOADED
229ALTER USER IF EXISTS u1@localhost IDENTIFIED WITH 'shsh',
230                     u2@localhost IDENTIFIED BY 'xyz',
231                     u3@localhost IDENTIFIED WITH 'sha256_password';
232
233# Check whether the above statements are written to binlog or not.
234--source include/show_binlog_events.inc
235
236--echo #
237--echo # Step 4) Cleanup
238--echo #
239DROP USER u2@localhost,u3@localhost;
240
241# Search for the warnings in the server log
242let server_log= $MYSQLTEST_VARDIR/log/mysqld.1.err;
243let SEARCH_FILE= $server_log;
244--echo # Search warnings in the server log
245
246--let SEARCH_PATTERN= Following users were specified in CREATE USER IF NOT EXISTS
247--source include/search_pattern_in_file.inc
248
249--let SEARCH_PATTERN= Following users were specified in ALTER USER IF EXISTS
250--source include/search_pattern_in_file.inc
251
252#Clean up
253--source include/rpl_end.inc
254
255--echo
256--echo End of 5.7 tests!
257--echo
258