MySQL-Sicherheitsbest Practices

Introduction

In this lab, you will learn how to enhance the security of your MySQL database. We will cover essential security best practices, including changing the root user password, enabling SSL for secure connections, revoking excess privileges from a user, and removing anonymous user accounts. By the end of this lab, you will have a better understanding of how to secure your MySQL server.

Change the Root User Password

In this step, you will change the root user password in MySQL. The root user has the highest privileges, so it is essential to keep its password strong and secure.

First, access the MySQL shell as the root user. Open your terminal in the LabEx VM. The default directory is ~/project.

To access the MySQL shell as the root user, execute the following command:

sudo mysql -u root

You might be prompted for the labex user password. Since labex has sudo privileges without a password, just press Enter.

Now you are in the MySQL shell. You will see a prompt that looks like mysql>.

Next, change the root user password. MySQL 8.0 and later versions use the caching_sha2_password authentication plugin. Use the ALTER USER statement to update the password. Replace your_new_password with a strong and unique password.

ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'your_new_password';

Important: Choose a strong password that includes a mix of uppercase and lowercase letters, numbers, and symbols. Do not use a password you use for other accounts.

After executing the command, you should see the following output:

Query OK, 0 rows affected (0.00 sec)

Now, flush the privileges to ensure the changes take effect immediately:

FLUSH PRIVILEGES;

You should see the following output:

Query OK, 0 rows affected (0.00 sec)

Finally, exit the MySQL shell by typing:

exit

You will return to the regular terminal prompt.

Now, test the new password. Try logging in to the MySQL shell using the new password:

mysql -u root

You will be prompted for the password. Enter the new password you set. If you successfully log in, you have successfully changed the root user password.

Enable SSL for Secure Connections

In this step, you will enable SSL (Secure Sockets Layer) for secure connections to your MySQL server. SSL encrypts the data transmitted between the client and the server, protecting it from eavesdropping and tampering.

First, you need to generate the SSL certificates and keys. You will use the openssl command-line tool for this.

Open your terminal in the LabEx VM. The default directory is ~/project.

Navigate to the ssl directory that was created during setup:

cd ssl

Now, generate the server key:

sudo openssl genrsa 2048 > server-key.pem

Next, generate the certificate signing request (CSR):

sudo openssl req -new -key server-key.pem -out server-req.pem

You will be prompted for information such as country name, state, city, organization name, etc. You can enter appropriate values or leave them blank. For the "Common Name", enter localhost or the server's hostname/IP address.

Now, sign the certificate request to create the SSL certificate:

sudo openssl x509 -days 365 -in server-req.pem -signkey server-key.pem -out server-cert.pem

This command creates a self-signed certificate valid for 365 days.

Next, generate the client key:

sudo openssl genrsa 2048 > client-key.pem

Generate the client certificate signing request (CSR):

sudo openssl req -new -key client-key.pem -out client-req.pem

You will be prompted for information similar to the server CSR.

Now, sign the client certificate request:

sudo openssl x509 -days 365 -in client-req.pem -signkey client-key.pem -out client-cert.pem

Finally, generate the CA (Certificate Authority) key and certificate:

sudo openssl genrsa 2048 > ca-key.pem
sudo openssl req -new -x509 -days 365 -key ca-key.pem -out ca-cert.pem

Now you have the necessary SSL files in the ~/project/ssl directory.

Next, you need to configure MySQL to use these SSL certificates. You will edit the my.cnf file located in the ~/project directory.

Navigate back to the ~/project directory:

cd ~/project

Edit the my.cnf file using nano:

nano my.cnf

Add the following lines under the [mysqld] section in the my.cnf file:

ssl-cert=/var/lib/mysql/ssl/server-cert.pem
ssl-key=/var/lib/mysql/ssl/server-key.pem
ssl-ca=/var/lib/mysql/ssl/ca-cert.pem
#require_secure_transport=ON

Save the file and exit nano (Press Ctrl + X, then Y, then Enter).

Important: The paths in the my.cnf file are relative to the MySQL container's filesystem. The setup script has already created the my.cnf file and the ssl directory in ~/project. In a real-world scenario, you would need to copy the generated certificate files to the location specified in my.cnf inside the MySQL server's data directory and restart the MySQL server. Due to the limitations of the LabEx environment, we will skip copying the files and restarting the server.

Now, let's connect to the MySQL server using SSL. You will need to provide the paths to the CA certificate, client certificate, and client key.

