Documentation Home
Security in MySQL
Related Documentation Download this Excerpt
PDF (US Ltr) - 1.2Mb
PDF (A4) - 1.2Mb
EPUB - 369.4Kb
HTML Download (TGZ) - 287.0Kb
HTML Download (Zip) - 296.8Kb

Security in MySQL  /  MySQL User Account Management  /  Using SSL for Secure Connections

5.12 Using SSL for Secure Connections

With an unencrypted connection between the MySQL client and the server, someone with access to the network could watch all your traffic and look at the data being sent or received, or even change the data while it is in transit between client and server.

When you must move information over a network in a secure fashion, an unencrypted connection is unacceptable. Encryption is the way to make any kind of data unreadable. 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.

MySQL supports secure (encrypted) connections between clients and the server using the Secure Sockets Layer (SSL) protocol. SSL uses encryption algorithms to ensure that data received over a public network can be trusted. It has mechanisms to detect any data change, loss, or replay. SSL also incorporates algorithms that provide identity verification using the X509 standard.

X509 makes it possible to identify someone on the Internet. It is most commonly used in e-commerce applications. 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 show the certificate to another party as proof of identity. A certificate consists of its owner's public key. Any data encrypted with this public key can be decrypted only using the corresponding secret key, which is held by the owner of the certificate.

For more information about SSL, X509, encryption, or public-key cryptography, perform an Internet search for the keywords in which you are interested.

MySQL supports SSL using the TLSv1.0 protocol. It does not support SSL 2.0 or SSL 3.0 because they provide weak encryption. To see which protocol version an SSL connection uses, check the value of the Ssl_version status variable using this query:

mysql> SHOW SESSION STATUS LIKE 'Ssl_version';
| Variable_name | Value |
| Ssl_version   | TLSv1 |

MySQL enables SSL encryption on a per-connection basis, and use of SSL can be optional or mandatory. You can choose an unencrypted connection or a secure SSL connection according to the requirements of individual applications. For information on how to require users to use SSL connections, see the discussion of the REQUIRE clause of the CREATE USER statement in CREATE USER Syntax.

Several improvements were made to SSL support in MySQL 5.7. The following timeline summarizes the changes:

  • 5.7.3: On the client side, an explicit --ssl option is no longer advisory but prescriptive. Given a server enabled to support SSL, a client program can require an SSL conection by specifying only the --ssl option. The connection attempt fails if SSL is not available. Other --ssl-xxx options on the client side mean that SSL is advisory.

  • 5.7.5: The server-side --ssl option value is enabled by default.

    For servers compiled using OpenSSL, the auto_generate_certs and sha256_password_auto_generate_rsa_keys system variables are available to enable autogeneration and autodiscovery of SSL/RSA certificate and key files at startup. For SSL autodiscovery, if --ssl is enabled and other SSL options are not given to configure SSL explicitly, the server attempts to enable SSL automatically at startup if it discovers the requisite SSL files in the data directory.

  • 5.7.6: The mysql_ssl_rsa_setup utility is supplied to make it easier to manually generate SSL/RSA certificate and key files. Autodiscovery of SSL/RSA files at startup is expanded to apply to all servers, whether compiled using OpenSSL or yaSSL.

    If the server discovers at startup that the CA certificate is self-signed, it writes a warning to the error log. (The certificate will be self-signed if created automatically by the server or manually using mysql_ssl_rsa_setup.)

  • 5.7.7: The C client library tries to establish an SSL connection by default whenever the server is enabled to support SSL. This affects client programs as follows:

    • In the absence of an --ssl option, the client falls back to an unencrypted connection if SSL is not available.

    • To require an SSL connection and fail if SSL is unavailable, invoke the client with an explicit --ssl option.

    • To suppress the attempt at using SSL for the connection, specify the --ssl=0 option.

    This change also affects subsequent releases of MySQL Cconnectors that are based on the C client library: Connector/C, Connector/C++, and Connector/ODBC.

Secure connections are are available through the MySQL C API using the mysql_ssl_set() function. See mysql_ssl_set().

Replication uses the C API, so secure connections can be used between master and slave servers. See Setting Up Replication Using SSL.

MySQL can be compiled using OpenSSL or yaSSL. For a comparison of the two packages, see Section 5.12.1, “OpenSSL Versus yaSSL”

Another way to connect securely is from within an SSH connection to the MySQL server host. For an example, see Section 5.14, “Connecting to MySQL Remotely from Windows with SSH”.

Download this Excerpt
PDF (US Ltr) - 1.2Mb
PDF (A4) - 1.2Mb
EPUB - 369.4Kb
HTML Download (TGZ) - 287.0Kb
HTML Download (Zip) - 296.8Kb
User Comments
Sign Up Login You must be logged in to post a comment.