In this lab, you will learn how to enhance the security of your MySQL database. We will cover essential security best practices, including setting a password for the root user, creating dedicated users according to the principle of least privilege, managing user permissions, and removing anonymous accounts. By the end of this lab, you will have a better understanding of how to secure your MySQL server from common vulnerabilities.
A default MySQL installation often allows the root user to connect without a password from the local machine. Your first step in securing MySQL is to set a strong password for the root account.
First, open the terminal from your desktop.
Connect to the MySQL server as the root user. In this lab environment, you can use sudo to connect without a password.
sudo mysql -u rootOnce connected, you will see the MySQL prompt (mysql>).
Now, set a password for the root user. The ALTER USER statement is used to modify user accounts. Replace YourStrongPassword! with a password of your choice.
ALTER USER 'root'@'localhost' IDENTIFIED BY 'YourStrongPassword!';This command sets a password for the root user when connecting from localhost. You should see the following output, confirming the command was successful:
Query OK, 0 rows affected (0.01 sec)Next, apply the privilege changes immediately using the FLUSH PRIVILEGES command.
FLUSH PRIVILEGES;The output will be:
Query OK, 0 rows affected (0.00 sec)You have now successfully set a password for the root user. While you can still use sudo mysql -u root to connect, any direct connection attempt with mysql -u root -p will now require the password you just set. This is a critical first step in securing your database.
Using the root account for applications is a security risk because it has unlimited permissions. A best practice is to create dedicated users with limited privileges for each application. In this step, you will create a new database and a user with access only to that database.
Now, create a new database for your application. Let's name it app_db.
CREATE DATABASE app_db;You should see this confirmation:
Query OK, 1 row affected (0.01 sec)Next, create a new user that your application will use to connect to the database. We will name this user app_user and assign it a password.
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'UserPassword123';The output will be:
Query OK, 0 rows affected (0.01 sec)Now, grant the necessary privileges to app_user. For this example, we will grant SELECT and INSERT permissions on all tables within the app_db database. This follows the principle of least privilege, giving the user only the permissions it needs.
GRANT SELECT, INSERT ON app_db.* TO 'app_user'@'localhost';Apply the changes by flushing privileges.
FLUSH PRIVILEGES;You can verify the privileges you just granted by running:
SHOW GRANTS FOR 'app_user'@'localhost';The output will show the exact permissions for app_user:
+------------------------------------------------------------------+
| Grants for app_user@localhost |
+------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `app_user`@`localhost` |
| GRANT SELECT, INSERT ON `app_db`.* TO `app_user`@`localhost` |
+------------------------------------------------------------------+
2 rows in set (0.00 sec)You have successfully created a dedicated database and user with appropriate permissions.
Application requirements can change, and you may need to adjust user permissions accordingly. In this step, you will practice modifying privileges by revoking an existing permission from app_user.
Let's assume the app_user should now only be able to read data (SELECT) but not add new data (INSERT). You need to revoke the INSERT privilege.
Use the REVOKE statement to remove the INSERT privilege from app_user on the app_db database.
REVOKE INSERT ON app_db.* FROM 'app_user'@'localhost';You will see the following confirmation:
Query OK, 0 rows affected (0.00 sec)Apply the changes by flushing privileges.
FLUSH PRIVILEGES;Now, verify that the privilege has been revoked by checking the user's grants again.
SHOW GRANTS FOR 'app_user'@'localhost';The output should now show that app_user only has the SELECT privilege on app_db. The INSERT privilege is gone.
+--------------------------------------------------------------+
| Grants for app_user@localhost |
+--------------------------------------------------------------+
| GRANT USAGE ON *.* TO `app_user`@`localhost` |
| GRANT SELECT ON `app_db`.* TO `app_user`@`localhost` |
+--------------------------------------------------------------+
2 rows in set (0.00 sec)This demonstrates how to effectively manage user permissions to adapt to changing security requirements.
By default, some MySQL installations create anonymous user accounts, which have an empty username. These accounts pose a security risk because they can allow unauthorized access. It is a critical security practice to check for and remove them if they exist.
You can identify anonymous users by querying the mysql.user table for entries with a blank username.
SELECT User, Host FROM mysql.user WHERE User = '';If anonymous users exist, this query will list them. A fresh installation on Ubuntu might include one, but newer versions of MySQL/MariaDB often don't have anonymous users by default.
If you see results like:
+------+-----------+
| User | Host |
+------+-----------+
| | localhost |
+------+-----------+
1 row in set (0.00 sec)Then you should remove the anonymous user with the DROP USER statement, specifying both the username (which is an empty string '') and the host:
DROP USER ''@'localhost';You should see this confirmation:
Query OK, 0 rows affected (0.01 sec)However, if your query returns an empty set like:
Empty set (0.00 sec)This means there are no anonymous users in your database, which is already secure. In this case, if you try to drop a non-existent anonymous user, you'll get an error:
ERROR 1396 (HY000): Operation DROP USER failed for ''@'localhost'This error is expected if no anonymous user exists, and you can safely proceed.
After checking for anonymous users and removing them if necessary, flush the privileges to ensure any changes are applied:
FLUSH PRIVILEGES;Verify again that no anonymous users remain by running the SELECT query:
SELECT User, Host FROM mysql.user WHERE User = '';This query should return an empty set, confirming that no anonymous users exist:
Empty set (0.00 sec)You have successfully checked for and addressed a potential security vulnerability from your server. You can now exit the MySQL shell.
exit;In this lab, you have learned and applied several essential MySQL security best practices. You successfully secured the root account with a password, created a dedicated application user with limited permissions based on the principle of least privilege, managed user access by revoking privileges, and checked for and addressed potential anonymous user accounts.
By implementing these fundamental security measures, you can significantly improve the security posture of your MySQL database and protect it from unauthorized access and potential threats.