mysql --ssl-ca=~/project/ssl/ca-cert.pem --ssl-cert=~/project/ssl/client-cert.pem --ssl-key=~/project/ssl/client-key.pem -u root -p

Enter the root password you set in the previous step when prompted.

To verify that the connection is using SSL, execute the following SQL command in the MySQL shell:

SHOW STATUS LIKE 'Ssl_cipher';

If the Value column shows a cipher, then the connection is using SSL.

+---------------+-----------------------+
| Variable_name | Value                 |
+---------------+-----------------------+
| Ssl_cipher    | TLS_AES_256_GCM_SHA384 |
+---------------+-----------------------+
1 row in set (0.00 sec)

If the Value column is empty, then the connection is not using SSL.

Exit the MySQL shell by typing:

exit

Revoke Excess Privileges from a User

In this step, you will revoke excess privileges from a MySQL user. This is a crucial security practice to limit the potential damage if a user account is compromised. You will work with the testuser and testdb that were created during the setup.

First, access the MySQL shell as the root user.

sudo mysql -u root

You are now in the MySQL shell.

The testuser currently has SELECT, INSERT, and UPDATE privileges on the testdb database. You can verify this by showing the grants for the user:

SHOW GRANTS FOR 'testuser'@'localhost';

The output will show the privileges granted to testuser.

+---------------------------------------------------------------------------------------------------------------------------------------+
| Grants for testuser@localhost                                                                                                       |
+---------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE ON `testdb`.* TO `testuser`@`localhost`                                                                |
| GRANT USAGE ON *.* TO `testuser`@`localhost`                                                                                           |
+---------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Now, let's say you want to revoke the UPDATE privilege from testuser. You can use the REVOKE statement.

REVOKE UPDATE ON testdb.* FROM 'testuser'@'localhost';

You should see the following output:

Query OK, 0 rows affected (0.00 sec)

Now, flush the privileges to ensure the changes take effect immediately:

FLUSH PRIVILEGES;

You should see the following output:

Query OK, 0 rows affected (0.00 sec)

To verify that the UPDATE privilege has been revoked, show the grants for testuser again:

SHOW GRANTS FOR 'testuser'@'localhost';

The output should now show only SELECT and INSERT privileges on testdb.

+---------------------------------------------------------------------------------------------------------------------------------------+
| Grants for testuser@localhost                                                                                                       |
+---------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT ON `testdb`.* TO `testuser`@`localhost`                                                                        |
| GRANT USAGE ON *.* TO `testuser`@`localhost`                                                                                           |
+---------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Exit the MySQL shell by typing:

exit

Remove Anonymous User Accounts

In this step, you will remove anonymous user accounts from your MySQL server. Anonymous user accounts have a username of '' (empty string) and can potentially allow unauthorized access to your database. It is a good security practice to remove these accounts.

First, access the MySQL shell as the root user.

sudo mysql -u root

You are now in the MySQL shell.

To identify anonymous user accounts, you can query the mysql.user table.

SELECT User, Host FROM mysql.user WHERE User='';

This query will show you any users with an empty username. The Host column indicates the host from which the user can connect. You might see entries like '@'localhost or '@'%'. The setup script has already removed these, but it's good to know how to check.

To remove an anonymous user account, use the DROP USER statement. For example, to remove the anonymous user account that can connect from localhost, execute the following command:

DROP USER ''@'localhost';

You should see the following output:

Query OK, 0 rows affected (0.00 sec)

If there are other anonymous user accounts (e.g., '@'%'), remove them as well:

DROP USER ''@'%';

You should see the following output:

Query OK, 0 rows affected (0.00 sec)

After removing the anonymous user accounts, flush the privileges to ensure the changes take effect immediately:

FLUSH PRIVILEGES;

You should see the following output:

Query OK, 0 rows affected (0.00 sec)

To verify that the anonymous user accounts have been removed, run the SELECT query again:

SELECT User, Host FROM mysql.user WHERE User='';

This time, the query should return an empty result set.

Exit the MySQL shell by typing:

exit

Summary

In this lab, you have learned several essential MySQL security best practices. You successfully changed the root user password, which is a fundamental step in securing your database. You also explored how to enable SSL for secure connections, although the full configuration and restart were skipped due to environment limitations. Furthermore, you practiced revoking excess privileges from a user, demonstrating the principle of least privilege. Finally, you learned how to identify and remove anonymous user accounts, eliminating a potential security vulnerability. By applying these practices, you can significantly enhance the security posture of your MySQL database.