This article explains using MySQL self-signed SSL certificates for a secure connection on Ubuntu 24.04.
By default, when you install MySQL server, it will only allow connections from the local system for users with the correct credentials, regardless of the transport protocol. Creating a self-signed certificate for MySQL can enhance security by enabling encrypted connections between the server and clients.
By generating and using a self-signed certificate, you can secure your MySQL connections and mitigate various security vulnerabilities while maintaining control over your database environment.
Configure MySQL SSL connection
When you install the MySQL database server, it automatically creates and configures SSL settings in the database. Users are not required to use SSL to connect.
Run the command below to list MySQL self-signed SSL certificate files with MySQL installed.
sudo bash
ls -al /var/lib/mysql/*.pem
Here’s a list of MySQL certificate files.
-rw------- 1 mysql mysql 1705 Feb 21 11:20 /var/lib/mysql/ca-key.pem
-rw-r--r-- 1 mysql mysql 1112 Feb 21 11:20 /var/lib/mysql/ca.pem
-rw-r--r-- 1 mysql mysql 1112 Feb 21 11:20 /var/lib/mysql/client-cert.pem
-rw------- 1 mysql mysql 1705 Feb 21 11:20 /var/lib/mysql/client-key.pem
-rw------- 1 mysql mysql 1705 Feb 21 11:20 /var/lib/mysql/private_key.pem
-rw-r--r-- 1 mysql mysql 452 Feb 21 11:20 /var/lib/mysql/public_key.pem
-rw-r--r-- 1 mysql mysql 1112 Feb 21 11:20 /var/lib/mysql/server-cert.pem
-rw------- 1 mysql mysql 1705 Feb 21 11:20 /var/lib/mysql/server-key.pem
MySQL database is also configured to allow SSL connection. You can validate that by running the SQL statement below.
First, log on to the MySQL database.
sudo mysql
Then, run the SQL statement to list the SSL tables.
show variables like '%ssl%';
The result should be similar to the one below.
+-------------------------------------+-----------------+
| Variable_name | Value |
+-------------------------------------+-----------------+
| admin_ssl_ca | |
| admin_ssl_capath | |
| admin_ssl_cert | |
| admin_ssl_cipher | |
| admin_ssl_crl | |
| admin_ssl_crlpath | |
| admin_ssl_key | |
| have_openssl | YES |
| have_ssl | YES |
| mysqlx_ssl_ca | |
| mysqlx_ssl_capath | |
| mysqlx_ssl_cert | |
| mysqlx_ssl_cipher | |
| mysqlx_ssl_crl | |
| mysqlx_ssl_crlpath | |
| mysqlx_ssl_key | |
| performance_schema_show_processlist | OFF |
| 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 |
| ssl_session_cache_mode | ON |
| ssl_session_cache_timeout | 300 |
+-------------------------------------+-----------------+
27 rows in set (0.00 sec)
You can also see how long the certificates are valid by running the command below.
show status like 'Ssl_server_not%';
It should output similar lines as below.
+-----------------------+--------------------------+
| Variable_name | Value |
+-----------------------+--------------------------+
| Ssl_server_not_after | Feb 19 17:20:43 2035 GMT |
| Ssl_server_not_before | Feb 21 17:20:43 2025 GMT |
+-----------------------+--------------------------+
Force users to connect with SSL
Now we know SSL is configured; you can force users to always use SSL when connecting to the database.
For new users, run the SQL statement below to create a new user named jdoe and type a new password.
CREATE USER jdoe IDENTIFIED BY 'type_your_password_here' require ssl;
Replace jdoe with the name of the account you want to create.
By running the statement below, you can validate all the database accounts that must use SSL when connecting.
select user,host,ssl_type,plugin from mysql.user;
Your output should look similar to the one below.
+------------------+-----------+----------+-----------------------+
| user | host | ssl_type | plugin |
+------------------+-----------+----------+-----------------------+
| jdoe | % | ANY | caching_sha2_password |
| debian-sys-maint | localhost | | caching_sha2_password |
| mysql.infoschema | localhost | | caching_sha2_password |
| mysql.session | localhost | | caching_sha2_password |
| mysql.sys | localhost | | caching_sha2_password |
| root | localhost | | auth_socket |
+------------------+-----------+----------+-----------------------+
Execute the SQL statement below to force existing database accounts to use SSL.
alter user 'root'@'localhost' require ssl;
Connect to MySQL using SSL
Now that users must use SSL to connect to MySQL, they must run the command below to access the MySQL database from the local host.
mysql -u jdoe -p --protocol=tcp
If they’re using a database tool, they must enable SSL for the connection to succeed.
That should do it!
Conclusion:
In conclusion, using a self-signed SSL certificate with MySQL on Ubuntu 24.04 significantly enhances the security of your database connections. Here are the key takeaways:
- Improved Security: Self-signed SSL certificates encrypt data in transit, protecting sensitive information from potential interception.
- Easy Configuration: MySQL automatically configures SSL settings upon installation, simplifying the process of enabling secure connections.
- User Restrictions: You can enforce that all users connect securely using SSL, minimizing vulnerability.
- Access Validation: Commands to check SSL configurations to ensure the setup is correct and functional.
- Database Tool Integration: Ensure your database tools are set to use SSL for successful connections.
Following these guidelines can create a more secure environment for managing your MySQL databases.
Leave a Reply