MySQL Security Checks: A Comprehensive Guide to Protect Your Database

Securing a MySQL database is critical to protect your data from breaches, unauthorized access, and potential vulnerabilities. This blog delves into essential MySQL security checks and best practices to ensure your database environment is resilient and secure. Let’s explore each topic in detail with practical examples.

In this blog post:

1. Enforcing strong password policies
2. Restricting root access
3. Removing anonymous users
4. Disabling symbolic link support
5. Configuring MySQL bind address
6. Enabling strict SQL mode
7. Disabling 'LOAD DATA LOCAL INFILE'
8. Monitoring and limiting privileges
9. Enabling SSL/TLS
10. Regularly updating MySQL

1. Enforcing strong password policies

Strong passwords are the first line of defense against brute-force attacks. MySQL provides the validate_password plugin to enforce password strength rules.

Steps to enable and configure validate_password:

(i). Install the Plugin (if not already installed):

INSTALL PLUGIN validate_password SONAME 'validate_password.so';

(ii). Set Password Rules:

SET GLOBAL validate_password.length = 12;         -- Minimum length
SET GLOBAL validate_password.mixed_case_count = 1; -- At least 1 uppercase and 1 lowercase
SET GLOBAL validate_password.number_count = 1;     -- At least 1 number
SET GLOBAL validate_password.special_char_count = 1; -- At least 1 special character

(iii). Check Current Settings:

SHOW VARIABLES LIKE 'validate_password%';

These rules prevent weak passwords, reducing the risk of unauthorized access.

2. Restricting root access

The root user in MySQL has full administrative privileges, making it a primary target for attackers. Limiting root access to specific IPs enhances security.

Restrict root login to localhost:

UPDATE mysql.user SET host = '127.0.0.1' WHERE user = 'root' AND host = '%';
FLUSH PRIVILEGES;

Disable remote root access:

(i). Edit the MySQL configuration file (my.cnf or my.ini):

   [mysqld]
   skip-networking
   bind_address = 127.0.0.1

(ii). Restart MySQL:

   sudo systemctl restart mysql

This minimizes the attack surface and protects your root account from remote intrusion.

3. Removing anonymous users

Anonymous users can log in without a username, potentially granting unintended access.

Identify and remove anonymous users:

SELECT user, host FROM mysql.user WHERE user = '';
DELETE FROM mysql.user WHERE user = '';
FLUSH PRIVILEGES;

Removing anonymous users ensures that all connections are authenticated and traceable.

4. Disabling symbolic link support

Symbolic links allow database files to point to locations outside the MySQL data directory. While useful in some cases, they can expose your database files to unauthorized access.

Disable Symbolic Links:

(i). Check Current Status:

SHOW VARIABLES LIKE 'have_symlink';

(ii). Disable in Configuration:

[mysqld]
symbolic-links = 0

(iii). Restart MySQL:

sudo systemctl restart mysql

This prevents attackers from exploiting symbolic links to access sensitive database files.

5. Configuring MySQL bind address

The bind_address parameter controls which IP addresses MySQL listens to for connections. Restricting this to trusted IPs reduces exposure to unauthorized access.

Restrict Bind Address to Localhost:

(i). Check Current Setting:

   SHOW VARIABLES LIKE 'bind_address';

(ii). Update Configuration File:

   [mysqld]
   bind_address = 127.0.0.1

(iii). Restart MySQL:

   sudo systemctl restart mysql

Restricting bind addresses limits connections to trusted sources, reducing the risk of remote attacks.

6. Enabling strict SQL mode

Strict SQL Mode enforces stricter data validation rules, ensuring that invalid data isn’t silently accepted into the database.

Enable Strict SQL Mode:

(i). Set Globally:

SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES';

(ii). Add the following to my.cnf:

[mysqld]
sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"

This prevents data integrity issues and ensures applications adhere to proper data formats.

7. Disabling 'LOAD DATA LOCAL INFILE'

The LOAD DATA LOCAL INFILE command allows importing files into MySQL. If enabled, it can be exploited to access sensitive files.

Disable This Feature:

(i). Check Current Status:

SHOW VARIABLES LIKE 'local_infile';

(ii). Disable in Configuration File:

[mysqld]
local_infile = 0

(iii). Restart MySQL:

sudo systemctl restart mysql

Disabling LOAD DATA LOCAL INFILE closes a potential attack vector for data exfiltration.

8. Monitoring and limiting privileges

Overprivileged accounts can expose your database to misuse or breaches. Auditing and limiting privileges ensures that users have only the access they require.

Audit user privileges:

SHOW GRANTS FOR 'username'@'host';

Revoke unnecessary privileges:

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'username'@'host';

Minimizing privileges reduces the impact of compromised accounts.

9. Enabling SSL/TLS

Encrypting data in transit prevents sensitive information from being intercepted by attackers.

Enable SSL/TLS:

(i). Generate certificates: Use OpenSSL or similar tools to create certificates.

(ii). Configure MySQL:

[mysqld]
ssl-ca=/path/to/ca-cert.pem
ssl-cert=/path/to/server-cert.pem
ssl-key=/path/to/server-key.pem

(iii). Verify SSL is enabled:

SHOW VARIABLES LIKE '%ssl%';

SSL/TLS secures data during transmission, safeguarding sensitive information.

10. Regularly updating MySQL

Outdated software often contains known vulnerabilities. Keeping MySQL up to date ensures you have the latest security patches.

Check version:

SELECT VERSION();

Update procedure:

(i). Backup your database:

mysqldump -u root -p --all-databases > backup.sql

(ii). Install updates using your package manager:

sudo apt update && sudo apt upgrade mysql-server

Staying current minimizes exposure to known vulnerabilities.

MySQL security is not a one-time task, it requires continuous monitoring and proactive measures. By enforcing strong passwords, limiting root access, disabling unnecessary features, and enabling secure connections, you can significantly enhance your database's security posture. Regularly review and update your configuration to stay ahead of potential threats.

Database Monitoring with Atatus

Atatus Database Monitoring simplifies real-time tracking of your database performance, offering deep insights into query execution, resource utilization, and transaction throughput for both SQL and NoSQL databases.

Atatus supports a wide range of databases, including SQL databases such as MySQL, PostgreSQL, MariaDB, and SQLite. It also supports NoSQL databases like MongoDB, Redis, Memcached, and Aerospike.

Key features of Atatus Database Monitoring:

• Query Performance Insights: Analyze SQL and NoSQL query performance with detailed metrics on execution times, latency, and throughput. Identify slow queries and drill down into their execution plans using EXPLAIN calls to uncover bottlenecks and inefficiencies.
• Active Connections Tracking: Monitor the number of active connections to your database, ensuring that connection limits are not exceeded and troubleshooting potential bottlenecks caused by excessive concurrency.
• Optimization Recommendations: Receive actionable suggestions for query and database optimization, such as adding indexes, restructuring queries, or reconfiguring database settings to improve performance.

By providing these powerful tools and insights, Atatus empowers you to optimize database performance, enhance reliability, and ensure seamless operations for your applications.

Start your 14-day free trial with Atatus today!