TLS for Client Connections, Certificate Validation, and Secure Transport Enforcement

Learning outcomes

ServiceHub has moved from a developer laptop to a small internal network. The application can still connect, but the team has never proved whether the TCP session is encrypted, whether the client verifies the server certificate, or whether a plaintext fallback is possible. Those are three different questions. This lesson turns them into observable tests rather than configuration folklore.

Encryption is not the same as identity verification

Transport Layer Security (TLS) protects bytes moving between a MySQL client and server. Encryption prevents a passive observer from reading SQL statements and result sets. Certificate validation answers a different question: whether the certificate presented by the server chains to a trusted Certificate Authority (CA). Hostname verification adds another identity check: whether the hostname the client used matches the identity encoded in the certificate.

A client can therefore have an encrypted connection and still be vulnerable to a sophisticated man-in-the-middle scenario if it does not validate the server identity. MySQL's command-line client makes this distinction visible through --ssl-mode.

Inspect the server before changing anything

-- Run as a local administrator on a disposable MySQL instance.CREATE DATABASE IF NOT EXISTS servicehub_security_lab  CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;CREATE TABLE IF NOT EXISTS servicehub_security_lab.security_events (  event_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,  event_time TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),  actor VARCHAR(80) NOT NULL,  event_type VARCHAR(40) NOT NULL,  detail VARCHAR(255) NOT NULL) ENGINE=InnoDB;INSERT INTO servicehub_security_lab.security_events(actor,event_type,detail)VALUES ('chapter12','LAB_START','Chapter 12 disposable security lab');SELECT COUNT(*) AS event_rowsFROM servicehub_security_lab.security_events;

SELECT VERSION() AS server_version,       @@hostname AS server_host,       @@port AS tcp_port,       @@GLOBAL.require_secure_transport AS require_secure_transport;SHOW VARIABLES WHERE Variable_name IN  ('ssl_ca','ssl_cert','ssl_key','tls_version');SHOW SESSION STATUS LIKE 'Ssl_cipher';SHOW SESSION STATUS LIKE 'Ssl_version';-- In the mysql command-line client, \s also reports the current SSL state.

On a typical packaged MySQL installation, certificate files may have been generated during initialization. Do not infer that from filenames alone: a current session with a nonempty Ssl_cipher is stronger runtime evidence. Likewise, an empty value tells you this session is not using TLS; it does not prove that the server is incapable of TLS.

Prove secure transport with positive and negative TCP tests

Use explicit TCP for this experiment. A Unix socket and Windows shared memory are considered secure transports by require_secure_transport, so they would not test the plaintext-TCP boundary.

# Bash, PowerShell, or Command Prompt: let -p prompt securely for the password.mysql -h 127.0.0.1 -P 3306 -u root -p --ssl-mode=REQUIRED# After connecting:SHOW SESSION STATUS LIKE 'Ssl_cipher';SHOW SESSION STATUS LIKE 'Ssl_version';

-- Keep this administrator session open so you can revert the change.SELECT @@GLOBAL.require_secure_transport AS before_value;SET GLOBAL require_secure_transport = ON;SELECT @@GLOBAL.require_secure_transport AS during_test;

# Open a SECOND terminal and force TCP plus plaintext.mysql -h 127.0.0.1 -P 3306 -u root -p --ssl-mode=DISABLED# Expected result when require_secure_transport=ON:# ERROR ... Secure transport required

The exact numeric error code and client wording can vary by client build, so the durable evidence is the failure category: the server refuses the nonsecure TCP transport. This is a configuration test, not a privilege test; granting more database privileges would not repair it.

-- If the original value was OFF, restore it now.SET GLOBAL require_secure_transport = OFF;SELECT @@GLOBAL.require_secure_transport AS restored_value;

Certificate validation: why VERIFY_IDENTITY can fail when REQUIRED succeeds

REQUIRED accepts any server certificate that permits creation of a TLS channel; it does not authenticate that certificate against a CA. VERIFY_CA additionally requires a trusted CA, and VERIFY_IDENTITY also compares the connection hostname with the certificate identity. A local auto-generated certificate may therefore work with REQUIRED yet fail a hostname-verifying test if its names do not match the hostname you used.

# Replace the path and host with values from YOUR trusted local certificate setup.mysql --host=db.servicehub.test --port=3306   --user=svc12_tls --password   --ssl-mode=VERIFY_IDENTITY   --ssl-ca=/path/to/trusted/ca.pem

Per-account transport policy and least privilege

Server-wide require_secure_transport controls transports for all accounts. MySQL also supports account-specific requirements. This is useful when an application account must use TLS even if a local administrative workflow still uses a socket.

DROP USER IF EXISTS 'svc12_tls'@'127.0.0.1';CREATE USER 'svc12_tls'@'127.0.0.1'  IDENTIFIED BY 'LabOnly-Rotate-After-Use!2026'  REQUIRE SSL;GRANT SELECT ON servicehub_security_lab.* TO 'svc12_tls'@'127.0.0.1';SHOW CREATE USER 'svc12_tls'@'127.0.0.1';SHOW GRANTS FOR 'svc12_tls'@'127.0.0.1';

Now test once with --ssl-mode=REQUIRED and once with --ssl-mode=DISABLED. The second connection should be denied even if the global variable is OFF, because the account itself requires SSL. The minimum repair for an insecure client is to make the client use TLS—not to remove REQUIRE SSL.

Production judgment and monitoring

Production clients should normally verify server identity, not merely encrypt opportunistically. The exact connector property differs among Java, .NET, Python, Node.js, Go, and managed services, so translate the same policy—encryption required, CA validated, hostname checked—into the connector you actually deploy. Track certificate expiry and trust-chain changes as operational dependencies.

Do not persist require_secure_transport=ON

Summary and bridge to Lesson 2

You can now prove whether a MySQL session is encrypted and distinguish that fact from CA and hostname verification. The next lesson moves from data in motion to data at rest, where the critical question is not only “is this file encrypted?” but also “where are the keys, and can we still restore the data if one dependency disappears?”

Authoritative references

• MySQL 8.4 — Configuring MySQL to Use Encrypted Connections
• MySQL 8.4 — Security-Related mysqld Options and Variables
• MySQL 8.4 — CREATE USER SSL/TLS options