Debian Codegraph @

mysql-test/suite/x/r/connection_openssl_unixsocket.result

raw
1
2
3 ## Preamble
4 SET GLOBAL mysqlx_connect_timeout = 300;
5 call mtr.add_suppression("Maximum number of authentication attempts reached");
6 call mtr.add_suppression("Access denied for user .*");
7 CREATE USER user1_mysqlx@localhost
8 IDENTIFIED WITH 'mysql_native_password' BY 'auth_string1';
9 CREATE USER user2_mysqlx@localhost
10 IDENTIFIED WITH 'mysql_native_password' BY 'auth_string2'
11 PASSWORD EXPIRE;
12 CREATE USER user3_mysqlx@localhost
13 IDENTIFIED WITH 'mysql_native_password' BY 'auth_string3'
14 REQUIRE CIPHER "ECDHE-RSA-AES256-GCM-SHA384" AND
15 SUBJECT "/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client"
16 ISSUER "/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=CA"
17 PASSWORD EXPIRE NEVER;
18 CREATE USER user4_mysqlx@localhost REQUIRE SSL ACCOUNT LOCK;
19 CREATE USER user5_mysqlx@localhost
20 IDENTIFIED WITH 'mysql_native_password' BY 'auth_string'
21 REQUIRE SSL;
22 CREATE USER user6_mysqlx@localhost
23 IDENTIFIED WITH 'mysql_native_password' BY 'dwh@#ghd'
24 REQUIRE x509;
25 CREATE USER user7_mysqlx@localhost
26 IDENTIFIED WITH 'mysql_native_password' BY ''
27 REQUIRE CIPHER "ECDHE-RSA-AES256-GCM-SHA384";
28 GRANT ALL ON *.* TO user1_mysqlx@localhost;
29 GRANT ALL ON *.* TO user2_mysqlx@localhost;
30 GRANT ALL ON *.* TO user3_mysqlx@localhost;
31 GRANT ALL ON *.* TO user4_mysqlx@localhost;
32 GRANT ALL ON *.* TO user5_mysqlx@localhost;
33 GRANT ALL ON *.* TO user6_mysqlx@localhost;
34 GRANT ALL ON *.* TO user7_mysqlx@localhost;
35
36
37 ## I. Verify handling of SSL with normal account ('mysql_native_password')
38 #
39 Verify ["SELECT USER() as column_user" returns in "column_user" column, following value user1_mysqlx@localhost]
40 Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
41 Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1.2]
42 Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of ECDHE-RSA-AES128-GCM-SHA256]
43 Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 1]
44 Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 1]
45 Mysqlx.Ok {
46 msg: "bye!"
47 }
48 ok
49
50
51 ## II. Verify handling of SSL with super account and XProtocol message
52 ## CapabilitySet (without SSL options on it)
53 #
54 # 1. Try to set "tls" capability with invalid data (no-ssl connection)
55 # 2. Try to set "tls" capability with valid data (ssl connection)
56 #
57 # II.1
58 # setting read/write tls param with possible invalid data types:
59 # V_SINT,V_UINT ,V_NULL,V_OCTETS ,V_DOUBLE,V_FLOAT,V_STRING
60 send Mysqlx.Connection.CapabilitiesGet {
61 }
62
63 Mysqlx.Connection.Capabilities(capabilities[0]) = Mysqlx.Connection.Capability {
64 name: "tls"
65 value {
66 type: SCALAR
67 scalar {
68 type: V_BOOL
69 v_bool: false
70 }
71 }
72 }
73
74 # Checking CapSet TLS to bool 0
75 send Mysqlx.Connection.CapabilitiesSet {
76 capabilities {
77 capabilities {
78 name: "tls"
79 value {
80 type: SCALAR
81 scalar {
82 type: V_BOOL
83 v_bool: false
84 }
85 }
86 }
87 }
88 }
89
90 Got expected error:
91 Mysqlx.Error {
92 severity: ERROR
93 code: 5001
94 msg: "Capability prepare failed for \'tls\'"
95 sql_state: "HY000"
96 }
97
98 # CapSet TLS to null
99 send Mysqlx.Connection.CapabilitiesSet {
100 capabilities {
101 capabilities {
102 name: "tls"
103 value {
104 type: SCALAR
105 scalar {
106 type: V_NULL
107 }
108 }
109 }
110 }
111 }
112
113 Got expected error:
114 Mysqlx.Error {
115 severity: ERROR
116 code: 5001
117 msg: "Capability prepare failed for \'tls\'"
118 sql_state: "HY000"
119 }
120
121 # CapSet TLS to empty octet
122 send Mysqlx.Connection.CapabilitiesSet {
123 capabilities {
124 capabilities {
125 name: "tls"
126 value {
127 type: SCALAR
128 scalar {
129 type: V_OCTETS
130 }
131 }
132 }
133 }
134 }
135
136 Got expected error:
137 Mysqlx.Error {
138 severity: ERROR
139 code: 5001
140 msg: "Capability prepare failed for \'tls\'"
141 sql_state: "HY000"
142 }
143
144 # CapSet TLS to empty uint
145 send Mysqlx.Connection.CapabilitiesSet {
146 capabilities {
147 capabilities {
148 name: "tls"
149 value {
150 type: SCALAR
151 scalar {
152 type: V_UINT
153 }
154 }
155 }
156 }
157 }
158
159 Got expected error:
160 Mysqlx.Error {
161 severity: ERROR
162 code: 5001
163 msg: "Capability prepare failed for \'tls\'"
164 sql_state: "HY000"
165 }
166
167 # CapSet TLS to empty sint
168 send Mysqlx.Connection.CapabilitiesSet {
169 capabilities {
170 capabilities {
171 name: "tls"
172 value {
173 type: SCALAR
174 scalar {
175 type: V_SINT
176 }
177 }
178 }
179 }
180 }
181
182 Got expected error:
183 Mysqlx.Error {
184 severity: ERROR
185 code: 5001
186 msg: "Capability prepare failed for \'tls\'"
187 sql_state: "HY000"
188 }
189
190 ok
191 # II.2
192 send Mysqlx.Connection.CapabilitiesGet {
193 }
194
195 Mysqlx.Connection.Capabilities(capabilities[0]) = Mysqlx.Connection.Capability {
196 name: "tls"
197 value {
198 type: SCALAR
199 scalar {
200 type: V_BOOL
201 v_bool: false
202 }
203 }
204 }
205
206 send Mysqlx.Connection.CapabilitiesSet {
207 capabilities {
208 capabilities {
209 name: "tls"
210 value {
211 type: SCALAR
212 scalar {
213 type: V_BOOL
214 v_bool: true
215 }
216 }
217 }
218 }
219 }
220
221 Mysqlx.Ok {
222 }
223
224 # SSL Enabled
225 send Mysqlx.Connection.CapabilitiesGet {
226 }
227
228 Mysqlx.Connection.Capabilities(capabilities[0]) = Mysqlx.Connection.Capability {
229 name: "tls"
230 value {
231 type: SCALAR
232 scalar {
233 type: V_BOOL
234 v_bool: true
235 }
236 }
237 }
238
239 ok
240
241
242 ## III. Verify handling of expired password with user2_mysqlx account ('mysql_native_password')
243 #
244 # 1. When client doesn't support handling of expired password,
245 # then connection must be rejected
246 # 2. When client support expired password, then he must be able
247 # to alter password
248 # 3. Verify that after the alter password, account can be used
249 # without any additional support for expired password on the client
250 # 4. When client support expired password, then he must be able
251 # to set the password
252 # 5. Verify that after the set password, account can be used
253 # without any additional support for expired password on the client
254 #
255 # III.1
256 Application terminated with expected error: Your password has expired. To log in you must change it using a client that supports expired passwords. (code 1862)
257 ok
258 # III.2
259 NOTICE: Account password expired
260 RUN ALTER USER USER() IDENTIFIED BY 'alter-new-auth'
261
262 0 rows affected
263 Mysqlx.Ok {
264 msg: "bye!"
265 }
266 ok
267 # III.3
268 Verify ["SELECT USER() as column_user" returns in "column_user" column, following value user2_mysqlx@localhost]
269 Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
270 Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1.2]
271 Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of ECDHE-RSA-AES128-GCM-SHA256]
272 Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 5]
273 Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 5]
274 Mysqlx.Ok {
275 msg: "bye!"
276 }
277 ok
278 # III.4
279 ALTER USER user2_mysqlx@localhost PASSWORD EXPIRE;
280 NOTICE: Account password expired
281 RUN SET PASSWORD='set-new-auth'
282
283 0 rows affected
284 Mysqlx.Ok {
285 msg: "bye!"
286 }
287 ok
288 # III.5
289 Verify ["SELECT USER() as column_user" returns in "column_user" column, following value user2_mysqlx@localhost]
290 Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
291 Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1.2]
292 Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of ECDHE-RSA-AES128-GCM-SHA256]
293 Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 7]
294 Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 7]
295 Mysqlx.Ok {
296 msg: "bye!"
297 }
298 ok
299
300
301 ## IV. Verify handling of account that has requires concrete cipher, signed cert
302 ## ('mysql_native_password')
303 #
304 # 1. Verify that user can connect when it has allowed cipher and matching cert
305 # 2. Verify that user can't connect when it has allowed cipher and non-matching cert
306 # 3. Verify that user can't connect when it doesn't use SSL
307 # 4. Verify that user can't connect when it use basic SSL configuration
308 # 5. Verify that user can't connect when it has allowed cipher and its without a cert
309 #
310 # IV.1
311 Verify ["SELECT USER() as column_user" returns in "column_user" column, following value user3_mysqlx@localhost]
312 Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
313 Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1.2]
314 Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of ECDHE-RSA-AES256-GCM-SHA384]
315 Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 8]
316 Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 8]
317 Mysqlx.Ok {
318 msg: "bye!"
319 }
320 ok
321 # IV.2
322 Application terminated with expected error: Access denied for user 'user3_mysqlx'@'localhost' (using password: YES) (code 1045)
323 ok
324 # IV.3
325 Application terminated with expected error: Access denied for user 'user3_mysqlx'@'localhost' (using password: YES) (code 1045)
326 ok
327 # IV.4
328 Application terminated with expected error: Access denied for user 'user3_mysqlx'@'localhost' (using password: YES) (code 1045)
329 ok
330 # IV.5
331 Application terminated with expected error: Access denied for user 'user3_mysqlx'@'localhost' (using password: YES) (code 1045)
332 ok
333
334
335 ## V. Verify handling of locked account ('mysql_native_password')
336 #
337 # 1. Verify that locked account can't connect
338 # 2. Unlock the account and verify that it can connect
339 #
340 # V.1
341 Application terminated with expected error: Access denied for user 'user4_mysqlx'@'localhost'. Account is locked. (code 3118)
342 ok
343 # V.2
344 ALTER USER user4_mysqlx@localhost ACCOUNT UNLOCK;
345 Verify ["SELECT USER() as column_user" returns in "column_user" column, following value user4_mysqlx@localhost]
346 Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
347 Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1.2]
348 Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of ECDHE-RSA-AES128-GCM-SHA256]
349 Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 14]
350 Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 14]
351 Mysqlx.Ok {
352 msg: "bye!"
353 }
354 ok
355
356
357 ## VI. Verify handling of account that was marked as SSL required ('mysql_native_password')
358 #
359 # 1. Verify connection with basic SSL configuration
360 # 2. Verify connection with SSL key & cert
361 # 3. Verify connection with SSL ca
362 #
363 # VI.1
364 Verify ["SELECT USER() as column_user" returns in "column_user" column, following value user5_mysqlx@localhost]
365 Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
366 Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1.2]
367 Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of ECDHE-RSA-AES128-GCM-SHA256]
368 Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 15]
369 Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 15]
370 Mysqlx.Ok {
371 msg: "bye!"
372 }
373 ok
374 # VI.2
375 Verify ["SELECT USER() as column_user" returns in "column_user" column, following value user5_mysqlx@localhost]
376 Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
377 Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1.2]
378 Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of ECDHE-RSA-AES128-GCM-SHA256]
379 Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 16]
380 Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 16]
381 Mysqlx.Ok {
382 msg: "bye!"
383 }
384 ok
385 # VI.3
386 Verify ["SELECT USER() as column_user" returns in "column_user" column, following value user5_mysqlx@localhost]
387 Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
388 Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1.2]
389 Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of ECDHE-RSA-AES128-GCM-SHA256]
390 Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 17]
391 Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 17]
392 Mysqlx.Ok {
393 msg: "bye!"
394 }
395 ok
396
397
398 ## VII. Verify handling of account that was marked as SSL required with signed cert
399 ## ('mysql_native_password')
400 #
401 # 1. Verify connection with signed cert
402 # 2. Verify that connection is rejected when SSL is disabled
403 # 3. Verify that connection is rejected when CA is set
404 # 4. Verify that connection is rejected when using basic SSL configuration
405 #
406 # VII.1
407 Verify ["SELECT USER() as column_user" returns in "column_user" column, following value user6_mysqlx@localhost]
408 Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
409 Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1.2]
410 Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of ECDHE-RSA-AES128-GCM-SHA256]
411 Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 18]
412 Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 18]
413 Mysqlx.Ok {
414 msg: "bye!"
415 }
416 ok
417 # VII.2
418 Application terminated with expected error: Access denied for user 'user6_mysqlx'@'localhost' (using password: YES) (code 1045)
419 ok
420 # VII.3
421 Application terminated with expected error: Access denied for user 'user6_mysqlx'@'localhost' (using password: YES) (code 1045)
422 ok
423 # VII.4
424 Application terminated with expected error: Access denied for user 'user6_mysqlx'@'localhost' (using password: YES) (code 1045)
425 ok
426
427
428 ## VIII. Verify handling of account that was marked that requires concrete SSL cipher
429 ## ('mysql_native_password')
430 #
431 # 1. Verify connection with concrete cipher and signed cert
432 # 2. Verify that connection is rejected with concrete cipher and without a cert
433 # 3. Verify that connection is rejected when SSL is disabled
434 # 4. Verify that connection is rejected with basic SSL configuration
435 # 5. Verify that connection is rejected with wrong cipher and signed cert
436 #
437 # VIII.1
438 Verify ["SELECT USER() as column_user" returns in "column_user" column, following value user7_mysqlx@localhost]
439 Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
440 Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1.2]
441 Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of ECDHE-RSA-AES256-GCM-SHA384]
442 Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 21]
443 Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 21]
444 Mysqlx.Ok {
445 msg: "bye!"
446 }
447 ok
448 # VIII.2
449 Application terminated with expected error: Access denied for user 'user7_mysqlx'@'localhost' (using password: NO) (code 1045)
450 ok
451 # VIII.3
452 Application terminated with expected error: Access denied for user 'user7_mysqlx'@'localhost' (using password: NO) (code 1045)
453 ok
454 # VIII.4
455 Application terminated with expected error: Access denied for user 'user7_mysqlx'@'localhost' (using password: NO) (code 1045)
456 ok
457 # VIII.5
458 Application terminated with expected error: Access denied for user 'user7_mysqlx'@'localhost' (using password: NO) (code 1045)
459 ok
460
461
462 ## IX. Verify X Plugins global and session status variables ('mysql_native_password')
463 #
464 # 1. Verify global SSL setup
465 # 2. Verify session SSL setup
466 #
467 # IX.1
468 SHOW GLOBAL STATUS WHERE `Variable_name` RLIKE '^Mysqlx_ssl_(cipher_list|ctx_verify_(depth|mode)|server_not_(after|before)|verify_(depth|mode))$';
469 Variable_name Value
470 Mysqlx_ssl_cipher_list
471 Mysqlx_ssl_ctx_verify_depth -1
472 Mysqlx_ssl_ctx_verify_mode 5
473 Mysqlx_ssl_server_not_after Dec 1 04:48:40 2029 GMT
474 Mysqlx_ssl_server_not_before Dec 5 04:48:40 2014 GMT
475 Mysqlx_ssl_verify_depth
476 Mysqlx_ssl_verify_mode
477 # IX.2
478 RUN SHOW STATUS WHERE `Variable_name` RLIKE '^Mysqlx_ssl_(ctx_verify_(depth|mode)|server_not_(after|before)|verify_(depth|mode))$'
479 Variable_name Value
480 Mysqlx_ssl_ctx_verify_depth -1
481 Mysqlx_ssl_ctx_verify_mode 5
482 Mysqlx_ssl_server_not_after Dec 1 04:48:40 2029 GMT
483 Mysqlx_ssl_server_not_before Dec 5 04:48:40 2014 GMT
484 Mysqlx_ssl_verify_depth -1
485 Mysqlx_ssl_verify_mode 5
486 0 rows affected
487 Verify [Status variable "Mysqlx_ssl_cipher_list" needs to have a value of ECDHE-RSA-AES128-GCM-SHA256]
488 Verify [Status variable "Mysqlx_ssl_cipher_list" needs to have a value of ECDHE-RSA-AES256-GCM-SHA384]
489 Mysqlx.Ok {
490 msg: "bye!"
491 }
492 ok
493
494
495 ## X. Lets confirm that Mysqlx plugin validates the connection_type vs 'require_secure_transport'
496 ## system variable ('mysql_native_password')
497 #
498 # 1. Connection without SSL (fails on TCP, it works on UNIX socket)
499 # 2. Connection with SSL (always must be successful)
500 #
501 # X.1
502 SET GLOBAL require_secure_transport:=1;
503 Verify ["SELECT USER() as column_user" returns in "column_user" column, following value x_root@localhost]
504 Verify ["SELECT CONNECTION_TYPE in ("TCP/IP", "Socket") as is_non_ssl_con FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "is_non_ssl_con" column, following value 1]
505 Verify [Status variable "Mysqlx_ssl_version" needs to have a value of ]
506 Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of ]
507 Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 25]
508 Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 25]
509 Mysqlx.Ok {
510 msg: "bye!"
511 }
512 ok
513 # X.2
514 Verify ["SELECT USER() as column_user" returns in "column_user" column, following value x_root@localhost]
515 Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
516 Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1.2]
517 Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of ECDHE-RSA-AES128-GCM-SHA256]
518 Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 26]
519 Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 26]
520 Mysqlx.Ok {
521 msg: "bye!"
522 }
523 ok
524 SET GLOBAL require_secure_transport:=0;
525
526
527 ## Cleanup
528 DROP USER user1_mysqlx@localhost;
529 DROP USER user2_mysqlx@localhost;
530 DROP USER user3_mysqlx@localhost;
531 DROP USER user4_mysqlx@localhost;
532 DROP USER user5_mysqlx@localhost;
533 DROP USER user6_mysqlx@localhost;
534 DROP USER user7_mysqlx@localhost;
535 SET GLOBAL mysqlx_connect_timeout = DEFAULT;
536
537
538 ## Preamble
539 SET GLOBAL mysqlx_connect_timeout = 300;
540 call mtr.add_suppression("Maximum number of authentication attempts reached");
541 call mtr.add_suppression("Access denied for user .*");
542 CREATE USER user1_mysqlx@localhost
543 IDENTIFIED WITH 'sha256_password' BY 'auth_string1';
544 CREATE USER user2_mysqlx@localhost
545 IDENTIFIED WITH 'sha256_password' BY 'auth_string2'
546 PASSWORD EXPIRE;
547 CREATE USER user3_mysqlx@localhost
548 IDENTIFIED WITH 'sha256_password' BY 'auth_string3'
549 REQUIRE CIPHER "ECDHE-RSA-AES256-GCM-SHA384" AND
550 SUBJECT "/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client"
551 ISSUER "/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=CA"
552 PASSWORD EXPIRE NEVER;
553 CREATE USER user4_mysqlx@localhost REQUIRE SSL ACCOUNT LOCK;
554 CREATE USER user5_mysqlx@localhost
555 IDENTIFIED WITH 'sha256_password' BY 'auth_string'
556 REQUIRE SSL;
557 CREATE USER user6_mysqlx@localhost
558 IDENTIFIED WITH 'sha256_password' BY 'dwh@#ghd'
559 REQUIRE x509;
560 CREATE USER user7_mysqlx@localhost
561 IDENTIFIED WITH 'sha256_password' BY ''
562 REQUIRE CIPHER "ECDHE-RSA-AES256-GCM-SHA384";
563 GRANT ALL ON *.* TO user1_mysqlx@localhost;
564 GRANT ALL ON *.* TO user2_mysqlx@localhost;
565 GRANT ALL ON *.* TO user3_mysqlx@localhost;
566 GRANT ALL ON *.* TO user4_mysqlx@localhost;
567 GRANT ALL ON *.* TO user5_mysqlx@localhost;
568 GRANT ALL ON *.* TO user6_mysqlx@localhost;
569 GRANT ALL ON *.* TO user7_mysqlx@localhost;
570
571
572 ## I. Verify handling of SSL with normal account ('sha256_password')
573 #
574 Verify ["SELECT USER() as column_user" returns in "column_user" column, following value user1_mysqlx@localhost]
575 Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
576 Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1.2]
577 Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of ECDHE-RSA-AES128-GCM-SHA256]
578 Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 1]
579 Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 1]
580 Mysqlx.Ok {
581 msg: "bye!"
582 }
583 ok
584
585
586 ## II. Verify handling of SSL with super account and XProtocol message
587 ## CapabilitySet (without SSL options on it)
588 #
589 # 1. Try to set "tls" capability with invalid data (no-ssl connection)
590 # 2. Try to set "tls" capability with valid data (ssl connection)
591 #
592 # II.1
593 # setting read/write tls param with possible invalid data types:
594 # V_SINT,V_UINT ,V_NULL,V_OCTETS ,V_DOUBLE,V_FLOAT,V_STRING
595 send Mysqlx.Connection.CapabilitiesGet {
596 }
597
598 Mysqlx.Connection.Capabilities(capabilities[0]) = Mysqlx.Connection.Capability {
599 name: "tls"
600 value {
601 type: SCALAR
602 scalar {
603 type: V_BOOL
604 v_bool: false
605 }
606 }
607 }
608
609 # Checking CapSet TLS to bool 0
610 send Mysqlx.Connection.CapabilitiesSet {
611 capabilities {
612 capabilities {
613 name: "tls"
614 value {
615 type: SCALAR
616 scalar {
617 type: V_BOOL
618 v_bool: false
619 }
620 }
621 }
622 }
623 }
624
625 Got expected error:
626 Mysqlx.Error {
627 severity: ERROR
628 code: 5001
629 msg: "Capability prepare failed for \'tls\'"
630 sql_state: "HY000"
631 }
632
633 # CapSet TLS to null
634 send Mysqlx.Connection.CapabilitiesSet {
635 capabilities {
636 capabilities {
637 name: "tls"
638 value {
639 type: SCALAR
640 scalar {
641 type: V_NULL
642 }
643 }
644 }
645 }
646 }
647
648 Got expected error:
649 Mysqlx.Error {
650 severity: ERROR
651 code: 5001
652 msg: "Capability prepare failed for \'tls\'"
653 sql_state: "HY000"
654 }
655
656 # CapSet TLS to empty octet
657 send Mysqlx.Connection.CapabilitiesSet {
658 capabilities {
659 capabilities {
660 name: "tls"
661 value {
662 type: SCALAR
663 scalar {
664 type: V_OCTETS
665 }
666 }
667 }
668 }
669 }
670
671 Got expected error:
672 Mysqlx.Error {
673 severity: ERROR
674 code: 5001
675 msg: "Capability prepare failed for \'tls\'"
676 sql_state: "HY000"
677 }
678
679 # CapSet TLS to empty uint
680 send Mysqlx.Connection.CapabilitiesSet {
681 capabilities {
682 capabilities {
683 name: "tls"
684 value {
685 type: SCALAR
686 scalar {
687 type: V_UINT
688 }
689 }
690 }
691 }
692 }
693
694 Got expected error:
695 Mysqlx.Error {
696 severity: ERROR
697 code: 5001
698 msg: "Capability prepare failed for \'tls\'"
699 sql_state: "HY000"
700 }
701
702 # CapSet TLS to empty sint
703 send Mysqlx.Connection.CapabilitiesSet {
704 capabilities {
705 capabilities {
706 name: "tls"
707 value {
708 type: SCALAR
709 scalar {
710 type: V_SINT
711 }
712 }
713 }
714 }
715 }
716
717 Got expected error:
718 Mysqlx.Error {
719 severity: ERROR
720 code: 5001
721 msg: "Capability prepare failed for \'tls\'"
722 sql_state: "HY000"
723 }
724
725 ok
726 # II.2
727 send Mysqlx.Connection.CapabilitiesGet {
728 }
729
730 Mysqlx.Connection.Capabilities(capabilities[0]) = Mysqlx.Connection.Capability {
731 name: "tls"
732 value {
733 type: SCALAR
734 scalar {
735 type: V_BOOL
736 v_bool: false
737 }
738 }
739 }
740
741 send Mysqlx.Connection.CapabilitiesSet {
742 capabilities {
743 capabilities {
744 name: "tls"
745 value {
746 type: SCALAR
747 scalar {
748 type: V_BOOL
749 v_bool: true
750 }
751 }
752 }
753 }
754 }
755
756 Mysqlx.Ok {
757 }
758
759 # SSL Enabled
760 send Mysqlx.Connection.CapabilitiesGet {
761 }
762
763 Mysqlx.Connection.Capabilities(capabilities[0]) = Mysqlx.Connection.Capability {
764 name: "tls"
765 value {
766 type: SCALAR
767 scalar {
768 type: V_BOOL
769 v_bool: true
770 }
771 }
772 }
773
774 ok
775
776
777 ## III. Verify handling of expired password with user2_mysqlx account ('sha256_password')
778 #
779 # 1. When client doesn't support handling of expired password,
780 # then connection must be rejected
781 # 2. When client support expired password, then he must be able
782 # to alter password
783 # 3. Verify that after the alter password, account can be used
784 # without any additional support for expired password on the client
785 # 4. When client support expired password, then he must be able
786 # to set the password
787 # 5. Verify that after the set password, account can be used
788 # without any additional support for expired password on the client
789 #
790 # III.1
791 Application terminated with expected error: Your password has expired. To log in you must change it using a client that supports expired passwords. (code 1862)
792 ok
793 # III.2
794 NOTICE: Account password expired
795 RUN ALTER USER USER() IDENTIFIED BY 'alter-new-auth'
796
797 0 rows affected
798 Mysqlx.Ok {
799 msg: "bye!"
800 }
801 ok
802 # III.3
803 Verify ["SELECT USER() as column_user" returns in "column_user" column, following value user2_mysqlx@localhost]
804 Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
805 Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1.2]
806 Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of ECDHE-RSA-AES128-GCM-SHA256]
807 Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 5]
808 Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 5]
809 Mysqlx.Ok {
810 msg: "bye!"
811 }
812 ok
813 # III.4
814 ALTER USER user2_mysqlx@localhost PASSWORD EXPIRE;
815 NOTICE: Account password expired
816 RUN SET PASSWORD='set-new-auth'
817
818 0 rows affected
819 Mysqlx.Ok {
820 msg: "bye!"
821 }
822 ok
823 # III.5
824 Verify ["SELECT USER() as column_user" returns in "column_user" column, following value user2_mysqlx@localhost]
825 Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
826 Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1.2]
827 Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of ECDHE-RSA-AES128-GCM-SHA256]
828 Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 7]
829 Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 7]
830 Mysqlx.Ok {
831 msg: "bye!"
832 }
833 ok
834
835
836 ## IV. Verify handling of account that has requires concrete cipher, signed cert
837 ## ('sha256_password')
838 #
839 # 1. Verify that user can connect when it has allowed cipher and matching cert
840 # 2. Verify that user can't connect when it has allowed cipher and non-matching cert
841 # 3. Verify that user can't connect when it doesn't use SSL
842 # 4. Verify that user can't connect when it use basic SSL configuration
843 # 5. Verify that user can't connect when it has allowed cipher and its without a cert
844 #
845 # IV.1
846 Verify ["SELECT USER() as column_user" returns in "column_user" column, following value user3_mysqlx@localhost]
847 Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
848 Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1.2]
849 Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of ECDHE-RSA-AES256-GCM-SHA384]
850 Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 8]
851 Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 8]
852 Mysqlx.Ok {
853 msg: "bye!"
854 }
855 ok
856 # IV.2
857 Application terminated with expected error: Access denied for user 'user3_mysqlx'@'localhost' (using password: YES) (code 1045)
858 ok
859 # IV.3
860 Application terminated with expected error: Access denied for user 'user3_mysqlx'@'localhost' (using password: YES) (code 1045)
861 ok
862 # IV.4
863 Application terminated with expected error: Access denied for user 'user3_mysqlx'@'localhost' (using password: YES) (code 1045)
864 ok
865 # IV.5
866 Application terminated with expected error: Access denied for user 'user3_mysqlx'@'localhost' (using password: YES) (code 1045)
867 ok
868
869
870 ## V. Verify handling of locked account ('sha256_password')
871 #
872 # 1. Verify that locked account can't connect
873 # 2. Unlock the account and verify that it can connect
874 #
875 # V.1
876 Application terminated with expected error: Access denied for user 'user4_mysqlx'@'localhost'. Account is locked. (code 3118)
877 ok
878 # V.2
879 ALTER USER user4_mysqlx@localhost ACCOUNT UNLOCK;
880 Verify ["SELECT USER() as column_user" returns in "column_user" column, following value user4_mysqlx@localhost]
881 Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
882 Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1.2]
883 Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of ECDHE-RSA-AES128-GCM-SHA256]
884 Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 14]
885 Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 14]
886 Mysqlx.Ok {
887 msg: "bye!"
888 }
889 ok
890
891
892 ## VI. Verify handling of account that was marked as SSL required ('sha256_password')
893 #
894 # 1. Verify connection with basic SSL configuration
895 # 2. Verify connection with SSL key & cert
896 # 3. Verify connection with SSL ca
897 #
898 # VI.1
899 Verify ["SELECT USER() as column_user" returns in "column_user" column, following value user5_mysqlx@localhost]
900 Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
901 Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1.2]
902 Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of ECDHE-RSA-AES128-GCM-SHA256]
903 Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 15]
904 Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 15]
905 Mysqlx.Ok {
906 msg: "bye!"
907 }
908 ok
909 # VI.2
910 Verify ["SELECT USER() as column_user" returns in "column_user" column, following value user5_mysqlx@localhost]
911 Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
912 Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1.2]
913 Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of ECDHE-RSA-AES128-GCM-SHA256]
914 Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 16]
915 Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 16]
916 Mysqlx.Ok {
917 msg: "bye!"
918 }
919 ok
920 # VI.3
921 Verify ["SELECT USER() as column_user" returns in "column_user" column, following value user5_mysqlx@localhost]
922 Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
923 Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1.2]
924 Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of ECDHE-RSA-AES128-GCM-SHA256]
925 Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 17]
926 Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 17]
927 Mysqlx.Ok {
928 msg: "bye!"
929 }
930 ok
931
932
933 ## VII. Verify handling of account that was marked as SSL required with signed cert
934 ## ('sha256_password')
935 #
936 # 1. Verify connection with signed cert
937 # 2. Verify that connection is rejected when SSL is disabled
938 # 3. Verify that connection is rejected when CA is set
939 # 4. Verify that connection is rejected when using basic SSL configuration
940 #
941 # VII.1
942 Verify ["SELECT USER() as column_user" returns in "column_user" column, following value user6_mysqlx@localhost]
943 Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
944 Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1.2]
945 Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of ECDHE-RSA-AES128-GCM-SHA256]
946 Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 18]
947 Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 18]
948 Mysqlx.Ok {
949 msg: "bye!"
950 }
951 ok
952 # VII.2
953 Application terminated with expected error: Access denied for user 'user6_mysqlx'@'localhost' (using password: YES) (code 1045)
954 ok
955 # VII.3
956 Application terminated with expected error: Access denied for user 'user6_mysqlx'@'localhost' (using password: YES) (code 1045)
957 ok
958 # VII.4
959 Application terminated with expected error: Access denied for user 'user6_mysqlx'@'localhost' (using password: YES) (code 1045)
960 ok
961
962
963 ## VIII. Verify handling of account that was marked that requires concrete SSL cipher
964 ## ('sha256_password')
965 #
966 # 1. Verify connection with concrete cipher and signed cert
967 # 2. Verify that connection is rejected with concrete cipher and without a cert
968 # 3. Verify that connection is rejected when SSL is disabled
969 # 4. Verify that connection is rejected with basic SSL configuration
970 # 5. Verify that connection is rejected with wrong cipher and signed cert
971 #
972 # VIII.1
973 Verify ["SELECT USER() as column_user" returns in "column_user" column, following value user7_mysqlx@localhost]
974 Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
975 Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1.2]
976 Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of ECDHE-RSA-AES256-GCM-SHA384]
977 Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 21]
978 Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 21]
979 Mysqlx.Ok {
980 msg: "bye!"
981 }
982 ok
983 # VIII.2
984 Application terminated with expected error: Access denied for user 'user7_mysqlx'@'localhost' (using password: NO) (code 1045)
985 ok
986 # VIII.3
987 Application terminated with expected error: Access denied for user 'user7_mysqlx'@'localhost' (using password: NO) (code 1045)
988 ok
989 # VIII.4
990 Application terminated with expected error: Access denied for user 'user7_mysqlx'@'localhost' (using password: NO) (code 1045)
991 ok
992 # VIII.5
993 Application terminated with expected error: Access denied for user 'user7_mysqlx'@'localhost' (using password: NO) (code 1045)
994 ok
995
996
997 ## IX. Verify X Plugins global and session status variables ('sha256_password')
998 #
999 # 1. Verify global SSL setup
1000 # 2. Verify session SSL setup
1001 #
1002 # IX.1
1003 SHOW GLOBAL STATUS WHERE `Variable_name` RLIKE '^Mysqlx_ssl_(cipher_list|ctx_verify_(depth|mode)|server_not_(after|before)|verify_(depth|mode))$';
1004 Variable_name Value
1005 Mysqlx_ssl_cipher_list
1006 Mysqlx_ssl_ctx_verify_depth -1
1007 Mysqlx_ssl_ctx_verify_mode 5
1008 Mysqlx_ssl_server_not_after Dec 1 04:48:40 2029 GMT
1009 Mysqlx_ssl_server_not_before Dec 5 04:48:40 2014 GMT
1010 Mysqlx_ssl_verify_depth
1011 Mysqlx_ssl_verify_mode
1012 # IX.2
1013 RUN SHOW STATUS WHERE `Variable_name` RLIKE '^Mysqlx_ssl_(ctx_verify_(depth|mode)|server_not_(after|before)|verify_(depth|mode))$'
1014 Variable_name Value
1015 Mysqlx_ssl_ctx_verify_depth -1
1016 Mysqlx_ssl_ctx_verify_mode 5
1017 Mysqlx_ssl_server_not_after Dec 1 04:48:40 2029 GMT
1018 Mysqlx_ssl_server_not_before Dec 5 04:48:40 2014 GMT
1019 Mysqlx_ssl_verify_depth -1
1020 Mysqlx_ssl_verify_mode 5
1021 0 rows affected
1022 Verify [Status variable "Mysqlx_ssl_cipher_list" needs to have a value of ECDHE-RSA-AES128-GCM-SHA256]
1023 Verify [Status variable "Mysqlx_ssl_cipher_list" needs to have a value of ECDHE-RSA-AES256-GCM-SHA384]
1024 Mysqlx.Ok {
1025 msg: "bye!"
1026 }
1027 ok
1028
1029
1030 ## X. Lets confirm that Mysqlx plugin validates the connection_type vs 'require_secure_transport'
1031 ## system variable ('sha256_password')
1032 #
1033 # 1. Connection without SSL (fails on TCP, it works on UNIX socket)
1034 # 2. Connection with SSL (always must be successful)
1035 #
1036 # X.1
1037 SET GLOBAL require_secure_transport:=1;
1038 Verify ["SELECT USER() as column_user" returns in "column_user" column, following value x_root@localhost]
1039 Verify ["SELECT CONNECTION_TYPE in ("TCP/IP", "Socket") as is_non_ssl_con FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "is_non_ssl_con" column, following value 1]
1040 Verify [Status variable "Mysqlx_ssl_version" needs to have a value of ]
1041 Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of ]
1042 Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 25]
1043 Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 25]
1044 Mysqlx.Ok {
1045 msg: "bye!"
1046 }
1047 ok
1048 # X.2
1049 Verify ["SELECT USER() as column_user" returns in "column_user" column, following value x_root@localhost]
1050 Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
1051 Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1.2]
1052 Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of ECDHE-RSA-AES128-GCM-SHA256]
1053 Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 26]
1054 Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 26]
1055 Mysqlx.Ok {
1056 msg: "bye!"
1057 }
1058 ok
1059 SET GLOBAL require_secure_transport:=0;
1060
1061
1062 ## Cleanup
1063 DROP USER user1_mysqlx@localhost;
1064 DROP USER user2_mysqlx@localhost;
1065 DROP USER user3_mysqlx@localhost;
1066 DROP USER user4_mysqlx@localhost;
1067 DROP USER user5_mysqlx@localhost;
1068 DROP USER user6_mysqlx@localhost;
1069 DROP USER user7_mysqlx@localhost;
1070 SET GLOBAL mysqlx_connect_timeout = DEFAULT;
1071
1072
1073 ## Preamble
1074 SET GLOBAL mysqlx_connect_timeout = 300;
1075 call mtr.add_suppression("Maximum number of authentication attempts reached");
1076 call mtr.add_suppression("Access denied for user .*");
1077 CREATE USER user1_mysqlx@localhost
1078 IDENTIFIED WITH 'caching_sha2_password' BY 'auth_string1';
1079 CREATE USER user2_mysqlx@localhost
1080 IDENTIFIED WITH 'caching_sha2_password' BY 'auth_string2'
1081 PASSWORD EXPIRE;
1082 CREATE USER user3_mysqlx@localhost
1083 IDENTIFIED WITH 'caching_sha2_password' BY 'auth_string3'
1084 REQUIRE CIPHER "ECDHE-RSA-AES256-GCM-SHA384" AND
1085 SUBJECT "/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client"
1086 ISSUER "/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=CA"
1087 PASSWORD EXPIRE NEVER;
1088 CREATE USER user4_mysqlx@localhost REQUIRE SSL ACCOUNT LOCK;
1089 CREATE USER user5_mysqlx@localhost
1090 IDENTIFIED WITH 'caching_sha2_password' BY 'auth_string'
1091 REQUIRE SSL;
1092 CREATE USER user6_mysqlx@localhost
1093 IDENTIFIED WITH 'caching_sha2_password' BY 'dwh@#ghd'
1094 REQUIRE x509;
1095 CREATE USER user7_mysqlx@localhost
1096 IDENTIFIED WITH 'caching_sha2_password' BY ''
1097 REQUIRE CIPHER "ECDHE-RSA-AES256-GCM-SHA384";
1098 GRANT ALL ON *.* TO user1_mysqlx@localhost;
1099 GRANT ALL ON *.* TO user2_mysqlx@localhost;
1100 GRANT ALL ON *.* TO user3_mysqlx@localhost;
1101 GRANT ALL ON *.* TO user4_mysqlx@localhost;
1102 GRANT ALL ON *.* TO user5_mysqlx@localhost;
1103 GRANT ALL ON *.* TO user6_mysqlx@localhost;
1104 GRANT ALL ON *.* TO user7_mysqlx@localhost;
1105
1106
1107 ## I. Verify handling of SSL with normal account ('caching_sha2_password')
1108 #
1109 Verify ["SELECT USER() as column_user" returns in "column_user" column, following value user1_mysqlx@localhost]
1110 Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
1111 Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1.2]
1112 Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of ECDHE-RSA-AES128-GCM-SHA256]
1113 Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 1]
1114 Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 1]
1115 Mysqlx.Ok {
1116 msg: "bye!"
1117 }
1118 ok
1119
1120
1121 ## II. Verify handling of SSL with super account and XProtocol message
1122 ## CapabilitySet (without SSL options on it)
1123 #
1124 # 1. Try to set "tls" capability with invalid data (no-ssl connection)
1125 # 2. Try to set "tls" capability with valid data (ssl connection)
1126 #
1127 # II.1
1128 # setting read/write tls param with possible invalid data types:
1129 # V_SINT,V_UINT ,V_NULL,V_OCTETS ,V_DOUBLE,V_FLOAT,V_STRING
1130 send Mysqlx.Connection.CapabilitiesGet {
1131 }
1132
1133 Mysqlx.Connection.Capabilities(capabilities[0]) = Mysqlx.Connection.Capability {
1134 name: "tls"
1135 value {
1136 type: SCALAR
1137 scalar {
1138 type: V_BOOL
1139 v_bool: false
1140 }
1141 }
1142 }
1143
1144 # Checking CapSet TLS to bool 0
1145 send Mysqlx.Connection.CapabilitiesSet {
1146 capabilities {
1147 capabilities {
1148 name: "tls"
1149 value {
1150 type: SCALAR
1151 scalar {
1152 type: V_BOOL
1153 v_bool: false
1154 }
1155 }
1156 }
1157 }
1158 }
1159
1160 Got expected error:
1161 Mysqlx.Error {
1162 severity: ERROR
1163 code: 5001
1164 msg: "Capability prepare failed for \'tls\'"
1165 sql_state: "HY000"
1166 }
1167
1168 # CapSet TLS to null
1169 send Mysqlx.Connection.CapabilitiesSet {
1170 capabilities {
1171 capabilities {
1172 name: "tls"
1173 value {
1174 type: SCALAR
1175 scalar {
1176 type: V_NULL
1177 }
1178 }
1179 }
1180 }
1181 }
1182
1183 Got expected error:
1184 Mysqlx.Error {
1185 severity: ERROR
1186 code: 5001
1187 msg: "Capability prepare failed for \'tls\'"
1188 sql_state: "HY000"
1189 }
1190
1191 # CapSet TLS to empty octet
1192 send Mysqlx.Connection.CapabilitiesSet {
1193 capabilities {
1194 capabilities {
1195 name: "tls"
1196 value {
1197 type: SCALAR
1198 scalar {
1199 type: V_OCTETS
1200 }
1201 }
1202 }
1203 }
1204 }
1205
1206 Got expected error:
1207 Mysqlx.Error {
1208 severity: ERROR
1209 code: 5001
1210 msg: "Capability prepare failed for \'tls\'"
1211 sql_state: "HY000"
1212 }
1213
1214 # CapSet TLS to empty uint
1215 send Mysqlx.Connection.CapabilitiesSet {
1216 capabilities {
1217 capabilities {
1218 name: "tls"
1219 value {
1220 type: SCALAR
1221 scalar {
1222 type: V_UINT
1223 }
1224 }
1225 }
1226 }
1227 }
1228
1229 Got expected error:
1230 Mysqlx.Error {
1231 severity: ERROR
1232 code: 5001
1233 msg: "Capability prepare failed for \'tls\'"
1234 sql_state: "HY000"
1235 }
1236
1237 # CapSet TLS to empty sint
1238 send Mysqlx.Connection.CapabilitiesSet {
1239 capabilities {
1240 capabilities {
1241 name: "tls"
1242 value {
1243 type: SCALAR
1244 scalar {
1245 type: V_SINT
1246 }
1247 }
1248 }
1249 }
1250 }
1251
1252 Got expected error:
1253 Mysqlx.Error {
1254 severity: ERROR
1255 code: 5001
1256 msg: "Capability prepare failed for \'tls\'"
1257 sql_state: "HY000"
1258 }
1259
1260 ok
1261 # II.2
1262 send Mysqlx.Connection.CapabilitiesGet {
1263 }
1264
1265 Mysqlx.Connection.Capabilities(capabilities[0]) = Mysqlx.Connection.Capability {
1266 name: "tls"
1267 value {
1268 type: SCALAR
1269 scalar {
1270 type: V_BOOL
1271 v_bool: false
1272 }
1273 }
1274 }
1275
1276 send Mysqlx.Connection.CapabilitiesSet {
1277 capabilities {
1278 capabilities {
1279 name: "tls"
1280 value {
1281 type: SCALAR
1282 scalar {
1283 type: V_BOOL
1284 v_bool: true
1285 }
1286 }
1287 }
1288 }
1289 }
1290
1291 Mysqlx.Ok {
1292 }
1293
1294 # SSL Enabled
1295 send Mysqlx.Connection.CapabilitiesGet {
1296 }
1297
1298 Mysqlx.Connection.Capabilities(capabilities[0]) = Mysqlx.Connection.Capability {
1299 name: "tls"
1300 value {
1301 type: SCALAR
1302 scalar {
1303 type: V_BOOL
1304 v_bool: true
1305 }
1306 }
1307 }
1308
1309 ok
1310
1311
1312 ## III. Verify handling of expired password with user2_mysqlx account ('caching_sha2_password')
1313 #
1314 # 1. When client doesn't support handling of expired password,
1315 # then connection must be rejected
1316 # 2. When client support expired password, then he must be able
1317 # to alter password
1318 # 3. Verify that after the alter password, account can be used
1319 # without any additional support for expired password on the client
1320 # 4. When client support expired password, then he must be able
1321 # to set the password
1322 # 5. Verify that after the set password, account can be used
1323 # without any additional support for expired password on the client
1324 #
1325 # III.1
1326 Application terminated with expected error: Your password has expired. To log in you must change it using a client that supports expired passwords. (code 1862)
1327 ok
1328 # III.2
1329 NOTICE: Account password expired
1330 RUN ALTER USER USER() IDENTIFIED BY 'alter-new-auth'
1331
1332 0 rows affected
1333 Mysqlx.Ok {
1334 msg: "bye!"
1335 }
1336 ok
1337 # III.3
1338 Verify ["SELECT USER() as column_user" returns in "column_user" column, following value user2_mysqlx@localhost]
1339 Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
1340 Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1.2]
1341 Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of ECDHE-RSA-AES128-GCM-SHA256]
1342 Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 5]
1343 Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 5]
1344 Mysqlx.Ok {
1345 msg: "bye!"
1346 }
1347 ok
1348 # III.4
1349 ALTER USER user2_mysqlx@localhost PASSWORD EXPIRE;
1350 NOTICE: Account password expired
1351 RUN SET PASSWORD='set-new-auth'
1352
1353 0 rows affected
1354 Mysqlx.Ok {
1355 msg: "bye!"
1356 }
1357 ok
1358 # III.5
1359 Verify ["SELECT USER() as column_user" returns in "column_user" column, following value user2_mysqlx@localhost]
1360 Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
1361 Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1.2]
1362 Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of ECDHE-RSA-AES128-GCM-SHA256]
1363 Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 7]
1364 Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 7]
1365 Mysqlx.Ok {
1366 msg: "bye!"
1367 }
1368 ok
1369
1370
1371 ## IV. Verify handling of account that has requires concrete cipher, signed cert
1372 ## ('caching_sha2_password')
1373 #
1374 # 1. Verify that user can connect when it has allowed cipher and matching cert
1375 # 2. Verify that user can't connect when it has allowed cipher and non-matching cert
1376 # 3. Verify that user can't connect when it doesn't use SSL
1377 # 4. Verify that user can't connect when it use basic SSL configuration
1378 # 5. Verify that user can't connect when it has allowed cipher and its without a cert
1379 #
1380 # IV.1
1381 Verify ["SELECT USER() as column_user" returns in "column_user" column, following value user3_mysqlx@localhost]
1382 Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
1383 Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1.2]
1384 Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of ECDHE-RSA-AES256-GCM-SHA384]
1385 Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 8]
1386 Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 8]
1387 Mysqlx.Ok {
1388 msg: "bye!"
1389 }
1390 ok
1391 # IV.2
1392 Application terminated with expected error: Access denied for user 'user3_mysqlx'@'localhost' (using password: YES) (code 1045)
1393 ok
1394 # IV.3
1395 Application terminated with expected error: Access denied for user 'user3_mysqlx'@'localhost' (using password: YES) (code 1045)
1396 ok
1397 # IV.4
1398 Application terminated with expected error: Access denied for user 'user3_mysqlx'@'localhost' (using password: YES) (code 1045)
1399 ok
1400 # IV.5
1401 Application terminated with expected error: Access denied for user 'user3_mysqlx'@'localhost' (using password: YES) (code 1045)
1402 ok
1403
1404
1405 ## V. Verify handling of locked account ('caching_sha2_password')
1406 #
1407 # 1. Verify that locked account can't connect
1408 # 2. Unlock the account and verify that it can connect
1409 #
1410 # V.1
1411 Application terminated with expected error: Access denied for user 'user4_mysqlx'@'localhost'. Account is locked. (code 3118)
1412 ok
1413 # V.2
1414 ALTER USER user4_mysqlx@localhost ACCOUNT UNLOCK;
1415 Verify ["SELECT USER() as column_user" returns in "column_user" column, following value user4_mysqlx@localhost]
1416 Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
1417 Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1.2]
1418 Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of ECDHE-RSA-AES128-GCM-SHA256]
1419 Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 14]
1420 Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 14]
1421 Mysqlx.Ok {
1422 msg: "bye!"
1423 }
1424 ok
1425
1426
1427 ## VI. Verify handling of account that was marked as SSL required ('caching_sha2_password')
1428 #
1429 # 1. Verify connection with basic SSL configuration
1430 # 2. Verify connection with SSL key & cert
1431 # 3. Verify connection with SSL ca
1432 #
1433 # VI.1
1434 Verify ["SELECT USER() as column_user" returns in "column_user" column, following value user5_mysqlx@localhost]
1435 Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
1436 Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1.2]
1437 Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of ECDHE-RSA-AES128-GCM-SHA256]
1438 Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 15]
1439 Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 15]
1440 Mysqlx.Ok {
1441 msg: "bye!"
1442 }
1443 ok
1444 # VI.2
1445 Verify ["SELECT USER() as column_user" returns in "column_user" column, following value user5_mysqlx@localhost]
1446 Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
1447 Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1.2]
1448 Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of ECDHE-RSA-AES128-GCM-SHA256]
1449 Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 16]
1450 Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 16]
1451 Mysqlx.Ok {
1452 msg: "bye!"
1453 }
1454 ok
1455 # VI.3
1456 Verify ["SELECT USER() as column_user" returns in "column_user" column, following value user5_mysqlx@localhost]
1457 Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
1458 Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1.2]
1459 Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of ECDHE-RSA-AES128-GCM-SHA256]
1460 Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 17]
1461 Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 17]
1462 Mysqlx.Ok {
1463 msg: "bye!"
1464 }
1465 ok
1466
1467
1468 ## VII. Verify handling of account that was marked as SSL required with signed cert
1469 ## ('caching_sha2_password')
1470 #
1471 # 1. Verify connection with signed cert
1472 # 2. Verify that connection is rejected when SSL is disabled
1473 # 3. Verify that connection is rejected when CA is set
1474 # 4. Verify that connection is rejected when using basic SSL configuration
1475 #
1476 # VII.1
1477 Verify ["SELECT USER() as column_user" returns in "column_user" column, following value user6_mysqlx@localhost]
1478 Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
1479 Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1.2]
1480 Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of ECDHE-RSA-AES128-GCM-SHA256]
1481 Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 18]
1482 Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 18]
1483 Mysqlx.Ok {
1484 msg: "bye!"
1485 }
1486 ok
1487 # VII.2
1488 Application terminated with expected error: Access denied for user 'user6_mysqlx'@'localhost' (using password: YES) (code 1045)
1489 ok
1490 # VII.3
1491 Application terminated with expected error: Access denied for user 'user6_mysqlx'@'localhost' (using password: YES) (code 1045)
1492 ok
1493 # VII.4
1494 Application terminated with expected error: Access denied for user 'user6_mysqlx'@'localhost' (using password: YES) (code 1045)
1495 ok
1496
1497
1498 ## VIII. Verify handling of account that was marked that requires concrete SSL cipher
1499 ## ('caching_sha2_password')
1500 #
1501 # 1. Verify connection with concrete cipher and signed cert
1502 # 2. Verify that connection is rejected with concrete cipher and without a cert
1503 # 3. Verify that connection is rejected when SSL is disabled
1504 # 4. Verify that connection is rejected with basic SSL configuration
1505 # 5. Verify that connection is rejected with wrong cipher and signed cert
1506 #
1507 # VIII.1
1508 Verify ["SELECT USER() as column_user" returns in "column_user" column, following value user7_mysqlx@localhost]
1509 Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
1510 Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1.2]
1511 Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of ECDHE-RSA-AES256-GCM-SHA384]
1512 Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 21]
1513 Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 21]
1514 Mysqlx.Ok {
1515 msg: "bye!"
1516 }
1517 ok
1518 # VIII.2
1519 Application terminated with expected error: Access denied for user 'user7_mysqlx'@'localhost' (using password: NO) (code 1045)
1520 ok
1521 # VIII.3
1522 Application terminated with expected error: Access denied for user 'user7_mysqlx'@'localhost' (using password: NO) (code 1045)
1523 ok
1524 # VIII.4
1525 Application terminated with expected error: Access denied for user 'user7_mysqlx'@'localhost' (using password: NO) (code 1045)
1526 ok
1527 # VIII.5
1528 Application terminated with expected error: Access denied for user 'user7_mysqlx'@'localhost' (using password: NO) (code 1045)
1529 ok
1530
1531
1532 ## IX. Verify X Plugins global and session status variables ('caching_sha2_password')
1533 #
1534 # 1. Verify global SSL setup
1535 # 2. Verify session SSL setup
1536 #
1537 # IX.1
1538 SHOW GLOBAL STATUS WHERE `Variable_name` RLIKE '^Mysqlx_ssl_(cipher_list|ctx_verify_(depth|mode)|server_not_(after|before)|verify_(depth|mode))$';
1539 Variable_name Value
1540 Mysqlx_ssl_cipher_list
1541 Mysqlx_ssl_ctx_verify_depth -1
1542 Mysqlx_ssl_ctx_verify_mode 5
1543 Mysqlx_ssl_server_not_after Dec 1 04:48:40 2029 GMT
1544 Mysqlx_ssl_server_not_before Dec 5 04:48:40 2014 GMT
1545 Mysqlx_ssl_verify_depth
1546 Mysqlx_ssl_verify_mode
1547 # IX.2
1548 RUN SHOW STATUS WHERE `Variable_name` RLIKE '^Mysqlx_ssl_(ctx_verify_(depth|mode)|server_not_(after|before)|verify_(depth|mode))$'
1549 Variable_name Value
1550 Mysqlx_ssl_ctx_verify_depth -1
1551 Mysqlx_ssl_ctx_verify_mode 5
1552 Mysqlx_ssl_server_not_after Dec 1 04:48:40 2029 GMT
1553 Mysqlx_ssl_server_not_before Dec 5 04:48:40 2014 GMT
1554 Mysqlx_ssl_verify_depth -1
1555 Mysqlx_ssl_verify_mode 5
1556 0 rows affected
1557 Verify [Status variable "Mysqlx_ssl_cipher_list" needs to have a value of ECDHE-RSA-AES128-GCM-SHA256]
1558 Verify [Status variable "Mysqlx_ssl_cipher_list" needs to have a value of ECDHE-RSA-AES256-GCM-SHA384]
1559 Mysqlx.Ok {
1560 msg: "bye!"
1561 }
1562 ok
1563
1564
1565 ## X. Lets confirm that Mysqlx plugin validates the connection_type vs 'require_secure_transport'
1566 ## system variable ('caching_sha2_password')
1567 #
1568 # 1. Connection without SSL (fails on TCP, it works on UNIX socket)
1569 # 2. Connection with SSL (always must be successful)
1570 #
1571 # X.1
1572 SET GLOBAL require_secure_transport:=1;
1573 Verify ["SELECT USER() as column_user" returns in "column_user" column, following value x_root@localhost]
1574 Verify ["SELECT CONNECTION_TYPE in ("TCP/IP", "Socket") as is_non_ssl_con FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "is_non_ssl_con" column, following value 1]
1575 Verify [Status variable "Mysqlx_ssl_version" needs to have a value of ]
1576 Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of ]
1577 Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 25]
1578 Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 25]
1579 Mysqlx.Ok {
1580 msg: "bye!"
1581 }
1582 ok
1583 # X.2
1584 Verify ["SELECT USER() as column_user" returns in "column_user" column, following value x_root@localhost]
1585 Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
1586 Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1.2]
1587 Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of ECDHE-RSA-AES128-GCM-SHA256]
1588 Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 26]
1589 Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 26]
1590 Mysqlx.Ok {
1591 msg: "bye!"
1592 }
1593 ok
1594 SET GLOBAL require_secure_transport:=0;
1595
1596
1597 ## Cleanup
1598 DROP USER user1_mysqlx@localhost;
1599 DROP USER user2_mysqlx@localhost;
1600 DROP USER user3_mysqlx@localhost;
1601 DROP USER user4_mysqlx@localhost;
1602 DROP USER user5_mysqlx@localhost;
1603 DROP USER user6_mysqlx@localhost;
1604 DROP USER user7_mysqlx@localhost;
1605 SET GLOBAL mysqlx_connect_timeout = DEFAULT;