Debian Codegraph @

mysql-test/suite/x/r/connection_openssl.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: Authentication failed, check username and password or try a secure connection (code 2510)
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: Authentication failed, check username and password or try a secure connection (code 2510)
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 Application terminated with expected error: Connections using insecure transport are prohibited while --require_secure_transport=ON. (code 3159)
504 ok
505 # X.2
506 Verify ["SELECT USER() as column_user" returns in "column_user" column, following value x_root@localhost]
507 Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
508 Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1.2]
509 Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of ECDHE-RSA-AES128-GCM-SHA256]
510 Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 26]
511 Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 26]
512 Mysqlx.Ok {
513 msg: "bye!"
514 }
515 ok
516 SET GLOBAL require_secure_transport:=0;
517
518
519 ## Cleanup
520 DROP USER user1_mysqlx@localhost;
521 DROP USER user2_mysqlx@localhost;
522 DROP USER user3_mysqlx@localhost;
523 DROP USER user4_mysqlx@localhost;
524 DROP USER user5_mysqlx@localhost;
525 DROP USER user6_mysqlx@localhost;
526 DROP USER user7_mysqlx@localhost;
527 SET GLOBAL mysqlx_connect_timeout = DEFAULT;
528
529
530 ## Preamble
531 SET GLOBAL mysqlx_connect_timeout = 300;
532 call mtr.add_suppression("Maximum number of authentication attempts reached");
533 call mtr.add_suppression("Access denied for user .*");
534 CREATE USER user1_mysqlx@localhost
535 IDENTIFIED WITH 'sha256_password' BY 'auth_string1';
536 CREATE USER user2_mysqlx@localhost
537 IDENTIFIED WITH 'sha256_password' BY 'auth_string2'
538 PASSWORD EXPIRE;
539 CREATE USER user3_mysqlx@localhost
540 IDENTIFIED WITH 'sha256_password' BY 'auth_string3'
541 REQUIRE CIPHER "ECDHE-RSA-AES256-GCM-SHA384" AND
542 SUBJECT "/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client"
543 ISSUER "/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=CA"
544 PASSWORD EXPIRE NEVER;
545 CREATE USER user4_mysqlx@localhost REQUIRE SSL ACCOUNT LOCK;
546 CREATE USER user5_mysqlx@localhost
547 IDENTIFIED WITH 'sha256_password' BY 'auth_string'
548 REQUIRE SSL;
549 CREATE USER user6_mysqlx@localhost
550 IDENTIFIED WITH 'sha256_password' BY 'dwh@#ghd'
551 REQUIRE x509;
552 CREATE USER user7_mysqlx@localhost
553 IDENTIFIED WITH 'sha256_password' BY ''
554 REQUIRE CIPHER "ECDHE-RSA-AES256-GCM-SHA384";
555 GRANT ALL ON *.* TO user1_mysqlx@localhost;
556 GRANT ALL ON *.* TO user2_mysqlx@localhost;
557 GRANT ALL ON *.* TO user3_mysqlx@localhost;
558 GRANT ALL ON *.* TO user4_mysqlx@localhost;
559 GRANT ALL ON *.* TO user5_mysqlx@localhost;
560 GRANT ALL ON *.* TO user6_mysqlx@localhost;
561 GRANT ALL ON *.* TO user7_mysqlx@localhost;
562
563
564 ## I. Verify handling of SSL with normal account ('sha256_password')
565 #
566 Verify ["SELECT USER() as column_user" returns in "column_user" column, following value user1_mysqlx@localhost]
567 Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
568 Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1.2]
569 Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of ECDHE-RSA-AES128-GCM-SHA256]
570 Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 1]
571 Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 1]
572 Mysqlx.Ok {
573 msg: "bye!"
574 }
575 ok
576
577
578 ## II. Verify handling of SSL with super account and XProtocol message
579 ## CapabilitySet (without SSL options on it)
580 #
581 # 1. Try to set "tls" capability with invalid data (no-ssl connection)
582 # 2. Try to set "tls" capability with valid data (ssl connection)
583 #
584 # II.1
585 # setting read/write tls param with possible invalid data types:
586 # V_SINT,V_UINT ,V_NULL,V_OCTETS ,V_DOUBLE,V_FLOAT,V_STRING
587 send Mysqlx.Connection.CapabilitiesGet {
588 }
589
590 Mysqlx.Connection.Capabilities(capabilities[0]) = Mysqlx.Connection.Capability {
591 name: "tls"
592 value {
593 type: SCALAR
594 scalar {
595 type: V_BOOL
596 v_bool: false
597 }
598 }
599 }
600
601 # Checking CapSet TLS to bool 0
602 send Mysqlx.Connection.CapabilitiesSet {
603 capabilities {
604 capabilities {
605 name: "tls"
606 value {
607 type: SCALAR
608 scalar {
609 type: V_BOOL
610 v_bool: false
611 }
612 }
613 }
614 }
615 }
616
617 Got expected error:
618 Mysqlx.Error {
619 severity: ERROR
620 code: 5001
621 msg: "Capability prepare failed for \'tls\'"
622 sql_state: "HY000"
623 }
624
625 # CapSet TLS to null
626 send Mysqlx.Connection.CapabilitiesSet {
627 capabilities {
628 capabilities {
629 name: "tls"
630 value {
631 type: SCALAR
632 scalar {
633 type: V_NULL
634 }
635 }
636 }
637 }
638 }
639
640 Got expected error:
641 Mysqlx.Error {
642 severity: ERROR
643 code: 5001
644 msg: "Capability prepare failed for \'tls\'"
645 sql_state: "HY000"
646 }
647
648 # CapSet TLS to empty octet
649 send Mysqlx.Connection.CapabilitiesSet {
650 capabilities {
651 capabilities {
652 name: "tls"
653 value {
654 type: SCALAR
655 scalar {
656 type: V_OCTETS
657 }
658 }
659 }
660 }
661 }
662
663 Got expected error:
664 Mysqlx.Error {
665 severity: ERROR
666 code: 5001
667 msg: "Capability prepare failed for \'tls\'"
668 sql_state: "HY000"
669 }
670
671 # CapSet TLS to empty uint
672 send Mysqlx.Connection.CapabilitiesSet {
673 capabilities {
674 capabilities {
675 name: "tls"
676 value {
677 type: SCALAR
678 scalar {
679 type: V_UINT
680 }
681 }
682 }
683 }
684 }
685
686 Got expected error:
687 Mysqlx.Error {
688 severity: ERROR
689 code: 5001
690 msg: "Capability prepare failed for \'tls\'"
691 sql_state: "HY000"
692 }
693
694 # CapSet TLS to empty sint
695 send Mysqlx.Connection.CapabilitiesSet {
696 capabilities {
697 capabilities {
698 name: "tls"
699 value {
700 type: SCALAR
701 scalar {
702 type: V_SINT
703 }
704 }
705 }
706 }
707 }
708
709 Got expected error:
710 Mysqlx.Error {
711 severity: ERROR
712 code: 5001
713 msg: "Capability prepare failed for \'tls\'"
714 sql_state: "HY000"
715 }
716
717 ok
718 # II.2
719 send Mysqlx.Connection.CapabilitiesGet {
720 }
721
722 Mysqlx.Connection.Capabilities(capabilities[0]) = Mysqlx.Connection.Capability {
723 name: "tls"
724 value {
725 type: SCALAR
726 scalar {
727 type: V_BOOL
728 v_bool: false
729 }
730 }
731 }
732
733 send Mysqlx.Connection.CapabilitiesSet {
734 capabilities {
735 capabilities {
736 name: "tls"
737 value {
738 type: SCALAR
739 scalar {
740 type: V_BOOL
741 v_bool: true
742 }
743 }
744 }
745 }
746 }
747
748 Mysqlx.Ok {
749 }
750
751 # SSL Enabled
752 send Mysqlx.Connection.CapabilitiesGet {
753 }
754
755 Mysqlx.Connection.Capabilities(capabilities[0]) = Mysqlx.Connection.Capability {
756 name: "tls"
757 value {
758 type: SCALAR
759 scalar {
760 type: V_BOOL
761 v_bool: true
762 }
763 }
764 }
765
766 ok
767
768
769 ## III. Verify handling of expired password with user2_mysqlx account ('sha256_password')
770 #
771 # 1. When client doesn't support handling of expired password,
772 # then connection must be rejected
773 # 2. When client support expired password, then he must be able
774 # to alter password
775 # 3. Verify that after the alter password, account can be used
776 # without any additional support for expired password on the client
777 # 4. When client support expired password, then he must be able
778 # to set the password
779 # 5. Verify that after the set password, account can be used
780 # without any additional support for expired password on the client
781 #
782 # III.1
783 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)
784 ok
785 # III.2
786 NOTICE: Account password expired
787 RUN ALTER USER USER() IDENTIFIED BY 'alter-new-auth'
788
789 0 rows affected
790 Mysqlx.Ok {
791 msg: "bye!"
792 }
793 ok
794 # III.3
795 Verify ["SELECT USER() as column_user" returns in "column_user" column, following value user2_mysqlx@localhost]
796 Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
797 Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1.2]
798 Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of ECDHE-RSA-AES128-GCM-SHA256]
799 Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 5]
800 Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 5]
801 Mysqlx.Ok {
802 msg: "bye!"
803 }
804 ok
805 # III.4
806 ALTER USER user2_mysqlx@localhost PASSWORD EXPIRE;
807 NOTICE: Account password expired
808 RUN SET PASSWORD='set-new-auth'
809
810 0 rows affected
811 Mysqlx.Ok {
812 msg: "bye!"
813 }
814 ok
815 # III.5
816 Verify ["SELECT USER() as column_user" returns in "column_user" column, following value user2_mysqlx@localhost]
817 Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
818 Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1.2]
819 Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of ECDHE-RSA-AES128-GCM-SHA256]
820 Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 7]
821 Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 7]
822 Mysqlx.Ok {
823 msg: "bye!"
824 }
825 ok
826
827
828 ## IV. Verify handling of account that has requires concrete cipher, signed cert
829 ## ('sha256_password')
830 #
831 # 1. Verify that user can connect when it has allowed cipher and matching cert
832 # 2. Verify that user can't connect when it has allowed cipher and non-matching cert
833 # 3. Verify that user can't connect when it doesn't use SSL
834 # 4. Verify that user can't connect when it use basic SSL configuration
835 # 5. Verify that user can't connect when it has allowed cipher and its without a cert
836 #
837 # IV.1
838 Verify ["SELECT USER() as column_user" returns in "column_user" column, following value user3_mysqlx@localhost]
839 Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
840 Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1.2]
841 Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of ECDHE-RSA-AES256-GCM-SHA384]
842 Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 8]
843 Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 8]
844 Mysqlx.Ok {
845 msg: "bye!"
846 }
847 ok
848 # IV.2
849 Application terminated with expected error: Access denied for user 'user3_mysqlx'@'localhost' (using password: YES) (code 1045)
850 ok
851 # IV.3
852 Application terminated with expected error: Access denied for user 'user3_mysqlx'@'localhost' (using password: YES) (code 1045)
853 ok
854 # IV.4
855 Application terminated with expected error: Access denied for user 'user3_mysqlx'@'localhost' (using password: YES) (code 1045)
856 ok
857 # IV.5
858 Application terminated with expected error: Access denied for user 'user3_mysqlx'@'localhost' (using password: YES) (code 1045)
859 ok
860
861
862 ## V. Verify handling of locked account ('sha256_password')
863 #
864 # 1. Verify that locked account can't connect
865 # 2. Unlock the account and verify that it can connect
866 #
867 # V.1
868 Application terminated with expected error: Access denied for user 'user4_mysqlx'@'localhost'. Account is locked. (code 3118)
869 ok
870 # V.2
871 ALTER USER user4_mysqlx@localhost ACCOUNT UNLOCK;
872 Verify ["SELECT USER() as column_user" returns in "column_user" column, following value user4_mysqlx@localhost]
873 Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
874 Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1.2]
875 Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of ECDHE-RSA-AES128-GCM-SHA256]
876 Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 14]
877 Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 14]
878 Mysqlx.Ok {
879 msg: "bye!"
880 }
881 ok
882
883
884 ## VI. Verify handling of account that was marked as SSL required ('sha256_password')
885 #
886 # 1. Verify connection with basic SSL configuration
887 # 2. Verify connection with SSL key & cert
888 # 3. Verify connection with SSL ca
889 #
890 # VI.1
891 Verify ["SELECT USER() as column_user" returns in "column_user" column, following value user5_mysqlx@localhost]
892 Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
893 Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1.2]
894 Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of ECDHE-RSA-AES128-GCM-SHA256]
895 Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 15]
896 Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 15]
897 Mysqlx.Ok {
898 msg: "bye!"
899 }
900 ok
901 # VI.2
902 Verify ["SELECT USER() as column_user" returns in "column_user" column, following value user5_mysqlx@localhost]
903 Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
904 Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1.2]
905 Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of ECDHE-RSA-AES128-GCM-SHA256]
906 Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 16]
907 Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 16]
908 Mysqlx.Ok {
909 msg: "bye!"
910 }
911 ok
912 # VI.3
913 Verify ["SELECT USER() as column_user" returns in "column_user" column, following value user5_mysqlx@localhost]
914 Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
915 Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1.2]
916 Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of ECDHE-RSA-AES128-GCM-SHA256]
917 Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 17]
918 Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 17]
919 Mysqlx.Ok {
920 msg: "bye!"
921 }
922 ok
923
924
925 ## VII. Verify handling of account that was marked as SSL required with signed cert
926 ## ('sha256_password')
927 #
928 # 1. Verify connection with signed cert
929 # 2. Verify that connection is rejected when SSL is disabled
930 # 3. Verify that connection is rejected when CA is set
931 # 4. Verify that connection is rejected when using basic SSL configuration
932 #
933 # VII.1
934 Verify ["SELECT USER() as column_user" returns in "column_user" column, following value user6_mysqlx@localhost]
935 Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
936 Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1.2]
937 Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of ECDHE-RSA-AES128-GCM-SHA256]
938 Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 18]
939 Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 18]
940 Mysqlx.Ok {
941 msg: "bye!"
942 }
943 ok
944 # VII.2
945 Application terminated with expected error: Authentication failed, check username and password or try a secure connection (code 2510)
946 ok
947 # VII.3
948 Application terminated with expected error: Access denied for user 'user6_mysqlx'@'localhost' (using password: YES) (code 1045)
949 ok
950 # VII.4
951 Application terminated with expected error: Access denied for user 'user6_mysqlx'@'localhost' (using password: YES) (code 1045)
952 ok
953
954
955 ## VIII. Verify handling of account that was marked that requires concrete SSL cipher
956 ## ('sha256_password')
957 #
958 # 1. Verify connection with concrete cipher and signed cert
959 # 2. Verify that connection is rejected with concrete cipher and without a cert
960 # 3. Verify that connection is rejected when SSL is disabled
961 # 4. Verify that connection is rejected with basic SSL configuration
962 # 5. Verify that connection is rejected with wrong cipher and signed cert
963 #
964 # VIII.1
965 Verify ["SELECT USER() as column_user" returns in "column_user" column, following value user7_mysqlx@localhost]
966 Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
967 Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1.2]
968 Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of ECDHE-RSA-AES256-GCM-SHA384]
969 Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 21]
970 Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 21]
971 Mysqlx.Ok {
972 msg: "bye!"
973 }
974 ok
975 # VIII.2
976 Application terminated with expected error: Access denied for user 'user7_mysqlx'@'localhost' (using password: NO) (code 1045)
977 ok
978 # VIII.3
979 Application terminated with expected error: Authentication failed, check username and password or try a secure connection (code 2510)
980 ok
981 # VIII.4
982 Application terminated with expected error: Access denied for user 'user7_mysqlx'@'localhost' (using password: NO) (code 1045)
983 ok
984 # VIII.5
985 Application terminated with expected error: Access denied for user 'user7_mysqlx'@'localhost' (using password: NO) (code 1045)
986 ok
987
988
989 ## IX. Verify X Plugins global and session status variables ('sha256_password')
990 #
991 # 1. Verify global SSL setup
992 # 2. Verify session SSL setup
993 #
994 # IX.1
995 SHOW GLOBAL STATUS WHERE `Variable_name` RLIKE '^Mysqlx_ssl_(cipher_list|ctx_verify_(depth|mode)|server_not_(after|before)|verify_(depth|mode))$';
996 Variable_name Value
997 Mysqlx_ssl_cipher_list
998 Mysqlx_ssl_ctx_verify_depth -1
999 Mysqlx_ssl_ctx_verify_mode 5
1000 Mysqlx_ssl_server_not_after Dec 1 04:48:40 2029 GMT
1001 Mysqlx_ssl_server_not_before Dec 5 04:48:40 2014 GMT
1002 Mysqlx_ssl_verify_depth
1003 Mysqlx_ssl_verify_mode
1004 # IX.2
1005 RUN SHOW STATUS WHERE `Variable_name` RLIKE '^Mysqlx_ssl_(ctx_verify_(depth|mode)|server_not_(after|before)|verify_(depth|mode))$'
1006 Variable_name Value
1007 Mysqlx_ssl_ctx_verify_depth -1
1008 Mysqlx_ssl_ctx_verify_mode 5
1009 Mysqlx_ssl_server_not_after Dec 1 04:48:40 2029 GMT
1010 Mysqlx_ssl_server_not_before Dec 5 04:48:40 2014 GMT
1011 Mysqlx_ssl_verify_depth -1
1012 Mysqlx_ssl_verify_mode 5
1013 0 rows affected
1014 Verify [Status variable "Mysqlx_ssl_cipher_list" needs to have a value of ECDHE-RSA-AES128-GCM-SHA256]
1015 Verify [Status variable "Mysqlx_ssl_cipher_list" needs to have a value of ECDHE-RSA-AES256-GCM-SHA384]
1016 Mysqlx.Ok {
1017 msg: "bye!"
1018 }
1019 ok
1020
1021
1022 ## X. Lets confirm that Mysqlx plugin validates the connection_type vs 'require_secure_transport'
1023 ## system variable ('sha256_password')
1024 #
1025 # 1. Connection without SSL (fails on TCP, it works on UNIX socket)
1026 # 2. Connection with SSL (always must be successful)
1027 #
1028 # X.1
1029 SET GLOBAL require_secure_transport:=1;
1030 Application terminated with expected error: Connections using insecure transport are prohibited while --require_secure_transport=ON. (code 3159)
1031 ok
1032 # X.2
1033 Verify ["SELECT USER() as column_user" returns in "column_user" column, following value x_root@localhost]
1034 Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
1035 Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1.2]
1036 Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of ECDHE-RSA-AES128-GCM-SHA256]
1037 Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 26]
1038 Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 26]
1039 Mysqlx.Ok {
1040 msg: "bye!"
1041 }
1042 ok
1043 SET GLOBAL require_secure_transport:=0;
1044
1045
1046 ## Cleanup
1047 DROP USER user1_mysqlx@localhost;
1048 DROP USER user2_mysqlx@localhost;
1049 DROP USER user3_mysqlx@localhost;
1050 DROP USER user4_mysqlx@localhost;
1051 DROP USER user5_mysqlx@localhost;
1052 DROP USER user6_mysqlx@localhost;
1053 DROP USER user7_mysqlx@localhost;
1054 SET GLOBAL mysqlx_connect_timeout = DEFAULT;
1055
1056
1057 ## Preamble
1058 SET GLOBAL mysqlx_connect_timeout = 300;
1059 call mtr.add_suppression("Maximum number of authentication attempts reached");
1060 call mtr.add_suppression("Access denied for user .*");
1061 CREATE USER user1_mysqlx@localhost
1062 IDENTIFIED WITH 'caching_sha2_password' BY 'auth_string1';
1063 CREATE USER user2_mysqlx@localhost
1064 IDENTIFIED WITH 'caching_sha2_password' BY 'auth_string2'
1065 PASSWORD EXPIRE;
1066 CREATE USER user3_mysqlx@localhost
1067 IDENTIFIED WITH 'caching_sha2_password' BY 'auth_string3'
1068 REQUIRE CIPHER "ECDHE-RSA-AES256-GCM-SHA384" AND
1069 SUBJECT "/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client"
1070 ISSUER "/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=CA"
1071 PASSWORD EXPIRE NEVER;
1072 CREATE USER user4_mysqlx@localhost REQUIRE SSL ACCOUNT LOCK;
1073 CREATE USER user5_mysqlx@localhost
1074 IDENTIFIED WITH 'caching_sha2_password' BY 'auth_string'
1075 REQUIRE SSL;
1076 CREATE USER user6_mysqlx@localhost
1077 IDENTIFIED WITH 'caching_sha2_password' BY 'dwh@#ghd'
1078 REQUIRE x509;
1079 CREATE USER user7_mysqlx@localhost
1080 IDENTIFIED WITH 'caching_sha2_password' BY ''
1081 REQUIRE CIPHER "ECDHE-RSA-AES256-GCM-SHA384";
1082 GRANT ALL ON *.* TO user1_mysqlx@localhost;
1083 GRANT ALL ON *.* TO user2_mysqlx@localhost;
1084 GRANT ALL ON *.* TO user3_mysqlx@localhost;
1085 GRANT ALL ON *.* TO user4_mysqlx@localhost;
1086 GRANT ALL ON *.* TO user5_mysqlx@localhost;
1087 GRANT ALL ON *.* TO user6_mysqlx@localhost;
1088 GRANT ALL ON *.* TO user7_mysqlx@localhost;
1089
1090
1091 ## I. Verify handling of SSL with normal account ('caching_sha2_password')
1092 #
1093 Verify ["SELECT USER() as column_user" returns in "column_user" column, following value user1_mysqlx@localhost]
1094 Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
1095 Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1.2]
1096 Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of ECDHE-RSA-AES128-GCM-SHA256]
1097 Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 1]
1098 Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 1]
1099 Mysqlx.Ok {
1100 msg: "bye!"
1101 }
1102 ok
1103
1104
1105 ## II. Verify handling of SSL with super account and XProtocol message
1106 ## CapabilitySet (without SSL options on it)
1107 #
1108 # 1. Try to set "tls" capability with invalid data (no-ssl connection)
1109 # 2. Try to set "tls" capability with valid data (ssl connection)
1110 #
1111 # II.1
1112 # setting read/write tls param with possible invalid data types:
1113 # V_SINT,V_UINT ,V_NULL,V_OCTETS ,V_DOUBLE,V_FLOAT,V_STRING
1114 send Mysqlx.Connection.CapabilitiesGet {
1115 }
1116
1117 Mysqlx.Connection.Capabilities(capabilities[0]) = Mysqlx.Connection.Capability {
1118 name: "tls"
1119 value {
1120 type: SCALAR
1121 scalar {
1122 type: V_BOOL
1123 v_bool: false
1124 }
1125 }
1126 }
1127
1128 # Checking CapSet TLS to bool 0
1129 send Mysqlx.Connection.CapabilitiesSet {
1130 capabilities {
1131 capabilities {
1132 name: "tls"
1133 value {
1134 type: SCALAR
1135 scalar {
1136 type: V_BOOL
1137 v_bool: false
1138 }
1139 }
1140 }
1141 }
1142 }
1143
1144 Got expected error:
1145 Mysqlx.Error {
1146 severity: ERROR
1147 code: 5001
1148 msg: "Capability prepare failed for \'tls\'"
1149 sql_state: "HY000"
1150 }
1151
1152 # CapSet TLS to null
1153 send Mysqlx.Connection.CapabilitiesSet {
1154 capabilities {
1155 capabilities {
1156 name: "tls"
1157 value {
1158 type: SCALAR
1159 scalar {
1160 type: V_NULL
1161 }
1162 }
1163 }
1164 }
1165 }
1166
1167 Got expected error:
1168 Mysqlx.Error {
1169 severity: ERROR
1170 code: 5001
1171 msg: "Capability prepare failed for \'tls\'"
1172 sql_state: "HY000"
1173 }
1174
1175 # CapSet TLS to empty octet
1176 send Mysqlx.Connection.CapabilitiesSet {
1177 capabilities {
1178 capabilities {
1179 name: "tls"
1180 value {
1181 type: SCALAR
1182 scalar {
1183 type: V_OCTETS
1184 }
1185 }
1186 }
1187 }
1188 }
1189
1190 Got expected error:
1191 Mysqlx.Error {
1192 severity: ERROR
1193 code: 5001
1194 msg: "Capability prepare failed for \'tls\'"
1195 sql_state: "HY000"
1196 }
1197
1198 # CapSet TLS to empty uint
1199 send Mysqlx.Connection.CapabilitiesSet {
1200 capabilities {
1201 capabilities {
1202 name: "tls"
1203 value {
1204 type: SCALAR
1205 scalar {
1206 type: V_UINT
1207 }
1208 }
1209 }
1210 }
1211 }
1212
1213 Got expected error:
1214 Mysqlx.Error {
1215 severity: ERROR
1216 code: 5001
1217 msg: "Capability prepare failed for \'tls\'"
1218 sql_state: "HY000"
1219 }
1220
1221 # CapSet TLS to empty sint
1222 send Mysqlx.Connection.CapabilitiesSet {
1223 capabilities {
1224 capabilities {
1225 name: "tls"
1226 value {
1227 type: SCALAR
1228 scalar {
1229 type: V_SINT
1230 }
1231 }
1232 }
1233 }
1234 }
1235
1236 Got expected error:
1237 Mysqlx.Error {
1238 severity: ERROR
1239 code: 5001
1240 msg: "Capability prepare failed for \'tls\'"
1241 sql_state: "HY000"
1242 }
1243
1244 ok
1245 # II.2
1246 send Mysqlx.Connection.CapabilitiesGet {
1247 }
1248
1249 Mysqlx.Connection.Capabilities(capabilities[0]) = Mysqlx.Connection.Capability {
1250 name: "tls"
1251 value {
1252 type: SCALAR
1253 scalar {
1254 type: V_BOOL
1255 v_bool: false
1256 }
1257 }
1258 }
1259
1260 send Mysqlx.Connection.CapabilitiesSet {
1261 capabilities {
1262 capabilities {
1263 name: "tls"
1264 value {
1265 type: SCALAR
1266 scalar {
1267 type: V_BOOL
1268 v_bool: true
1269 }
1270 }
1271 }
1272 }
1273 }
1274
1275 Mysqlx.Ok {
1276 }
1277
1278 # SSL Enabled
1279 send Mysqlx.Connection.CapabilitiesGet {
1280 }
1281
1282 Mysqlx.Connection.Capabilities(capabilities[0]) = Mysqlx.Connection.Capability {
1283 name: "tls"
1284 value {
1285 type: SCALAR
1286 scalar {
1287 type: V_BOOL
1288 v_bool: true
1289 }
1290 }
1291 }
1292
1293 ok
1294
1295
1296 ## III. Verify handling of expired password with user2_mysqlx account ('caching_sha2_password')
1297 #
1298 # 1. When client doesn't support handling of expired password,
1299 # then connection must be rejected
1300 # 2. When client support expired password, then he must be able
1301 # to alter password
1302 # 3. Verify that after the alter password, account can be used
1303 # without any additional support for expired password on the client
1304 # 4. When client support expired password, then he must be able
1305 # to set the password
1306 # 5. Verify that after the set password, account can be used
1307 # without any additional support for expired password on the client
1308 #
1309 # III.1
1310 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)
1311 ok
1312 # III.2
1313 NOTICE: Account password expired
1314 RUN ALTER USER USER() IDENTIFIED BY 'alter-new-auth'
1315
1316 0 rows affected
1317 Mysqlx.Ok {
1318 msg: "bye!"
1319 }
1320 ok
1321 # III.3
1322 Verify ["SELECT USER() as column_user" returns in "column_user" column, following value user2_mysqlx@localhost]
1323 Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
1324 Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1.2]
1325 Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of ECDHE-RSA-AES128-GCM-SHA256]
1326 Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 5]
1327 Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 5]
1328 Mysqlx.Ok {
1329 msg: "bye!"
1330 }
1331 ok
1332 # III.4
1333 ALTER USER user2_mysqlx@localhost PASSWORD EXPIRE;
1334 NOTICE: Account password expired
1335 RUN SET PASSWORD='set-new-auth'
1336
1337 0 rows affected
1338 Mysqlx.Ok {
1339 msg: "bye!"
1340 }
1341 ok
1342 # III.5
1343 Verify ["SELECT USER() as column_user" returns in "column_user" column, following value user2_mysqlx@localhost]
1344 Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
1345 Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1.2]
1346 Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of ECDHE-RSA-AES128-GCM-SHA256]
1347 Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 7]
1348 Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 7]
1349 Mysqlx.Ok {
1350 msg: "bye!"
1351 }
1352 ok
1353
1354
1355 ## IV. Verify handling of account that has requires concrete cipher, signed cert
1356 ## ('caching_sha2_password')
1357 #
1358 # 1. Verify that user can connect when it has allowed cipher and matching cert
1359 # 2. Verify that user can't connect when it has allowed cipher and non-matching cert
1360 # 3. Verify that user can't connect when it doesn't use SSL
1361 # 4. Verify that user can't connect when it use basic SSL configuration
1362 # 5. Verify that user can't connect when it has allowed cipher and its without a cert
1363 #
1364 # IV.1
1365 Verify ["SELECT USER() as column_user" returns in "column_user" column, following value user3_mysqlx@localhost]
1366 Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
1367 Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1.2]
1368 Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of ECDHE-RSA-AES256-GCM-SHA384]
1369 Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 8]
1370 Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 8]
1371 Mysqlx.Ok {
1372 msg: "bye!"
1373 }
1374 ok
1375 # IV.2
1376 Application terminated with expected error: Access denied for user 'user3_mysqlx'@'localhost' (using password: YES) (code 1045)
1377 ok
1378 # IV.3
1379 Application terminated with expected error: Access denied for user 'user3_mysqlx'@'localhost' (using password: YES) (code 1045)
1380 ok
1381 # IV.4
1382 Application terminated with expected error: Access denied for user 'user3_mysqlx'@'localhost' (using password: YES) (code 1045)
1383 ok
1384 # IV.5
1385 Application terminated with expected error: Access denied for user 'user3_mysqlx'@'localhost' (using password: YES) (code 1045)
1386 ok
1387
1388
1389 ## V. Verify handling of locked account ('caching_sha2_password')
1390 #
1391 # 1. Verify that locked account can't connect
1392 # 2. Unlock the account and verify that it can connect
1393 #
1394 # V.1
1395 Application terminated with expected error: Access denied for user 'user4_mysqlx'@'localhost'. Account is locked. (code 3118)
1396 ok
1397 # V.2
1398 ALTER USER user4_mysqlx@localhost ACCOUNT UNLOCK;
1399 Verify ["SELECT USER() as column_user" returns in "column_user" column, following value user4_mysqlx@localhost]
1400 Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
1401 Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1.2]
1402 Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of ECDHE-RSA-AES128-GCM-SHA256]
1403 Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 14]
1404 Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 14]
1405 Mysqlx.Ok {
1406 msg: "bye!"
1407 }
1408 ok
1409
1410
1411 ## VI. Verify handling of account that was marked as SSL required ('caching_sha2_password')
1412 #
1413 # 1. Verify connection with basic SSL configuration
1414 # 2. Verify connection with SSL key & cert
1415 # 3. Verify connection with SSL ca
1416 #
1417 # VI.1
1418 Verify ["SELECT USER() as column_user" returns in "column_user" column, following value user5_mysqlx@localhost]
1419 Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
1420 Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1.2]
1421 Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of ECDHE-RSA-AES128-GCM-SHA256]
1422 Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 15]
1423 Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 15]
1424 Mysqlx.Ok {
1425 msg: "bye!"
1426 }
1427 ok
1428 # VI.2
1429 Verify ["SELECT USER() as column_user" returns in "column_user" column, following value user5_mysqlx@localhost]
1430 Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
1431 Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1.2]
1432 Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of ECDHE-RSA-AES128-GCM-SHA256]
1433 Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 16]
1434 Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 16]
1435 Mysqlx.Ok {
1436 msg: "bye!"
1437 }
1438 ok
1439 # VI.3
1440 Verify ["SELECT USER() as column_user" returns in "column_user" column, following value user5_mysqlx@localhost]
1441 Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
1442 Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1.2]
1443 Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of ECDHE-RSA-AES128-GCM-SHA256]
1444 Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 17]
1445 Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 17]
1446 Mysqlx.Ok {
1447 msg: "bye!"
1448 }
1449 ok
1450
1451
1452 ## VII. Verify handling of account that was marked as SSL required with signed cert
1453 ## ('caching_sha2_password')
1454 #
1455 # 1. Verify connection with signed cert
1456 # 2. Verify that connection is rejected when SSL is disabled
1457 # 3. Verify that connection is rejected when CA is set
1458 # 4. Verify that connection is rejected when using basic SSL configuration
1459 #
1460 # VII.1
1461 Verify ["SELECT USER() as column_user" returns in "column_user" column, following value user6_mysqlx@localhost]
1462 Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
1463 Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1.2]
1464 Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of ECDHE-RSA-AES128-GCM-SHA256]
1465 Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 18]
1466 Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 18]
1467 Mysqlx.Ok {
1468 msg: "bye!"
1469 }
1470 ok
1471 # VII.2
1472 Application terminated with expected error: Authentication failed, check username and password or try a secure connection (code 2510)
1473 ok
1474 # VII.3
1475 Application terminated with expected error: Access denied for user 'user6_mysqlx'@'localhost' (using password: YES) (code 1045)
1476 ok
1477 # VII.4
1478 Application terminated with expected error: Access denied for user 'user6_mysqlx'@'localhost' (using password: YES) (code 1045)
1479 ok
1480
1481
1482 ## VIII. Verify handling of account that was marked that requires concrete SSL cipher
1483 ## ('caching_sha2_password')
1484 #
1485 # 1. Verify connection with concrete cipher and signed cert
1486 # 2. Verify that connection is rejected with concrete cipher and without a cert
1487 # 3. Verify that connection is rejected when SSL is disabled
1488 # 4. Verify that connection is rejected with basic SSL configuration
1489 # 5. Verify that connection is rejected with wrong cipher and signed cert
1490 #
1491 # VIII.1
1492 Verify ["SELECT USER() as column_user" returns in "column_user" column, following value user7_mysqlx@localhost]
1493 Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
1494 Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1.2]
1495 Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of ECDHE-RSA-AES256-GCM-SHA384]
1496 Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 21]
1497 Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 21]
1498 Mysqlx.Ok {
1499 msg: "bye!"
1500 }
1501 ok
1502 # VIII.2
1503 Application terminated with expected error: Access denied for user 'user7_mysqlx'@'localhost' (using password: NO) (code 1045)
1504 ok
1505 # VIII.3
1506 Application terminated with expected error: Authentication failed, check username and password or try a secure connection (code 2510)
1507 ok
1508 # VIII.4
1509 Application terminated with expected error: Access denied for user 'user7_mysqlx'@'localhost' (using password: NO) (code 1045)
1510 ok
1511 # VIII.5
1512 Application terminated with expected error: Access denied for user 'user7_mysqlx'@'localhost' (using password: NO) (code 1045)
1513 ok
1514
1515
1516 ## IX. Verify X Plugins global and session status variables ('caching_sha2_password')
1517 #
1518 # 1. Verify global SSL setup
1519 # 2. Verify session SSL setup
1520 #
1521 # IX.1
1522 SHOW GLOBAL STATUS WHERE `Variable_name` RLIKE '^Mysqlx_ssl_(cipher_list|ctx_verify_(depth|mode)|server_not_(after|before)|verify_(depth|mode))$';
1523 Variable_name Value
1524 Mysqlx_ssl_cipher_list
1525 Mysqlx_ssl_ctx_verify_depth -1
1526 Mysqlx_ssl_ctx_verify_mode 5
1527 Mysqlx_ssl_server_not_after Dec 1 04:48:40 2029 GMT
1528 Mysqlx_ssl_server_not_before Dec 5 04:48:40 2014 GMT
1529 Mysqlx_ssl_verify_depth
1530 Mysqlx_ssl_verify_mode
1531 # IX.2
1532 RUN SHOW STATUS WHERE `Variable_name` RLIKE '^Mysqlx_ssl_(ctx_verify_(depth|mode)|server_not_(after|before)|verify_(depth|mode))$'
1533 Variable_name Value
1534 Mysqlx_ssl_ctx_verify_depth -1
1535 Mysqlx_ssl_ctx_verify_mode 5
1536 Mysqlx_ssl_server_not_after Dec 1 04:48:40 2029 GMT
1537 Mysqlx_ssl_server_not_before Dec 5 04:48:40 2014 GMT
1538 Mysqlx_ssl_verify_depth -1
1539 Mysqlx_ssl_verify_mode 5
1540 0 rows affected
1541 Verify [Status variable "Mysqlx_ssl_cipher_list" needs to have a value of ECDHE-RSA-AES128-GCM-SHA256]
1542 Verify [Status variable "Mysqlx_ssl_cipher_list" needs to have a value of ECDHE-RSA-AES256-GCM-SHA384]
1543 Mysqlx.Ok {
1544 msg: "bye!"
1545 }
1546 ok
1547
1548
1549 ## X. Lets confirm that Mysqlx plugin validates the connection_type vs 'require_secure_transport'
1550 ## system variable ('caching_sha2_password')
1551 #
1552 # 1. Connection without SSL (fails on TCP, it works on UNIX socket)
1553 # 2. Connection with SSL (always must be successful)
1554 #
1555 # X.1
1556 SET GLOBAL require_secure_transport:=1;
1557 Application terminated with expected error: Connections using insecure transport are prohibited while --require_secure_transport=ON. (code 3159)
1558 ok
1559 # X.2
1560 Verify ["SELECT USER() as column_user" returns in "column_user" column, following value x_root@localhost]
1561 Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
1562 Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1.2]
1563 Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of ECDHE-RSA-AES128-GCM-SHA256]
1564 Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 26]
1565 Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 26]
1566 Mysqlx.Ok {
1567 msg: "bye!"
1568 }
1569 ok
1570 SET GLOBAL require_secure_transport:=0;
1571
1572
1573 ## Cleanup
1574 DROP USER user1_mysqlx@localhost;
1575 DROP USER user2_mysqlx@localhost;
1576 DROP USER user3_mysqlx@localhost;
1577 DROP USER user4_mysqlx@localhost;
1578 DROP USER user5_mysqlx@localhost;
1579 DROP USER user6_mysqlx@localhost;
1580 DROP USER user7_mysqlx@localhost;
1581 SET GLOBAL mysqlx_connect_timeout = DEFAULT;