1603 lines
54 KiB
Plaintext
1603 lines
54 KiB
Plaintext
|
|
|
|
## Preamble
|
|
SET GLOBAL mysqlx_connect_timeout = 300;
|
|
call mtr.add_suppression("Maximum number of authentication attempts reached");
|
|
call mtr.add_suppression("Access denied for user .*");
|
|
CREATE USER user1_mysqlx@localhost
|
|
IDENTIFIED WITH 'mysql_native_password' BY 'auth_string1';
|
|
CREATE USER user2_mysqlx@localhost
|
|
IDENTIFIED WITH 'mysql_native_password' BY 'auth_string2'
|
|
PASSWORD EXPIRE;
|
|
CREATE USER user3_mysqlx@localhost
|
|
IDENTIFIED WITH 'mysql_native_password' BY 'auth_string3'
|
|
REQUIRE CIPHER "AES256-SHA" AND
|
|
SUBJECT "/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client"
|
|
ISSUER "/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=CA"
|
|
PASSWORD EXPIRE NEVER;
|
|
CREATE USER user4_mysqlx@localhost REQUIRE SSL ACCOUNT LOCK;
|
|
CREATE USER user5_mysqlx@localhost
|
|
IDENTIFIED WITH 'mysql_native_password' BY 'auth_string'
|
|
REQUIRE SSL;
|
|
CREATE USER user6_mysqlx@localhost
|
|
IDENTIFIED WITH 'mysql_native_password' BY 'dwh@#ghd'
|
|
REQUIRE x509;
|
|
CREATE USER user7_mysqlx@localhost
|
|
IDENTIFIED WITH 'mysql_native_password' BY ''
|
|
REQUIRE CIPHER "AES256-SHA";
|
|
GRANT ALL ON *.* TO user1_mysqlx@localhost;
|
|
GRANT ALL ON *.* TO user2_mysqlx@localhost;
|
|
GRANT ALL ON *.* TO user3_mysqlx@localhost;
|
|
GRANT ALL ON *.* TO user4_mysqlx@localhost;
|
|
GRANT ALL ON *.* TO user5_mysqlx@localhost;
|
|
GRANT ALL ON *.* TO user6_mysqlx@localhost;
|
|
GRANT ALL ON *.* TO user7_mysqlx@localhost;
|
|
|
|
|
|
## I. Verify handling of SSL with normal account ('mysql_native_password')
|
|
#
|
|
Verify ["SELECT USER() as column_user" returns in "column_user" column, following value user1_mysqlx@localhost]
|
|
Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
|
|
Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1]
|
|
Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of DHE-RSA-AES256-SHA]
|
|
Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 1]
|
|
Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 1]
|
|
Mysqlx.Ok {
|
|
msg: "bye!"
|
|
}
|
|
ok
|
|
|
|
|
|
## II. Verify handling of SSL with super account and XProtocol message
|
|
## CapabilitySet (without SSL options on it)
|
|
#
|
|
# 1. Try to set "tls" capability with invalid data (no-ssl connection)
|
|
# 2. Try to set "tls" capability with valid data (ssl connection)
|
|
#
|
|
# II.1
|
|
# setting read/write tls param with possible invalid data types:
|
|
# V_SINT,V_UINT ,V_NULL,V_OCTETS ,V_DOUBLE,V_FLOAT,V_STRING
|
|
send Mysqlx.Connection.CapabilitiesGet {
|
|
}
|
|
|
|
Mysqlx.Connection.Capabilities(capabilities[0]) = Mysqlx.Connection.Capability {
|
|
name: "tls"
|
|
value {
|
|
type: SCALAR
|
|
scalar {
|
|
type: V_BOOL
|
|
v_bool: false
|
|
}
|
|
}
|
|
}
|
|
|
|
# Checking CapSet TLS to bool 0
|
|
send Mysqlx.Connection.CapabilitiesSet {
|
|
capabilities {
|
|
capabilities {
|
|
name: "tls"
|
|
value {
|
|
type: SCALAR
|
|
scalar {
|
|
type: V_BOOL
|
|
v_bool: false
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
Got expected error:
|
|
Mysqlx.Error {
|
|
severity: ERROR
|
|
code: 5001
|
|
msg: "Capability prepare failed for \'tls\'"
|
|
sql_state: "HY000"
|
|
}
|
|
|
|
# CapSet TLS to null
|
|
send Mysqlx.Connection.CapabilitiesSet {
|
|
capabilities {
|
|
capabilities {
|
|
name: "tls"
|
|
value {
|
|
type: SCALAR
|
|
scalar {
|
|
type: V_NULL
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
Got expected error:
|
|
Mysqlx.Error {
|
|
severity: ERROR
|
|
code: 5001
|
|
msg: "Capability prepare failed for \'tls\'"
|
|
sql_state: "HY000"
|
|
}
|
|
|
|
# CapSet TLS to empty octet
|
|
send Mysqlx.Connection.CapabilitiesSet {
|
|
capabilities {
|
|
capabilities {
|
|
name: "tls"
|
|
value {
|
|
type: SCALAR
|
|
scalar {
|
|
type: V_OCTETS
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
Got expected error:
|
|
Mysqlx.Error {
|
|
severity: ERROR
|
|
code: 5001
|
|
msg: "Capability prepare failed for \'tls\'"
|
|
sql_state: "HY000"
|
|
}
|
|
|
|
# CapSet TLS to empty uint
|
|
send Mysqlx.Connection.CapabilitiesSet {
|
|
capabilities {
|
|
capabilities {
|
|
name: "tls"
|
|
value {
|
|
type: SCALAR
|
|
scalar {
|
|
type: V_UINT
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
Got expected error:
|
|
Mysqlx.Error {
|
|
severity: ERROR
|
|
code: 5001
|
|
msg: "Capability prepare failed for \'tls\'"
|
|
sql_state: "HY000"
|
|
}
|
|
|
|
# CapSet TLS to empty sint
|
|
send Mysqlx.Connection.CapabilitiesSet {
|
|
capabilities {
|
|
capabilities {
|
|
name: "tls"
|
|
value {
|
|
type: SCALAR
|
|
scalar {
|
|
type: V_SINT
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
Got expected error:
|
|
Mysqlx.Error {
|
|
severity: ERROR
|
|
code: 5001
|
|
msg: "Capability prepare failed for \'tls\'"
|
|
sql_state: "HY000"
|
|
}
|
|
|
|
ok
|
|
# II.2
|
|
send Mysqlx.Connection.CapabilitiesGet {
|
|
}
|
|
|
|
Mysqlx.Connection.Capabilities(capabilities[0]) = Mysqlx.Connection.Capability {
|
|
name: "tls"
|
|
value {
|
|
type: SCALAR
|
|
scalar {
|
|
type: V_BOOL
|
|
v_bool: false
|
|
}
|
|
}
|
|
}
|
|
|
|
send Mysqlx.Connection.CapabilitiesSet {
|
|
capabilities {
|
|
capabilities {
|
|
name: "tls"
|
|
value {
|
|
type: SCALAR
|
|
scalar {
|
|
type: V_BOOL
|
|
v_bool: true
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
Mysqlx.Ok {
|
|
}
|
|
|
|
# SSL Enabled
|
|
send Mysqlx.Connection.CapabilitiesGet {
|
|
}
|
|
|
|
Mysqlx.Connection.Capabilities(capabilities[0]) = Mysqlx.Connection.Capability {
|
|
name: "tls"
|
|
value {
|
|
type: SCALAR
|
|
scalar {
|
|
type: V_BOOL
|
|
v_bool: true
|
|
}
|
|
}
|
|
}
|
|
|
|
ok
|
|
|
|
|
|
## III. Verify handling of expired password with user2_mysqlx account ('mysql_native_password')
|
|
#
|
|
# 1. When client doesn't support handling of expired password,
|
|
# then connection must be rejected
|
|
# 2. When client support expired password, then he must be able
|
|
# to alter password
|
|
# 3. Verify that after the alter password, account can be used
|
|
# without any additional support for expired password on the client
|
|
# 4. When client support expired password, then he must be able
|
|
# to set the password
|
|
# 5. Verify that after the set password, account can be used
|
|
# without any additional support for expired password on the client
|
|
#
|
|
# III.1
|
|
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)
|
|
ok
|
|
# III.2
|
|
NOTICE: Account password expired
|
|
RUN ALTER USER USER() IDENTIFIED BY 'alter-new-auth'
|
|
|
|
0 rows affected
|
|
Mysqlx.Ok {
|
|
msg: "bye!"
|
|
}
|
|
ok
|
|
# III.3
|
|
Verify ["SELECT USER() as column_user" returns in "column_user" column, following value user2_mysqlx@localhost]
|
|
Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
|
|
Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1]
|
|
Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of DHE-RSA-AES256-SHA]
|
|
Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 5]
|
|
Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 5]
|
|
Mysqlx.Ok {
|
|
msg: "bye!"
|
|
}
|
|
ok
|
|
# III.4
|
|
ALTER USER user2_mysqlx@localhost PASSWORD EXPIRE;
|
|
NOTICE: Account password expired
|
|
RUN SET PASSWORD='set-new-auth'
|
|
|
|
0 rows affected
|
|
Mysqlx.Ok {
|
|
msg: "bye!"
|
|
}
|
|
ok
|
|
# III.5
|
|
Verify ["SELECT USER() as column_user" returns in "column_user" column, following value user2_mysqlx@localhost]
|
|
Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
|
|
Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1]
|
|
Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of DHE-RSA-AES256-SHA]
|
|
Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 7]
|
|
Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 7]
|
|
Mysqlx.Ok {
|
|
msg: "bye!"
|
|
}
|
|
ok
|
|
|
|
|
|
## IV. Verify handling of account that has requires concrete cipher, signed cert
|
|
## ('mysql_native_password')
|
|
#
|
|
# 1. Verify that user can connect when it has allowed cipher and matching cert
|
|
# 2. Verify that user can't connect when it has allowed cipher and non-matching cert
|
|
# 3. Verify that user can't connect when it doesn't use SSL
|
|
# 4. Verify that user can't connect when it use basic SSL configuration
|
|
# 5. Verify that user can't connect when it has allowed cipher and its without a cert
|
|
#
|
|
# IV.1
|
|
Verify ["SELECT USER() as column_user" returns in "column_user" column, following value user3_mysqlx@localhost]
|
|
Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
|
|
Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1]
|
|
Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of AES256-SHA]
|
|
Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 8]
|
|
Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 8]
|
|
Mysqlx.Ok {
|
|
msg: "bye!"
|
|
}
|
|
ok
|
|
# IV.2
|
|
Application terminated with expected error: Access denied for user 'user3_mysqlx'@'localhost' (using password: YES) (code 1045)
|
|
ok
|
|
# IV.3
|
|
Application terminated with expected error: Access denied for user 'user3_mysqlx'@'localhost' (using password: YES) (code 1045)
|
|
ok
|
|
# IV.4
|
|
Application terminated with expected error: Access denied for user 'user3_mysqlx'@'localhost' (using password: YES) (code 1045)
|
|
ok
|
|
# IV.5
|
|
Application terminated with expected error: Access denied for user 'user3_mysqlx'@'localhost' (using password: YES) (code 1045)
|
|
ok
|
|
|
|
|
|
## V. Verify handling of locked account ('mysql_native_password')
|
|
#
|
|
# 1. Verify that locked account can't connect
|
|
# 2. Unlock the account and verify that it can connect
|
|
#
|
|
# V.1
|
|
Application terminated with expected error: Access denied for user 'user4_mysqlx'@'localhost'. Account is locked. (code 3118)
|
|
ok
|
|
# V.2
|
|
ALTER USER user4_mysqlx@localhost ACCOUNT UNLOCK;
|
|
Verify ["SELECT USER() as column_user" returns in "column_user" column, following value user4_mysqlx@localhost]
|
|
Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
|
|
Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1]
|
|
Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of DHE-RSA-AES256-SHA]
|
|
Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 14]
|
|
Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 14]
|
|
Mysqlx.Ok {
|
|
msg: "bye!"
|
|
}
|
|
ok
|
|
|
|
|
|
## VI. Verify handling of account that was marked as SSL required ('mysql_native_password')
|
|
#
|
|
# 1. Verify connection with basic SSL configuration
|
|
# 2. Verify connection with SSL key & cert
|
|
# 3. Verify connection with SSL ca
|
|
#
|
|
# VI.1
|
|
Verify ["SELECT USER() as column_user" returns in "column_user" column, following value user5_mysqlx@localhost]
|
|
Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
|
|
Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1]
|
|
Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of DHE-RSA-AES256-SHA]
|
|
Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 15]
|
|
Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 15]
|
|
Mysqlx.Ok {
|
|
msg: "bye!"
|
|
}
|
|
ok
|
|
# VI.2
|
|
Verify ["SELECT USER() as column_user" returns in "column_user" column, following value user5_mysqlx@localhost]
|
|
Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
|
|
Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1]
|
|
Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of DHE-RSA-AES256-SHA]
|
|
Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 16]
|
|
Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 16]
|
|
Mysqlx.Ok {
|
|
msg: "bye!"
|
|
}
|
|
ok
|
|
# VI.3
|
|
Verify ["SELECT USER() as column_user" returns in "column_user" column, following value user5_mysqlx@localhost]
|
|
Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
|
|
Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1]
|
|
Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of DHE-RSA-AES256-SHA]
|
|
Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 17]
|
|
Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 17]
|
|
Mysqlx.Ok {
|
|
msg: "bye!"
|
|
}
|
|
ok
|
|
|
|
|
|
## VII. Verify handling of account that was marked as SSL required with signed cert
|
|
## ('mysql_native_password')
|
|
#
|
|
# 1. Verify connection with signed cert
|
|
# 2. Verify that connection is rejected when SSL is disabled
|
|
# 3. Verify that connection is rejected when CA is set
|
|
# 4. Verify that connection is rejected when using basic SSL configuration
|
|
#
|
|
# VII.1
|
|
Verify ["SELECT USER() as column_user" returns in "column_user" column, following value user6_mysqlx@localhost]
|
|
Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
|
|
Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1]
|
|
Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of DHE-RSA-AES256-SHA]
|
|
Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 18]
|
|
Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 18]
|
|
Mysqlx.Ok {
|
|
msg: "bye!"
|
|
}
|
|
ok
|
|
# VII.2
|
|
Application terminated with expected error: Access denied for user 'user6_mysqlx'@'localhost' (using password: YES) (code 1045)
|
|
ok
|
|
# VII.3
|
|
Application terminated with expected error: Access denied for user 'user6_mysqlx'@'localhost' (using password: YES) (code 1045)
|
|
ok
|
|
# VII.4
|
|
Application terminated with expected error: Access denied for user 'user6_mysqlx'@'localhost' (using password: YES) (code 1045)
|
|
ok
|
|
|
|
|
|
## VIII. Verify handling of account that was marked that requires concrete SSL cipher
|
|
## ('mysql_native_password')
|
|
#
|
|
# 1. Verify connection with concrete cipher and signed cert
|
|
# 2. Verify that connection is rejected with concrete cipher and without a cert
|
|
# 3. Verify that connection is rejected when SSL is disabled
|
|
# 4. Verify that connection is rejected with basic SSL configuration
|
|
# 5. Verify that connection is rejected with wrong cipher and signed cert
|
|
#
|
|
# VIII.1
|
|
Verify ["SELECT USER() as column_user" returns in "column_user" column, following value user7_mysqlx@localhost]
|
|
Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
|
|
Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1]
|
|
Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of AES256-SHA]
|
|
Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 21]
|
|
Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 21]
|
|
Mysqlx.Ok {
|
|
msg: "bye!"
|
|
}
|
|
ok
|
|
# VIII.2
|
|
Application terminated with expected error: Access denied for user 'user7_mysqlx'@'localhost' (using password: NO) (code 1045)
|
|
ok
|
|
# VIII.3
|
|
Application terminated with expected error: Access denied for user 'user7_mysqlx'@'localhost' (using password: NO) (code 1045)
|
|
ok
|
|
# VIII.4
|
|
Application terminated with expected error: Access denied for user 'user7_mysqlx'@'localhost' (using password: NO) (code 1045)
|
|
ok
|
|
# VIII.5
|
|
Application terminated with expected error: Access denied for user 'user7_mysqlx'@'localhost' (using password: NO) (code 1045)
|
|
ok
|
|
|
|
|
|
## IX. Verify X Plugins global and session status variables ('mysql_native_password')
|
|
#
|
|
# 1. Verify global SSL setup
|
|
# 2. Verify session SSL setup
|
|
#
|
|
# IX.1
|
|
SHOW GLOBAL STATUS WHERE `Variable_name` RLIKE '^Mysqlx_ssl_(cipher_list|ctx_verify_(depth|mode)|server_not_(after|before)|verify_(depth|mode))$';
|
|
Variable_name Value
|
|
Mysqlx_ssl_cipher_list
|
|
Mysqlx_ssl_ctx_verify_depth -1
|
|
Mysqlx_ssl_ctx_verify_mode 5
|
|
Mysqlx_ssl_server_not_after Dec 1 04:48:40 2029 GMT
|
|
Mysqlx_ssl_server_not_before Dec 5 04:48:40 2014 GMT
|
|
Mysqlx_ssl_verify_depth
|
|
Mysqlx_ssl_verify_mode
|
|
# IX.2
|
|
RUN SHOW STATUS WHERE `Variable_name` RLIKE '^Mysqlx_ssl_(cipher_list|ctx_verify_(depth|mode)|server_not_(after|before)|verify_(depth|mode))$'
|
|
Variable_name Value
|
|
Mysqlx_ssl_cipher_list DHE-RSA-AES256-SHA:AES256-SHA
|
|
Mysqlx_ssl_ctx_verify_depth -1
|
|
Mysqlx_ssl_ctx_verify_mode 5
|
|
Mysqlx_ssl_server_not_after Dec 1 04:48:40 2029 GMT
|
|
Mysqlx_ssl_server_not_before Dec 5 04:48:40 2014 GMT
|
|
Mysqlx_ssl_verify_depth -1
|
|
Mysqlx_ssl_verify_mode 5
|
|
0 rows affected
|
|
Mysqlx.Ok {
|
|
msg: "bye!"
|
|
}
|
|
ok
|
|
|
|
|
|
## X. Lets confirm that Mysqlx plugin validates the connection_type vs 'require_secure_transport'
|
|
## system variable ('mysql_native_password')
|
|
#
|
|
# 1. Connection without SSL (fails on TCP, it works on UNIX socket)
|
|
# 2. Connection with SSL (always must be successful)
|
|
#
|
|
# X.1
|
|
SET GLOBAL require_secure_transport:=1;
|
|
Verify ["SELECT USER() as column_user" returns in "column_user" column, following value x_root@localhost]
|
|
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]
|
|
Verify [Status variable "Mysqlx_ssl_version" needs to have a value of ]
|
|
Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of ]
|
|
Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 25]
|
|
Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 25]
|
|
Mysqlx.Ok {
|
|
msg: "bye!"
|
|
}
|
|
ok
|
|
# X.2
|
|
Verify ["SELECT USER() as column_user" returns in "column_user" column, following value x_root@localhost]
|
|
Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
|
|
Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1]
|
|
Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of DHE-RSA-AES256-SHA]
|
|
Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 26]
|
|
Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 26]
|
|
Mysqlx.Ok {
|
|
msg: "bye!"
|
|
}
|
|
ok
|
|
SET GLOBAL require_secure_transport:=0;
|
|
|
|
|
|
## Cleanup
|
|
DROP USER user1_mysqlx@localhost;
|
|
DROP USER user2_mysqlx@localhost;
|
|
DROP USER user3_mysqlx@localhost;
|
|
DROP USER user4_mysqlx@localhost;
|
|
DROP USER user5_mysqlx@localhost;
|
|
DROP USER user6_mysqlx@localhost;
|
|
DROP USER user7_mysqlx@localhost;
|
|
SET GLOBAL mysqlx_connect_timeout = DEFAULT;
|
|
|
|
|
|
## Preamble
|
|
SET GLOBAL mysqlx_connect_timeout = 300;
|
|
call mtr.add_suppression("Maximum number of authentication attempts reached");
|
|
call mtr.add_suppression("Access denied for user .*");
|
|
CREATE USER user1_mysqlx@localhost
|
|
IDENTIFIED WITH 'sha256_password' BY 'auth_string1';
|
|
CREATE USER user2_mysqlx@localhost
|
|
IDENTIFIED WITH 'sha256_password' BY 'auth_string2'
|
|
PASSWORD EXPIRE;
|
|
CREATE USER user3_mysqlx@localhost
|
|
IDENTIFIED WITH 'sha256_password' BY 'auth_string3'
|
|
REQUIRE CIPHER "AES256-SHA" AND
|
|
SUBJECT "/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client"
|
|
ISSUER "/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=CA"
|
|
PASSWORD EXPIRE NEVER;
|
|
CREATE USER user4_mysqlx@localhost REQUIRE SSL ACCOUNT LOCK;
|
|
CREATE USER user5_mysqlx@localhost
|
|
IDENTIFIED WITH 'sha256_password' BY 'auth_string'
|
|
REQUIRE SSL;
|
|
CREATE USER user6_mysqlx@localhost
|
|
IDENTIFIED WITH 'sha256_password' BY 'dwh@#ghd'
|
|
REQUIRE x509;
|
|
CREATE USER user7_mysqlx@localhost
|
|
IDENTIFIED WITH 'sha256_password' BY ''
|
|
REQUIRE CIPHER "AES256-SHA";
|
|
GRANT ALL ON *.* TO user1_mysqlx@localhost;
|
|
GRANT ALL ON *.* TO user2_mysqlx@localhost;
|
|
GRANT ALL ON *.* TO user3_mysqlx@localhost;
|
|
GRANT ALL ON *.* TO user4_mysqlx@localhost;
|
|
GRANT ALL ON *.* TO user5_mysqlx@localhost;
|
|
GRANT ALL ON *.* TO user6_mysqlx@localhost;
|
|
GRANT ALL ON *.* TO user7_mysqlx@localhost;
|
|
|
|
|
|
## I. Verify handling of SSL with normal account ('sha256_password')
|
|
#
|
|
Verify ["SELECT USER() as column_user" returns in "column_user" column, following value user1_mysqlx@localhost]
|
|
Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
|
|
Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1]
|
|
Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of DHE-RSA-AES256-SHA]
|
|
Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 1]
|
|
Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 1]
|
|
Mysqlx.Ok {
|
|
msg: "bye!"
|
|
}
|
|
ok
|
|
|
|
|
|
## II. Verify handling of SSL with super account and XProtocol message
|
|
## CapabilitySet (without SSL options on it)
|
|
#
|
|
# 1. Try to set "tls" capability with invalid data (no-ssl connection)
|
|
# 2. Try to set "tls" capability with valid data (ssl connection)
|
|
#
|
|
# II.1
|
|
# setting read/write tls param with possible invalid data types:
|
|
# V_SINT,V_UINT ,V_NULL,V_OCTETS ,V_DOUBLE,V_FLOAT,V_STRING
|
|
send Mysqlx.Connection.CapabilitiesGet {
|
|
}
|
|
|
|
Mysqlx.Connection.Capabilities(capabilities[0]) = Mysqlx.Connection.Capability {
|
|
name: "tls"
|
|
value {
|
|
type: SCALAR
|
|
scalar {
|
|
type: V_BOOL
|
|
v_bool: false
|
|
}
|
|
}
|
|
}
|
|
|
|
# Checking CapSet TLS to bool 0
|
|
send Mysqlx.Connection.CapabilitiesSet {
|
|
capabilities {
|
|
capabilities {
|
|
name: "tls"
|
|
value {
|
|
type: SCALAR
|
|
scalar {
|
|
type: V_BOOL
|
|
v_bool: false
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
Got expected error:
|
|
Mysqlx.Error {
|
|
severity: ERROR
|
|
code: 5001
|
|
msg: "Capability prepare failed for \'tls\'"
|
|
sql_state: "HY000"
|
|
}
|
|
|
|
# CapSet TLS to null
|
|
send Mysqlx.Connection.CapabilitiesSet {
|
|
capabilities {
|
|
capabilities {
|
|
name: "tls"
|
|
value {
|
|
type: SCALAR
|
|
scalar {
|
|
type: V_NULL
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
Got expected error:
|
|
Mysqlx.Error {
|
|
severity: ERROR
|
|
code: 5001
|
|
msg: "Capability prepare failed for \'tls\'"
|
|
sql_state: "HY000"
|
|
}
|
|
|
|
# CapSet TLS to empty octet
|
|
send Mysqlx.Connection.CapabilitiesSet {
|
|
capabilities {
|
|
capabilities {
|
|
name: "tls"
|
|
value {
|
|
type: SCALAR
|
|
scalar {
|
|
type: V_OCTETS
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
Got expected error:
|
|
Mysqlx.Error {
|
|
severity: ERROR
|
|
code: 5001
|
|
msg: "Capability prepare failed for \'tls\'"
|
|
sql_state: "HY000"
|
|
}
|
|
|
|
# CapSet TLS to empty uint
|
|
send Mysqlx.Connection.CapabilitiesSet {
|
|
capabilities {
|
|
capabilities {
|
|
name: "tls"
|
|
value {
|
|
type: SCALAR
|
|
scalar {
|
|
type: V_UINT
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
Got expected error:
|
|
Mysqlx.Error {
|
|
severity: ERROR
|
|
code: 5001
|
|
msg: "Capability prepare failed for \'tls\'"
|
|
sql_state: "HY000"
|
|
}
|
|
|
|
# CapSet TLS to empty sint
|
|
send Mysqlx.Connection.CapabilitiesSet {
|
|
capabilities {
|
|
capabilities {
|
|
name: "tls"
|
|
value {
|
|
type: SCALAR
|
|
scalar {
|
|
type: V_SINT
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
Got expected error:
|
|
Mysqlx.Error {
|
|
severity: ERROR
|
|
code: 5001
|
|
msg: "Capability prepare failed for \'tls\'"
|
|
sql_state: "HY000"
|
|
}
|
|
|
|
ok
|
|
# II.2
|
|
send Mysqlx.Connection.CapabilitiesGet {
|
|
}
|
|
|
|
Mysqlx.Connection.Capabilities(capabilities[0]) = Mysqlx.Connection.Capability {
|
|
name: "tls"
|
|
value {
|
|
type: SCALAR
|
|
scalar {
|
|
type: V_BOOL
|
|
v_bool: false
|
|
}
|
|
}
|
|
}
|
|
|
|
send Mysqlx.Connection.CapabilitiesSet {
|
|
capabilities {
|
|
capabilities {
|
|
name: "tls"
|
|
value {
|
|
type: SCALAR
|
|
scalar {
|
|
type: V_BOOL
|
|
v_bool: true
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
Mysqlx.Ok {
|
|
}
|
|
|
|
# SSL Enabled
|
|
send Mysqlx.Connection.CapabilitiesGet {
|
|
}
|
|
|
|
Mysqlx.Connection.Capabilities(capabilities[0]) = Mysqlx.Connection.Capability {
|
|
name: "tls"
|
|
value {
|
|
type: SCALAR
|
|
scalar {
|
|
type: V_BOOL
|
|
v_bool: true
|
|
}
|
|
}
|
|
}
|
|
|
|
ok
|
|
|
|
|
|
## III. Verify handling of expired password with user2_mysqlx account ('sha256_password')
|
|
#
|
|
# 1. When client doesn't support handling of expired password,
|
|
# then connection must be rejected
|
|
# 2. When client support expired password, then he must be able
|
|
# to alter password
|
|
# 3. Verify that after the alter password, account can be used
|
|
# without any additional support for expired password on the client
|
|
# 4. When client support expired password, then he must be able
|
|
# to set the password
|
|
# 5. Verify that after the set password, account can be used
|
|
# without any additional support for expired password on the client
|
|
#
|
|
# III.1
|
|
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)
|
|
ok
|
|
# III.2
|
|
NOTICE: Account password expired
|
|
RUN ALTER USER USER() IDENTIFIED BY 'alter-new-auth'
|
|
|
|
0 rows affected
|
|
Mysqlx.Ok {
|
|
msg: "bye!"
|
|
}
|
|
ok
|
|
# III.3
|
|
Verify ["SELECT USER() as column_user" returns in "column_user" column, following value user2_mysqlx@localhost]
|
|
Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
|
|
Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1]
|
|
Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of DHE-RSA-AES256-SHA]
|
|
Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 5]
|
|
Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 5]
|
|
Mysqlx.Ok {
|
|
msg: "bye!"
|
|
}
|
|
ok
|
|
# III.4
|
|
ALTER USER user2_mysqlx@localhost PASSWORD EXPIRE;
|
|
NOTICE: Account password expired
|
|
RUN SET PASSWORD='set-new-auth'
|
|
|
|
0 rows affected
|
|
Mysqlx.Ok {
|
|
msg: "bye!"
|
|
}
|
|
ok
|
|
# III.5
|
|
Verify ["SELECT USER() as column_user" returns in "column_user" column, following value user2_mysqlx@localhost]
|
|
Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
|
|
Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1]
|
|
Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of DHE-RSA-AES256-SHA]
|
|
Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 7]
|
|
Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 7]
|
|
Mysqlx.Ok {
|
|
msg: "bye!"
|
|
}
|
|
ok
|
|
|
|
|
|
## IV. Verify handling of account that has requires concrete cipher, signed cert
|
|
## ('sha256_password')
|
|
#
|
|
# 1. Verify that user can connect when it has allowed cipher and matching cert
|
|
# 2. Verify that user can't connect when it has allowed cipher and non-matching cert
|
|
# 3. Verify that user can't connect when it doesn't use SSL
|
|
# 4. Verify that user can't connect when it use basic SSL configuration
|
|
# 5. Verify that user can't connect when it has allowed cipher and its without a cert
|
|
#
|
|
# IV.1
|
|
Verify ["SELECT USER() as column_user" returns in "column_user" column, following value user3_mysqlx@localhost]
|
|
Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
|
|
Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1]
|
|
Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of AES256-SHA]
|
|
Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 8]
|
|
Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 8]
|
|
Mysqlx.Ok {
|
|
msg: "bye!"
|
|
}
|
|
ok
|
|
# IV.2
|
|
Application terminated with expected error: Access denied for user 'user3_mysqlx'@'localhost' (using password: YES) (code 1045)
|
|
ok
|
|
# IV.3
|
|
Application terminated with expected error: Access denied for user 'user3_mysqlx'@'localhost' (using password: YES) (code 1045)
|
|
ok
|
|
# IV.4
|
|
Application terminated with expected error: Access denied for user 'user3_mysqlx'@'localhost' (using password: YES) (code 1045)
|
|
ok
|
|
# IV.5
|
|
Application terminated with expected error: Access denied for user 'user3_mysqlx'@'localhost' (using password: YES) (code 1045)
|
|
ok
|
|
|
|
|
|
## V. Verify handling of locked account ('sha256_password')
|
|
#
|
|
# 1. Verify that locked account can't connect
|
|
# 2. Unlock the account and verify that it can connect
|
|
#
|
|
# V.1
|
|
Application terminated with expected error: Access denied for user 'user4_mysqlx'@'localhost'. Account is locked. (code 3118)
|
|
ok
|
|
# V.2
|
|
ALTER USER user4_mysqlx@localhost ACCOUNT UNLOCK;
|
|
Verify ["SELECT USER() as column_user" returns in "column_user" column, following value user4_mysqlx@localhost]
|
|
Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
|
|
Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1]
|
|
Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of DHE-RSA-AES256-SHA]
|
|
Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 14]
|
|
Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 14]
|
|
Mysqlx.Ok {
|
|
msg: "bye!"
|
|
}
|
|
ok
|
|
|
|
|
|
## VI. Verify handling of account that was marked as SSL required ('sha256_password')
|
|
#
|
|
# 1. Verify connection with basic SSL configuration
|
|
# 2. Verify connection with SSL key & cert
|
|
# 3. Verify connection with SSL ca
|
|
#
|
|
# VI.1
|
|
Verify ["SELECT USER() as column_user" returns in "column_user" column, following value user5_mysqlx@localhost]
|
|
Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
|
|
Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1]
|
|
Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of DHE-RSA-AES256-SHA]
|
|
Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 15]
|
|
Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 15]
|
|
Mysqlx.Ok {
|
|
msg: "bye!"
|
|
}
|
|
ok
|
|
# VI.2
|
|
Verify ["SELECT USER() as column_user" returns in "column_user" column, following value user5_mysqlx@localhost]
|
|
Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
|
|
Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1]
|
|
Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of DHE-RSA-AES256-SHA]
|
|
Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 16]
|
|
Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 16]
|
|
Mysqlx.Ok {
|
|
msg: "bye!"
|
|
}
|
|
ok
|
|
# VI.3
|
|
Verify ["SELECT USER() as column_user" returns in "column_user" column, following value user5_mysqlx@localhost]
|
|
Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
|
|
Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1]
|
|
Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of DHE-RSA-AES256-SHA]
|
|
Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 17]
|
|
Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 17]
|
|
Mysqlx.Ok {
|
|
msg: "bye!"
|
|
}
|
|
ok
|
|
|
|
|
|
## VII. Verify handling of account that was marked as SSL required with signed cert
|
|
## ('sha256_password')
|
|
#
|
|
# 1. Verify connection with signed cert
|
|
# 2. Verify that connection is rejected when SSL is disabled
|
|
# 3. Verify that connection is rejected when CA is set
|
|
# 4. Verify that connection is rejected when using basic SSL configuration
|
|
#
|
|
# VII.1
|
|
Verify ["SELECT USER() as column_user" returns in "column_user" column, following value user6_mysqlx@localhost]
|
|
Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
|
|
Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1]
|
|
Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of DHE-RSA-AES256-SHA]
|
|
Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 18]
|
|
Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 18]
|
|
Mysqlx.Ok {
|
|
msg: "bye!"
|
|
}
|
|
ok
|
|
# VII.2
|
|
Application terminated with expected error: Access denied for user 'user6_mysqlx'@'localhost' (using password: YES) (code 1045)
|
|
ok
|
|
# VII.3
|
|
Application terminated with expected error: Access denied for user 'user6_mysqlx'@'localhost' (using password: YES) (code 1045)
|
|
ok
|
|
# VII.4
|
|
Application terminated with expected error: Access denied for user 'user6_mysqlx'@'localhost' (using password: YES) (code 1045)
|
|
ok
|
|
|
|
|
|
## VIII. Verify handling of account that was marked that requires concrete SSL cipher
|
|
## ('sha256_password')
|
|
#
|
|
# 1. Verify connection with concrete cipher and signed cert
|
|
# 2. Verify that connection is rejected with concrete cipher and without a cert
|
|
# 3. Verify that connection is rejected when SSL is disabled
|
|
# 4. Verify that connection is rejected with basic SSL configuration
|
|
# 5. Verify that connection is rejected with wrong cipher and signed cert
|
|
#
|
|
# VIII.1
|
|
Verify ["SELECT USER() as column_user" returns in "column_user" column, following value user7_mysqlx@localhost]
|
|
Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
|
|
Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1]
|
|
Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of AES256-SHA]
|
|
Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 21]
|
|
Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 21]
|
|
Mysqlx.Ok {
|
|
msg: "bye!"
|
|
}
|
|
ok
|
|
# VIII.2
|
|
Application terminated with expected error: Access denied for user 'user7_mysqlx'@'localhost' (using password: NO) (code 1045)
|
|
ok
|
|
# VIII.3
|
|
Application terminated with expected error: Access denied for user 'user7_mysqlx'@'localhost' (using password: NO) (code 1045)
|
|
ok
|
|
# VIII.4
|
|
Application terminated with expected error: Access denied for user 'user7_mysqlx'@'localhost' (using password: NO) (code 1045)
|
|
ok
|
|
# VIII.5
|
|
Application terminated with expected error: Access denied for user 'user7_mysqlx'@'localhost' (using password: NO) (code 1045)
|
|
ok
|
|
|
|
|
|
## IX. Verify X Plugins global and session status variables ('sha256_password')
|
|
#
|
|
# 1. Verify global SSL setup
|
|
# 2. Verify session SSL setup
|
|
#
|
|
# IX.1
|
|
SHOW GLOBAL STATUS WHERE `Variable_name` RLIKE '^Mysqlx_ssl_(cipher_list|ctx_verify_(depth|mode)|server_not_(after|before)|verify_(depth|mode))$';
|
|
Variable_name Value
|
|
Mysqlx_ssl_cipher_list
|
|
Mysqlx_ssl_ctx_verify_depth -1
|
|
Mysqlx_ssl_ctx_verify_mode 5
|
|
Mysqlx_ssl_server_not_after Dec 1 04:48:40 2029 GMT
|
|
Mysqlx_ssl_server_not_before Dec 5 04:48:40 2014 GMT
|
|
Mysqlx_ssl_verify_depth
|
|
Mysqlx_ssl_verify_mode
|
|
# IX.2
|
|
RUN SHOW STATUS WHERE `Variable_name` RLIKE '^Mysqlx_ssl_(cipher_list|ctx_verify_(depth|mode)|server_not_(after|before)|verify_(depth|mode))$'
|
|
Variable_name Value
|
|
Mysqlx_ssl_cipher_list DHE-RSA-AES256-SHA:AES256-SHA
|
|
Mysqlx_ssl_ctx_verify_depth -1
|
|
Mysqlx_ssl_ctx_verify_mode 5
|
|
Mysqlx_ssl_server_not_after Dec 1 04:48:40 2029 GMT
|
|
Mysqlx_ssl_server_not_before Dec 5 04:48:40 2014 GMT
|
|
Mysqlx_ssl_verify_depth -1
|
|
Mysqlx_ssl_verify_mode 5
|
|
0 rows affected
|
|
Mysqlx.Ok {
|
|
msg: "bye!"
|
|
}
|
|
ok
|
|
|
|
|
|
## X. Lets confirm that Mysqlx plugin validates the connection_type vs 'require_secure_transport'
|
|
## system variable ('sha256_password')
|
|
#
|
|
# 1. Connection without SSL (fails on TCP, it works on UNIX socket)
|
|
# 2. Connection with SSL (always must be successful)
|
|
#
|
|
# X.1
|
|
SET GLOBAL require_secure_transport:=1;
|
|
Verify ["SELECT USER() as column_user" returns in "column_user" column, following value x_root@localhost]
|
|
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]
|
|
Verify [Status variable "Mysqlx_ssl_version" needs to have a value of ]
|
|
Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of ]
|
|
Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 25]
|
|
Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 25]
|
|
Mysqlx.Ok {
|
|
msg: "bye!"
|
|
}
|
|
ok
|
|
# X.2
|
|
Verify ["SELECT USER() as column_user" returns in "column_user" column, following value x_root@localhost]
|
|
Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
|
|
Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1]
|
|
Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of DHE-RSA-AES256-SHA]
|
|
Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 26]
|
|
Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 26]
|
|
Mysqlx.Ok {
|
|
msg: "bye!"
|
|
}
|
|
ok
|
|
SET GLOBAL require_secure_transport:=0;
|
|
|
|
|
|
## Cleanup
|
|
DROP USER user1_mysqlx@localhost;
|
|
DROP USER user2_mysqlx@localhost;
|
|
DROP USER user3_mysqlx@localhost;
|
|
DROP USER user4_mysqlx@localhost;
|
|
DROP USER user5_mysqlx@localhost;
|
|
DROP USER user6_mysqlx@localhost;
|
|
DROP USER user7_mysqlx@localhost;
|
|
SET GLOBAL mysqlx_connect_timeout = DEFAULT;
|
|
|
|
|
|
## Preamble
|
|
SET GLOBAL mysqlx_connect_timeout = 300;
|
|
call mtr.add_suppression("Maximum number of authentication attempts reached");
|
|
call mtr.add_suppression("Access denied for user .*");
|
|
CREATE USER user1_mysqlx@localhost
|
|
IDENTIFIED WITH 'caching_sha2_password' BY 'auth_string1';
|
|
CREATE USER user2_mysqlx@localhost
|
|
IDENTIFIED WITH 'caching_sha2_password' BY 'auth_string2'
|
|
PASSWORD EXPIRE;
|
|
CREATE USER user3_mysqlx@localhost
|
|
IDENTIFIED WITH 'caching_sha2_password' BY 'auth_string3'
|
|
REQUIRE CIPHER "AES256-SHA" AND
|
|
SUBJECT "/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client"
|
|
ISSUER "/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=CA"
|
|
PASSWORD EXPIRE NEVER;
|
|
CREATE USER user4_mysqlx@localhost REQUIRE SSL ACCOUNT LOCK;
|
|
CREATE USER user5_mysqlx@localhost
|
|
IDENTIFIED WITH 'caching_sha2_password' BY 'auth_string'
|
|
REQUIRE SSL;
|
|
CREATE USER user6_mysqlx@localhost
|
|
IDENTIFIED WITH 'caching_sha2_password' BY 'dwh@#ghd'
|
|
REQUIRE x509;
|
|
CREATE USER user7_mysqlx@localhost
|
|
IDENTIFIED WITH 'caching_sha2_password' BY ''
|
|
REQUIRE CIPHER "AES256-SHA";
|
|
GRANT ALL ON *.* TO user1_mysqlx@localhost;
|
|
GRANT ALL ON *.* TO user2_mysqlx@localhost;
|
|
GRANT ALL ON *.* TO user3_mysqlx@localhost;
|
|
GRANT ALL ON *.* TO user4_mysqlx@localhost;
|
|
GRANT ALL ON *.* TO user5_mysqlx@localhost;
|
|
GRANT ALL ON *.* TO user6_mysqlx@localhost;
|
|
GRANT ALL ON *.* TO user7_mysqlx@localhost;
|
|
|
|
|
|
## I. Verify handling of SSL with normal account ('caching_sha2_password')
|
|
#
|
|
Verify ["SELECT USER() as column_user" returns in "column_user" column, following value user1_mysqlx@localhost]
|
|
Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
|
|
Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1]
|
|
Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of DHE-RSA-AES256-SHA]
|
|
Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 1]
|
|
Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 1]
|
|
Mysqlx.Ok {
|
|
msg: "bye!"
|
|
}
|
|
ok
|
|
|
|
|
|
## II. Verify handling of SSL with super account and XProtocol message
|
|
## CapabilitySet (without SSL options on it)
|
|
#
|
|
# 1. Try to set "tls" capability with invalid data (no-ssl connection)
|
|
# 2. Try to set "tls" capability with valid data (ssl connection)
|
|
#
|
|
# II.1
|
|
# setting read/write tls param with possible invalid data types:
|
|
# V_SINT,V_UINT ,V_NULL,V_OCTETS ,V_DOUBLE,V_FLOAT,V_STRING
|
|
send Mysqlx.Connection.CapabilitiesGet {
|
|
}
|
|
|
|
Mysqlx.Connection.Capabilities(capabilities[0]) = Mysqlx.Connection.Capability {
|
|
name: "tls"
|
|
value {
|
|
type: SCALAR
|
|
scalar {
|
|
type: V_BOOL
|
|
v_bool: false
|
|
}
|
|
}
|
|
}
|
|
|
|
# Checking CapSet TLS to bool 0
|
|
send Mysqlx.Connection.CapabilitiesSet {
|
|
capabilities {
|
|
capabilities {
|
|
name: "tls"
|
|
value {
|
|
type: SCALAR
|
|
scalar {
|
|
type: V_BOOL
|
|
v_bool: false
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
Got expected error:
|
|
Mysqlx.Error {
|
|
severity: ERROR
|
|
code: 5001
|
|
msg: "Capability prepare failed for \'tls\'"
|
|
sql_state: "HY000"
|
|
}
|
|
|
|
# CapSet TLS to null
|
|
send Mysqlx.Connection.CapabilitiesSet {
|
|
capabilities {
|
|
capabilities {
|
|
name: "tls"
|
|
value {
|
|
type: SCALAR
|
|
scalar {
|
|
type: V_NULL
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
Got expected error:
|
|
Mysqlx.Error {
|
|
severity: ERROR
|
|
code: 5001
|
|
msg: "Capability prepare failed for \'tls\'"
|
|
sql_state: "HY000"
|
|
}
|
|
|
|
# CapSet TLS to empty octet
|
|
send Mysqlx.Connection.CapabilitiesSet {
|
|
capabilities {
|
|
capabilities {
|
|
name: "tls"
|
|
value {
|
|
type: SCALAR
|
|
scalar {
|
|
type: V_OCTETS
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
Got expected error:
|
|
Mysqlx.Error {
|
|
severity: ERROR
|
|
code: 5001
|
|
msg: "Capability prepare failed for \'tls\'"
|
|
sql_state: "HY000"
|
|
}
|
|
|
|
# CapSet TLS to empty uint
|
|
send Mysqlx.Connection.CapabilitiesSet {
|
|
capabilities {
|
|
capabilities {
|
|
name: "tls"
|
|
value {
|
|
type: SCALAR
|
|
scalar {
|
|
type: V_UINT
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
Got expected error:
|
|
Mysqlx.Error {
|
|
severity: ERROR
|
|
code: 5001
|
|
msg: "Capability prepare failed for \'tls\'"
|
|
sql_state: "HY000"
|
|
}
|
|
|
|
# CapSet TLS to empty sint
|
|
send Mysqlx.Connection.CapabilitiesSet {
|
|
capabilities {
|
|
capabilities {
|
|
name: "tls"
|
|
value {
|
|
type: SCALAR
|
|
scalar {
|
|
type: V_SINT
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
Got expected error:
|
|
Mysqlx.Error {
|
|
severity: ERROR
|
|
code: 5001
|
|
msg: "Capability prepare failed for \'tls\'"
|
|
sql_state: "HY000"
|
|
}
|
|
|
|
ok
|
|
# II.2
|
|
send Mysqlx.Connection.CapabilitiesGet {
|
|
}
|
|
|
|
Mysqlx.Connection.Capabilities(capabilities[0]) = Mysqlx.Connection.Capability {
|
|
name: "tls"
|
|
value {
|
|
type: SCALAR
|
|
scalar {
|
|
type: V_BOOL
|
|
v_bool: false
|
|
}
|
|
}
|
|
}
|
|
|
|
send Mysqlx.Connection.CapabilitiesSet {
|
|
capabilities {
|
|
capabilities {
|
|
name: "tls"
|
|
value {
|
|
type: SCALAR
|
|
scalar {
|
|
type: V_BOOL
|
|
v_bool: true
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
Mysqlx.Ok {
|
|
}
|
|
|
|
# SSL Enabled
|
|
send Mysqlx.Connection.CapabilitiesGet {
|
|
}
|
|
|
|
Mysqlx.Connection.Capabilities(capabilities[0]) = Mysqlx.Connection.Capability {
|
|
name: "tls"
|
|
value {
|
|
type: SCALAR
|
|
scalar {
|
|
type: V_BOOL
|
|
v_bool: true
|
|
}
|
|
}
|
|
}
|
|
|
|
ok
|
|
|
|
|
|
## III. Verify handling of expired password with user2_mysqlx account ('caching_sha2_password')
|
|
#
|
|
# 1. When client doesn't support handling of expired password,
|
|
# then connection must be rejected
|
|
# 2. When client support expired password, then he must be able
|
|
# to alter password
|
|
# 3. Verify that after the alter password, account can be used
|
|
# without any additional support for expired password on the client
|
|
# 4. When client support expired password, then he must be able
|
|
# to set the password
|
|
# 5. Verify that after the set password, account can be used
|
|
# without any additional support for expired password on the client
|
|
#
|
|
# III.1
|
|
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)
|
|
ok
|
|
# III.2
|
|
NOTICE: Account password expired
|
|
RUN ALTER USER USER() IDENTIFIED BY 'alter-new-auth'
|
|
|
|
0 rows affected
|
|
Mysqlx.Ok {
|
|
msg: "bye!"
|
|
}
|
|
ok
|
|
# III.3
|
|
Verify ["SELECT USER() as column_user" returns in "column_user" column, following value user2_mysqlx@localhost]
|
|
Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
|
|
Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1]
|
|
Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of DHE-RSA-AES256-SHA]
|
|
Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 5]
|
|
Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 5]
|
|
Mysqlx.Ok {
|
|
msg: "bye!"
|
|
}
|
|
ok
|
|
# III.4
|
|
ALTER USER user2_mysqlx@localhost PASSWORD EXPIRE;
|
|
NOTICE: Account password expired
|
|
RUN SET PASSWORD='set-new-auth'
|
|
|
|
0 rows affected
|
|
Mysqlx.Ok {
|
|
msg: "bye!"
|
|
}
|
|
ok
|
|
# III.5
|
|
Verify ["SELECT USER() as column_user" returns in "column_user" column, following value user2_mysqlx@localhost]
|
|
Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
|
|
Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1]
|
|
Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of DHE-RSA-AES256-SHA]
|
|
Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 7]
|
|
Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 7]
|
|
Mysqlx.Ok {
|
|
msg: "bye!"
|
|
}
|
|
ok
|
|
|
|
|
|
## IV. Verify handling of account that has requires concrete cipher, signed cert
|
|
## ('caching_sha2_password')
|
|
#
|
|
# 1. Verify that user can connect when it has allowed cipher and matching cert
|
|
# 2. Verify that user can't connect when it has allowed cipher and non-matching cert
|
|
# 3. Verify that user can't connect when it doesn't use SSL
|
|
# 4. Verify that user can't connect when it use basic SSL configuration
|
|
# 5. Verify that user can't connect when it has allowed cipher and its without a cert
|
|
#
|
|
# IV.1
|
|
Verify ["SELECT USER() as column_user" returns in "column_user" column, following value user3_mysqlx@localhost]
|
|
Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
|
|
Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1]
|
|
Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of AES256-SHA]
|
|
Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 8]
|
|
Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 8]
|
|
Mysqlx.Ok {
|
|
msg: "bye!"
|
|
}
|
|
ok
|
|
# IV.2
|
|
Application terminated with expected error: Access denied for user 'user3_mysqlx'@'localhost' (using password: YES) (code 1045)
|
|
ok
|
|
# IV.3
|
|
Application terminated with expected error: Access denied for user 'user3_mysqlx'@'localhost' (using password: YES) (code 1045)
|
|
ok
|
|
# IV.4
|
|
Application terminated with expected error: Access denied for user 'user3_mysqlx'@'localhost' (using password: YES) (code 1045)
|
|
ok
|
|
# IV.5
|
|
Application terminated with expected error: Access denied for user 'user3_mysqlx'@'localhost' (using password: YES) (code 1045)
|
|
ok
|
|
|
|
|
|
## V. Verify handling of locked account ('caching_sha2_password')
|
|
#
|
|
# 1. Verify that locked account can't connect
|
|
# 2. Unlock the account and verify that it can connect
|
|
#
|
|
# V.1
|
|
Application terminated with expected error: Access denied for user 'user4_mysqlx'@'localhost'. Account is locked. (code 3118)
|
|
ok
|
|
# V.2
|
|
ALTER USER user4_mysqlx@localhost ACCOUNT UNLOCK;
|
|
Verify ["SELECT USER() as column_user" returns in "column_user" column, following value user4_mysqlx@localhost]
|
|
Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
|
|
Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1]
|
|
Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of DHE-RSA-AES256-SHA]
|
|
Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 14]
|
|
Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 14]
|
|
Mysqlx.Ok {
|
|
msg: "bye!"
|
|
}
|
|
ok
|
|
|
|
|
|
## VI. Verify handling of account that was marked as SSL required ('caching_sha2_password')
|
|
#
|
|
# 1. Verify connection with basic SSL configuration
|
|
# 2. Verify connection with SSL key & cert
|
|
# 3. Verify connection with SSL ca
|
|
#
|
|
# VI.1
|
|
Verify ["SELECT USER() as column_user" returns in "column_user" column, following value user5_mysqlx@localhost]
|
|
Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
|
|
Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1]
|
|
Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of DHE-RSA-AES256-SHA]
|
|
Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 15]
|
|
Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 15]
|
|
Mysqlx.Ok {
|
|
msg: "bye!"
|
|
}
|
|
ok
|
|
# VI.2
|
|
Verify ["SELECT USER() as column_user" returns in "column_user" column, following value user5_mysqlx@localhost]
|
|
Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
|
|
Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1]
|
|
Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of DHE-RSA-AES256-SHA]
|
|
Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 16]
|
|
Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 16]
|
|
Mysqlx.Ok {
|
|
msg: "bye!"
|
|
}
|
|
ok
|
|
# VI.3
|
|
Verify ["SELECT USER() as column_user" returns in "column_user" column, following value user5_mysqlx@localhost]
|
|
Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
|
|
Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1]
|
|
Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of DHE-RSA-AES256-SHA]
|
|
Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 17]
|
|
Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 17]
|
|
Mysqlx.Ok {
|
|
msg: "bye!"
|
|
}
|
|
ok
|
|
|
|
|
|
## VII. Verify handling of account that was marked as SSL required with signed cert
|
|
## ('caching_sha2_password')
|
|
#
|
|
# 1. Verify connection with signed cert
|
|
# 2. Verify that connection is rejected when SSL is disabled
|
|
# 3. Verify that connection is rejected when CA is set
|
|
# 4. Verify that connection is rejected when using basic SSL configuration
|
|
#
|
|
# VII.1
|
|
Verify ["SELECT USER() as column_user" returns in "column_user" column, following value user6_mysqlx@localhost]
|
|
Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
|
|
Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1]
|
|
Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of DHE-RSA-AES256-SHA]
|
|
Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 18]
|
|
Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 18]
|
|
Mysqlx.Ok {
|
|
msg: "bye!"
|
|
}
|
|
ok
|
|
# VII.2
|
|
Application terminated with expected error: Access denied for user 'user6_mysqlx'@'localhost' (using password: YES) (code 1045)
|
|
ok
|
|
# VII.3
|
|
Application terminated with expected error: Access denied for user 'user6_mysqlx'@'localhost' (using password: YES) (code 1045)
|
|
ok
|
|
# VII.4
|
|
Application terminated with expected error: Access denied for user 'user6_mysqlx'@'localhost' (using password: YES) (code 1045)
|
|
ok
|
|
|
|
|
|
## VIII. Verify handling of account that was marked that requires concrete SSL cipher
|
|
## ('caching_sha2_password')
|
|
#
|
|
# 1. Verify connection with concrete cipher and signed cert
|
|
# 2. Verify that connection is rejected with concrete cipher and without a cert
|
|
# 3. Verify that connection is rejected when SSL is disabled
|
|
# 4. Verify that connection is rejected with basic SSL configuration
|
|
# 5. Verify that connection is rejected with wrong cipher and signed cert
|
|
#
|
|
# VIII.1
|
|
Verify ["SELECT USER() as column_user" returns in "column_user" column, following value user7_mysqlx@localhost]
|
|
Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
|
|
Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1]
|
|
Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of AES256-SHA]
|
|
Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 21]
|
|
Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 21]
|
|
Mysqlx.Ok {
|
|
msg: "bye!"
|
|
}
|
|
ok
|
|
# VIII.2
|
|
Application terminated with expected error: Access denied for user 'user7_mysqlx'@'localhost' (using password: NO) (code 1045)
|
|
ok
|
|
# VIII.3
|
|
Application terminated with expected error: Access denied for user 'user7_mysqlx'@'localhost' (using password: NO) (code 1045)
|
|
ok
|
|
# VIII.4
|
|
Application terminated with expected error: Access denied for user 'user7_mysqlx'@'localhost' (using password: NO) (code 1045)
|
|
ok
|
|
# VIII.5
|
|
Application terminated with expected error: Access denied for user 'user7_mysqlx'@'localhost' (using password: NO) (code 1045)
|
|
ok
|
|
|
|
|
|
## IX. Verify X Plugins global and session status variables ('caching_sha2_password')
|
|
#
|
|
# 1. Verify global SSL setup
|
|
# 2. Verify session SSL setup
|
|
#
|
|
# IX.1
|
|
SHOW GLOBAL STATUS WHERE `Variable_name` RLIKE '^Mysqlx_ssl_(cipher_list|ctx_verify_(depth|mode)|server_not_(after|before)|verify_(depth|mode))$';
|
|
Variable_name Value
|
|
Mysqlx_ssl_cipher_list
|
|
Mysqlx_ssl_ctx_verify_depth -1
|
|
Mysqlx_ssl_ctx_verify_mode 5
|
|
Mysqlx_ssl_server_not_after Dec 1 04:48:40 2029 GMT
|
|
Mysqlx_ssl_server_not_before Dec 5 04:48:40 2014 GMT
|
|
Mysqlx_ssl_verify_depth
|
|
Mysqlx_ssl_verify_mode
|
|
# IX.2
|
|
RUN SHOW STATUS WHERE `Variable_name` RLIKE '^Mysqlx_ssl_(cipher_list|ctx_verify_(depth|mode)|server_not_(after|before)|verify_(depth|mode))$'
|
|
Variable_name Value
|
|
Mysqlx_ssl_cipher_list DHE-RSA-AES256-SHA:AES256-SHA
|
|
Mysqlx_ssl_ctx_verify_depth -1
|
|
Mysqlx_ssl_ctx_verify_mode 5
|
|
Mysqlx_ssl_server_not_after Dec 1 04:48:40 2029 GMT
|
|
Mysqlx_ssl_server_not_before Dec 5 04:48:40 2014 GMT
|
|
Mysqlx_ssl_verify_depth -1
|
|
Mysqlx_ssl_verify_mode 5
|
|
0 rows affected
|
|
Mysqlx.Ok {
|
|
msg: "bye!"
|
|
}
|
|
ok
|
|
|
|
|
|
## X. Lets confirm that Mysqlx plugin validates the connection_type vs 'require_secure_transport'
|
|
## system variable ('caching_sha2_password')
|
|
#
|
|
# 1. Connection without SSL (fails on TCP, it works on UNIX socket)
|
|
# 2. Connection with SSL (always must be successful)
|
|
#
|
|
# X.1
|
|
SET GLOBAL require_secure_transport:=1;
|
|
Verify ["SELECT USER() as column_user" returns in "column_user" column, following value x_root@localhost]
|
|
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]
|
|
Verify [Status variable "Mysqlx_ssl_version" needs to have a value of ]
|
|
Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of ]
|
|
Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 25]
|
|
Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 25]
|
|
Mysqlx.Ok {
|
|
msg: "bye!"
|
|
}
|
|
ok
|
|
# X.2
|
|
Verify ["SELECT USER() as column_user" returns in "column_user" column, following value x_root@localhost]
|
|
Verify ["SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE processlist_id = connection_id()" returns in "CONNECTION_TYPE" column, following value SSL/TLS]
|
|
Verify [Status variable "Mysqlx_ssl_version" needs to have a value of TLSv1]
|
|
Verify [Status variable "Mysqlx_ssl_cipher" needs to have a value of DHE-RSA-AES256-SHA]
|
|
Verify [Status variable "Mysqlx_ssl_accepts" needs to have a value of 26]
|
|
Verify [Status variable "Mysqlx_ssl_finished_accepts" needs to have a value of 26]
|
|
Mysqlx.Ok {
|
|
msg: "bye!"
|
|
}
|
|
ok
|
|
SET GLOBAL require_secure_transport:=0;
|
|
|
|
|
|
## Cleanup
|
|
DROP USER user1_mysqlx@localhost;
|
|
DROP USER user2_mysqlx@localhost;
|
|
DROP USER user3_mysqlx@localhost;
|
|
DROP USER user4_mysqlx@localhost;
|
|
DROP USER user5_mysqlx@localhost;
|
|
DROP USER user6_mysqlx@localhost;
|
|
DROP USER user7_mysqlx@localhost;
|
|
SET GLOBAL mysqlx_connect_timeout = DEFAULT;
|