TLS, Certificates, Secure Transport, Network Segmentation, and Secret Rotation

Learning outcomes

An application can have perfect least-privilege grants and still leak credentials or data if it connects over an untrusted network without authenticating the server. Transport Layer Security (TLS) provides encryption in transit and can also authenticate peers using X.509 certificates. Encryption and identity verification are related but distinct: an encrypted connection to the wrong server is still vulnerable to a man-in-the-middle attack.

1. Observe the current transport before changing it

SHOW GLOBAL VARIABLES LIKE 'have_ssl';SHOW GLOBAL VARIABLES LIKE 'tls_version';SHOW GLOBAL VARIABLES LIKE 'require_secure_transport';SHOW GLOBAL VARIABLES LIKE 'ssl_%';SHOW SESSION STATUS LIKE 'Ssl_version';SHOW SESSION STATUS LIKE 'Ssl_cipher';

A non-empty Ssl_version in the current session proves this session negotiated TLS. It does not prove the certificate was validated against the intended hostname unless the client’s verification policy is known. Likewise, have_ssl describes server capability/state, not whether every account is forced to use TLS.

2. Disposable CA and server certificate lab

For learning, create a local Certificate Authority (CA) and a server certificate whose Subject Alternative Name (SAN) matches the hostname used by the client. Production certificates should come from your organization’s PKI and private keys must be protected outside source control.

# Free local tooling; works on Linux/macOS and Windows where OpenSSL is installed.openssl genrsa -out ca-key.pem 3072openssl req -x509 -new -key ca-key.pem -sha256 -days 30   -subj "/CN=ServiceHub Lab CA" -out ca.pemopenssl genrsa -out server-key.pem 3072openssl req -new -key server-key.pem   -subj "/CN=localhost" -out server.csrprintf "subjectAltName=DNS:localhost,IP:127.0.0.1extendedKeyUsage=serverAuth" > server-ext.cnfopenssl x509 -req -in server.csr -CA ca.pem -CAkey ca-key.pem   -CAcreateserial -days 30 -sha256 -extfile server-ext.cnf -out server-cert.pemopenssl x509 -in server-cert.pem -noout -subject -issuer -dates -ext subjectAltName

The short validity is deliberate for a disposable lab, not a production recommendation. Protect ca-key.pem and server-key.pem; anyone holding the CA private key can issue certificates trusted by this lab CA.

3. Configure MariaDB and verify effective values

Custom server certificate paths are startup configuration. Put them in a dedicated override file for the lab rather than editing vendor defaults. Paths and service-management commands are platform/package specific, so the configuration below is conceptual syntax for the MariaDB option file.

[mariadb]ssl_ca=/absolute/path/to/ca.pemssl_cert=/absolute/path/to/server-cert.pemssl_key=/absolute/path/to/server-key.pem# Optional global policy after testing all clients:# require_secure_transport=ON

Restart the disposable server using your platform’s normal service/container mechanism, then rerun the TLS evidence queries. A restart that “succeeded” is not proof the files were loaded; verify ssl_ca, ssl_cert, ssl_key, the error log and a real TLS connection.

mariadb -h localhost -u root -p   --ssl-ca=/absolute/path/to/ca.pem   --ssl-verify-server-cert

On current MariaDB clients, certificate verification behavior is stronger than older releases, but the explicit flags make the lab’s trust assumptions visible. The hostname supplied with -h must match the certificate identity rules.

4. Account requirements and server-wide secure transport

DROP USER IF EXISTS 'svc_tls_app'@'localhost';CREATE USER 'svc_tls_app'@'localhost'  IDENTIFIED BY 'LabOnly-TLS-App-42!'  REQUIRE SSL;GRANT USAGE ON *.* TO 'svc_tls_app'@'localhost';SHOW CREATE USER 'svc_tls_app'@'localhost';

REQUIRE SSL requires TLS but not a client certificate. REQUIRE X509 additionally requires a valid client X.509 certificate. REQUIRE SUBJECT, ISSUER and CIPHER can narrow certificate expectations further where justified and supported.

SHOW GLOBAL VARIABLES LIKE 'require_secure_transport';SET GLOBAL require_secure_transport=ON;SHOW GLOBAL VARIABLES LIKE 'require_secure_transport';

require_secure_transport rejects insecure transports but treats local Unix sockets and named pipes as secure transports. Because it is dynamic, a lab can enable and disable it, but production persistence still belongs in configuration management. Test every application, health check, backup tool and replication/cluster connection before enforcing it.

5. Deliberately wrong: encrypted but not verified

The unsafe mental model is “I used --ssl, therefore the server is authentic.” Older client versions commonly enabled encryption without certificate verification by default, and current behavior still depends on client version/options. The attack is not that TLS is absent—it is that the client may accept a certificate for the wrong peer.

# Stronger explicit policy for a CA-backed lab:mariadb -h localhost -u svc_tls_app -p   --ssl-ca=/absolute/path/to/ca.pem   --ssl-verify-server-cert# Deliberately unsafe diagnostic form: encryption can remain while peer verification is disabled.# Never make this the production default.mariadb -h localhost -u svc_tls_app -p   --ssl-ca=/absolute/path/to/ca.pem   --disable-ssl-verify-server-cert

Inside each session, run SHOW SESSION STATUS LIKE 'Ssl_version'. Both may report TLS; only the client policy tells you whether server identity was checked. That is why “encrypted” and “authenticated peer” must be separate checklist items.

6. Network segmentation and secret rotation are separate layers

MariaDB account host matching is authorization metadata, not a packet filter. Bind addresses, host firewalls, cloud security groups, container networks and proxies determine whether traffic can reach the server at all. Expose only the interfaces/subnets that must connect, then still require authenticated MariaDB accounts and verified TLS.

ALTER USER 'svc_tls_app'@'localhost'  IDENTIFIED BY 'LabOnly-TLS-App-Rotated-43!'  REQUIRE SSL;SHOW CREATE USER 'svc_tls_app'@'localhost';SHOW GRANTS FOR 'svc_tls_app'@'localhost';

A real rotation is a coordinated application operation: stage the new secret in your secret manager, update consumers, verify new connections, drain old pools if needed, revoke the old credential, and confirm no stale clients remain. Never place production passwords in SQL migrations or shell history.

7. Production verification and cleanup

1. Verify the certificate SAN matches the hostname clients use.
2. Verify TLS session status after every connection-policy change.
3. Test expected connection failure when TLS requirements are not met.
4. Inventory every connector/tool version before global enforcement.
5. Protect certificate private keys and database secrets with OS/secret-manager permissions.

SET GLOBAL require_secure_transport=OFF;DROP USER IF EXISTS 'svc_tls_app'@'localhost';

Lesson 5 combines identity, authorization and transport into an operational hardening baseline with audit evidence, file/plugin risk and patch discipline.