blog
Are my MySQL server connections encrypted and safe?
One of the greatest factors and fundamentals of data governance is security. It is a good practice to have database security implemented in place whenever you have your data management involved for enterprise or mass consumption.
Data security is one of the most significant aspects of administering a database. It plays a critical role for which every database management should implement. When it’s implemented and done correctly, the result shall improve not only your data security but also affects system stability, enhances the development life-cycle, boosts your data compliance, and enhances security awareness down to your team level. Everyone does not want their data to end up in the wrong hands. If data is breached, it not only risks the confidentiality and integrity of your data but also leaves your organization open to significant financial risks. Even for just a simple database management implementation, if you find out someone has already intruded your system, the feeling of being insecure and scared as to what consequences shall bring you is totally discomforting.
Determining if your MySQL server connection is safe depends on how securely MySQL transmits data-in-transit. With an unencrypted connection between the MySQL client and the server, someone with access to the network could watch all your traffic and inspect the data being sent or received between client and server.
When you must move information over a network in a secure fashion, an unencrypted connection is unacceptable. To make any kind of data unreadable, use encryption. Encryption algorithms must include security elements to resist many kinds of known attacks such as changing the order of encrypted messages or replaying data twice.
But my MySQL is safe, right?
Believing that your MySQL is safe without determining its stability and vulnerability checks is like a religion. You tend to believe even without seeing it, even without touching it. The problem is, MySQL is a technology and its existence is not based on abstract thoughts. It has to be tested, it has to be proven, and it requires safety and follows best practices that have been tested as well by others.
Determining if your MySQL server connections i.e. in-transit are safe or if it is encrypted relies on “how did you setup your database?” or “who setup your database?”.
MySQL supports encrypted connections between clients and the server using the TLS (Transport Layer Security) protocol. TLS is sometimes referred to as SSL (Secure Sockets Layer) but MySQL does not actually use the SSL protocol for encrypted connections because its encryption is weak and SSL has been already deprecated in favor of TLS. TLS uses encryption algorithms to ensure that data received over a public network can be trusted. It has mechanisms to detect data change, loss, or replay. TLS also incorporates algorithms that provide identity verification using the X.509 standard. SSL or TLS is being used interchangeably but for the context of encryption with MySQL, TLS is being used for which MySQL supports encrypted connections using the TLSv1, TLSv1.1, TLSv1.2, and TLSv1.3 protocols.
X.509 makes it possible to identify someone on the Internet. In basic terms, there should be some entity called a “Certificate Authority” (or CA) that assigns electronic certificates to anyone who needs them. Certificates rely on asymmetric encryption algorithms that have two encryption keys (a public key and a secret key). A certificate owner can present the certificate to another party as proof of identity. A certificate consists of its owner’s public key. Any data encrypted using this public key can be decrypted only using the corresponding secret key, which is held by the owner of the certificate.
Just like the Spartans used Scytale
Scytale is known to be used as a way to encrypt and decrypt a message which was used around 400 B.C. by the Spartans. They would write a message on a sheet of papyrus (a type of paper) that was wrapped around a staff. The recipient can only decipher the message if the correct diameter and size of the staff. It serves as a way to encrypt and avoid unauthorized extraction of messages or data to the target destination.
Just like with MySQL, using SSL/TLS protocols and ciphers is a way to avoid someone extracting your data or hijacking your data as it passes the wire or over the internet.
By default, MySQL programs attempt to connect using encryption if the server supports encrypted connections, falling back to an unencrypted connection if an encrypted connection cannot be established. Since version MySQL >= 5.7, TLS/SSL and RSA files can be created or generated with the support of variables. For MySQL distributions compiled using OpenSSL, the MySQL server has the capability of automatically generating missing SSL and RSA files at startup. The auto_generate_certs, sha256_password_auto_generate_rsa_keys, and caching_sha2_password_auto_generate_rsa_keys (version >= 8.0), system variables control automatic generation of these files. These variables are enabled by default. They can be enabled at startup and inspected but not set at runtime.
By default, these variables are set to ON or enabled. Otherwise, users can invoke the mysql_ssl_rsa_setup utility manually. For some distribution types, such as RPM and DEB packages, mysql_ssl_rsa_setup invocation occurs during data directory initialization. In this case, the MySQL distribution need not have been compiled using OpenSSL as long as the openssl command is available.
Once these files are available and/or generated, MySQL will still not use encryption connections for the following reasons. As mentioned earlier, by default, MySQL client programs attempt to establish an encrypted connection if the server supports encrypted connections, with further control available through the –ssl-mode (or –ssl <= 5.7.11 as this is already deprecated) option:
-
By default, if MySQL connection is not flagged with –ssl-mode, the default value is set to –ssl-mode=PREFFERED. Therefore, clients attempt to connect using encryption, falling back to an unencrypted connection if an encrypted connection cannot be established.
-
With –ssl-mode=REQUIRED, clients require an encrypted connection and fail if one cannot be established.
-
With –ssl-mode=DISABLED, clients use an unencrypted connection.
-
With –ssl-mode=VERIFY_CA or –ssl-mode=VERIFY_IDENTITY, clients require an encrypted connection and also perform verification against the server CA certificate and (with VERIFY_IDENTITY) against the server host name in its certificate.
With the default mechanism of MySQL to use a preferred connection, likely it tries to attempt to use the encrypted or secured connection but this still leaves some things to do and to determine.
As mentioned earlier, the auto_generate_certs, sha256_password_auto_generate_rsa_keys, and caching_sha2_password_auto_generate_rsa_keys (version >= 8.0) variables help generate the required SSL/TLS and RSA files, with the normal user without such any requirements during connection shall still be insecure. For example, let’s create a user called dbadmin.
mysql> create user 'dbadmin'@'192.168.40.%' identified by 'R00tP@55';
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO 'dbadmin'@'192.168.40.%';
Query OK, 0 rows affected (0.01 sec)Then verify if the variables are set correctly which should be enabled as they are by default:
mysql> show global variables where variable_name in ('auto_generate_certs','sha256_password_auto_generate_rsa_keys','caching_sha2_password_auto_generate_rsa_keys');
+----------------------------------------------+-------+
| Variable_name | Value |
+----------------------------------------------+-------+
| auto_generate_certs | ON |
| caching_sha2_password_auto_generate_rsa_keys | ON |
| sha256_password_auto_generate_rsa_keys | ON |
+----------------------------------------------+-------+
3 rows in set (0.00 sec)Verifying if the files are generated accordingly in path /var/lib/mysql/ (or the path of datadir for this MySQL):
$ find /var/lib/mysql -name "*.pem"
/var/lib/mysql/ca-key.pem
/var/lib/mysql/ca.pem
/var/lib/mysql/server-key.pem
/var/lib/mysql/server-cert.pem
/var/lib/mysql/client-key.pem
/var/lib/mysql/client-cert.pem
/var/lib/mysql/private_key.pem
/var/lib/mysql/public_key.pemThen verify if SSL files are loaded correctly:
mysql> show global variables like 'ssl%';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| ssl_ca | ca.pem |
| ssl_capath | |
| ssl_cert | server-cert.pem |
| ssl_cipher | |
| ssl_crl | |
| ssl_crlpath | |
| ssl_fips_mode | OFF |
| ssl_key | server-key.pem |
+---------------+-----------------+
8 rows in set (0.00 sec)Determine your connection’s security
Now, this looks good. It does also mean that MySQL is ready to accept encrypted connections. But connecting to MySQL as is, as stated it shall use –ssl-mode=PREFFERED by default, or if –ssl-mode is not specified, it will still failback to use an unencrypted connection. See below:
$ mysql -pR00tP@55 -h 192.168.40.110 -udbadmin -e “status;”|grep ssl -i
SSL: Not in use
This reveals that it’s not using a secured connection. Checking the SSL session status variables if any ciphers are used reveals empty:
mysql> show global status like 'ssl%';
+--------------------------------+--------------------------+
| Variable_name | Value |
+--------------------------------+--------------------------+
| Ssl_accept_renegotiates | 0 |
| Ssl_accepts | 2 |
| Ssl_callback_cache_hits | 0 |
| Ssl_cipher | |
| Ssl_cipher_list | |
| Ssl_client_connects | 0 |
| Ssl_connect_renegotiates | 0 |
| Ssl_ctx_verify_depth | 18446744073709551615 |
| Ssl_ctx_verify_mode | 5 |
| Ssl_default_timeout | 0 |
| Ssl_finished_accepts | 2 |
| Ssl_finished_connects | 0 |
| Ssl_server_not_after | Aug 28 12:48:46 2031 GMT |
| Ssl_server_not_before | Aug 30 12:48:46 2021 GMT |
| Ssl_session_cache_hits | 0 |
| Ssl_session_cache_misses | 0 |
| Ssl_session_cache_mode | SERVER |
| Ssl_session_cache_overflows | 0 |
| Ssl_session_cache_size | 128 |
| Ssl_session_cache_timeouts | 0 |
| Ssl_sessions_reused | 0 |
| Ssl_used_session_cache_entries | 0 |
| Ssl_verify_depth | 0 |
| Ssl_verify_mode | 0 |
| Ssl_version | |
+--------------------------------+--------------------------+
25 rows in set (0.002 sec)Enforcing a secured connection
Since it reveals that the connection is still not secured, MySQL introduces the require_secure_transport variable which requires that all connections to be made should be encrypted and secured. Any attempts to connect for an unsecured connection fail. For example, enabling it on the server:
mysql> set global require_secure_transport=1;
Query OK, 0 rows affected (0.00 sec)Trying to connect as a client using an unencrypted connection will fail:
$ mysql -pR00tP@55 -h 192.168.40.110 -udbadmin
ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.To connect successfully and securely, you need to specify the ssl-ca, ssl-cert, ssl-key variables. See below:
$ mysql -pR00tP@55 -h 192.168.40.110 -udbadmin --ssl-ca=/tmp/pem/ca.pem --ssl-cert=/tmp/pem/server-cert.pem --ssl-key=/tmp/pem/server-key.pem -e "show global status like 'ssl%'G"
*************************** 1. row ***************************
Variable_name: Ssl_accept_renegotiates
Value: 0
*************************** 2. row ***************************
Variable_name: Ssl_accepts
Value: 16
*************************** 3. row ***************************
Variable_name: Ssl_callback_cache_hits
Value: 0
*************************** 4. row ***************************
Variable_name: Ssl_cipher
Value: TLS_AES_256_GCM_SHA384
*************************** 5. row ***************************
Variable_name: Ssl_cipher_list
Value: TLS_AES_256_GCM_SHA384:TLS_CHACHA20_POLY1305_SHA256:TLS_AES_128_GCM_SHA256:TLS_AES_128_CCM_SHA256:ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-RSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES128-SHA256:ECDHE-RSA-AES128-SHA256:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-AES256-SHA384:ECDHE-RSA-AES256-SHA384:DHE-RSA-AES128-GCM-SHA256:DHE-DSS-AES128-GCM-SHA256:DHE-RSA-AES128-SHA256:DHE-DSS-AES128-SHA256:DHE-DSS-AES256-GCM-SHA384:DHE-RSA-AES256-SHA256:DHE-DSS-AES256-SHA256:DHE-RSA-AES256-GCM-SHA384:ECDHE-RSA-AES128-SHA:ECDHE-ECDSA-AES128-SHA:ECDHE-RSA-AES256-SHA:ECDHE-ECDSA-AES256-SHA:DHE-DSS-AES128-SHA:DHE-RSA-AES128-SHA:DHE-DSS-AES256-SHA:DHE-RSA-AES256-SHA:AES256-SHA:CAMELLIA256-SHA:CAMELLIA128-SHA:AES128-GCM-SHA256:AES256-GCM-SHA384:AES128-SHA256:AES256-SHA256:AES128-SHA
*************************** 6. row ***************************
Variable_name: Ssl_client_connects
Value: 0
*************************** 7. row ***************************
Variable_name: Ssl_connect_renegotiates
Value: 0
*************************** 8. row ***************************
Variable_name: Ssl_ctx_verify_depth
Value: 18446744073709551615
*************************** 9. row ***************************
Variable_name: Ssl_ctx_verify_mode
Value: 5
*************************** 10. row ***************************
Variable_name: Ssl_default_timeout
Value: 7200
*************************** 11. row ***************************
Variable_name: Ssl_finished_accepts
Value: 11
*************************** 12. row ***************************
Variable_name: Ssl_finished_connects
Value: 0
*************************** 13. row ***************************
Variable_name: Ssl_server_not_after
Value: Aug 28 12:48:46 2031 GMT
*************************** 14. row ***************************
Variable_name: Ssl_server_not_before
Value: Aug 30 12:48:46 2021 GMT
*************************** 15. row ***************************
Variable_name: Ssl_session_cache_hits
Value: 0
*************************** 16. row ***************************
Variable_name: Ssl_session_cache_misses
Value: 0
*************************** 17. row ***************************
Variable_name: Ssl_session_cache_mode
Value: SERVER
*************************** 18. row ***************************
Variable_name: Ssl_session_cache_overflows
Value: 0
*************************** 19. row ***************************
Variable_name: Ssl_session_cache_size
Value: 128
*************************** 20. row ***************************
Variable_name: Ssl_session_cache_timeouts
Value: 0
*************************** 21. row ***************************
Variable_name: Ssl_sessions_reused
Value: 0
*************************** 22. row ***************************
Variable_name: Ssl_used_session_cache_entries
Value: 0
*************************** 23. row ***************************
Variable_name: Ssl_verify_depth
Value: 18446744073709551615
*************************** 24. row ***************************
Variable_name: Ssl_verify_mode
Value: 5
*************************** 25. row ***************************
Variable_name: Ssl_version
Value: TLSv1.3Alternatively, if a user is created with REQUIRED SSL for example, that should also connect you using SSL regardless that require_secure_transport is disabled, which is its default value. Take note that, if require_secure_transport is enabled, its capability supplements per-account SSL requirements, which take precedence. Therefore, if an account is defined with REQUIRE SSL, enabling require_secure_transport does not make it possible to use the account to connect using a Unix socket file.
Making sure MySQL server deployments are encrypted and safe
Hassle-free is what we always look forward to so that there are no other problems and concerns to worry about. ClusterControl deploys MySQL databases using encrypted connections and generates the SSL and RSA certificates for you. For example, a screenshot below showing you the job activity of a Create Cluster command from ClusterControl.
It sets up the SSL and RSA files and places them in /etc/mysql/certs/ path just like below:
mysql> show global variables like 'ssl%';
+---------------+--------------------------------+
| Variable_name | Value |
+---------------+--------------------------------+
| ssl_ca | /etc/mysql/certs/server_ca.crt |
| ssl_capath | |
| ssl_cert | /etc/mysql/certs/server.crt |
| ssl_cipher | |
| ssl_crl | |
| ssl_crlpath | |
| ssl_key | /etc/mysql/certs/server.key |
+---------------+--------------------------------+
7 rows in set (0.00 sec)Then ClusterControl also groups the generated SSL and RSA files in a centralized fashion under the Key Management navigation panel as shown below:
Once deployed, all you have to do is either create users with REQUIRED SSL or have require_secure_transport if you want to enforce an encrypted and secured layer for your MySQL server connections.
