## 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: Authentication failed, check username and password or try a secure connection (code 2510)
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: Authentication failed, check username and password or try a secure connection (code 2510)
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;
Application terminated with expected error: Connections using insecure transport are prohibited while --require_secure_transport=ON. (code 3159)
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: Authentication failed, check username and password or try a secure connection (code 2510)
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: Authentication failed, check username and password or try a secure connection (code 2510)
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;
Application terminated with expected error: Connections using insecure transport are prohibited while --require_secure_transport=ON. (code 3159)
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: Authentication failed, check username and password or try a secure connection (code 2510)
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: Authentication failed, check username and password or try a secure connection (code 2510)
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;
Application terminated with expected error: Connections using insecure transport are prohibited while --require_secure_transport=ON. (code 3159)
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;