MySQL Security Best Practices

What Is MySQL?

MySQL is an open source RDBMS (relational database management system). It is based on Structured Query Language (SQL) and runs on many platforms, including Windows, Linux, and UNIX. MySQL is also provided as a service on all popular public cloud platforms including Amazon, Azure, and Google Cloud.

MySQL is part of LAMP, an open source web development platform. The LAMP enterprise stack consists of Linux (operating system), Apache (web server), MySQL (RDBMS), and PHP (object-oriented scripting language), Perl, or Python.

MySQL helps support various use cases, including web applications and online publishing scenarios. It powers websites and consumer-facing and corporate web-based applications, such as Twitter, YouTube, and Facebook.

Want More Tech News? Subscribe to ComputingEdge Newsletter Today!

Database Security Threats

Databases store sensitive information, which is vulnerable to many threats—including data loss and theft, privacy violations, availability issues, and malicious or accidental modifications. These issues can occur due to various reasons, including outside attackers, malicious insiders, and accidental action.

Open source databases like MySQL typically pose security concerns related to secure communication and access controls, as well as open source security risks such as known and zero-day vulnerabilities.

Access and privileges

Excessive privileges, for example, can allow users to gain needless access to confidential information. This issue can escalate to privilege abuse, enabling authorized users to misuse their privileges to perform unauthorized actions. While you can use access control policies and query-level access control to mitigate these threats, many more issues may arise. For example, it may not prevent threat actors from escalating low-level access to high-level privileges.

Vulnerabilities and attacks

Platforms and operating systems often contain vulnerabilities, which can lead to leakage and data corruption. You can mitigate this using a patch management process alongside vulnerability assessment.

However, vulnerability assessment cannot prevent threat actors from using SQL injection to send unauthorized database queries to trick the server into revealing information. You need to use prepared SQL statements to mitigate this threat. Databases are also exposed to Denial of Service (DoS) attacks that can make resources and databases unavailable, denying users access to data or applications.

MySQL Security Best Practices

The following best practices can help you enhance the security of your MySQL database.

Modify the Port Mappings

The default mapping for MySQL is to run on port 3306. You should change this setting after installing MYSQL to conceal the ports on which your critical services run. Attackers typically target default settings first, so it is important to modify them to avoid exploits.

Avoid Running MySQL with Root Privileges

You should run MySQL under a dedicated user account that contains the minimal permissions required to run a service. Don’t let MySQL run directly as a root server. In addition to the advantages of logging and auditing, avoiding root-level privileges helps ensure that attackers cannot hijack the root user account to gain access.

Secure MySQL in the Cloud

If you run MySQL in a cloud environment, your cloud provider will likely offer security services to protect your database. For example, Azure lets you protect your open source relational database using Microsoft Defender to detect anomalous behavior that may indicate malicious attempts to access the server. AWS offers AWS Shield to help secure applications and databases from a distributed denial of service (DDoS) attack.

Disable and Delete Your MySQL History

By default, MySQL creates a history file upon installation, storing it under ~/.mysql_history. You should delete this file because it details the history of your installation and configuration. If compromised, malicious actors could use it to expose critical database user passwords. You should also create a soft link from the history file to a null device to prevent MySQL from logging events to the file.

Lock Users Accounts on Suspicious Activity

MySQL 8.0.19 introduced a temporary account locking function. You can set up MySQL to lock a user account based on variables such as the number of failed login attempts and the account lock time.

Run the following script to enable account locking when you create a user account:

CREATE USER ‘demo_user’@’localhost’ IDENTIFIED BY ‘userpassword’ FAILED_LOGIN_ATTEMPTS 4 PASSWORD_LOCK_TIME 10;

The value that follows the failed login attempts variable specifies the number of failed attempts required before locking the account. The value that follows the password lock time variable specifies the time (in days) the account will be locked. You can also specify “unbounded” as the password lock time value to keep the account locked indefinitely until it is manually unlocked.

Use Authentication Plugins

Authentication plugins enable users to select their preferred authentication method. MySQL supports numerous pluggable authentication options, which you can combine for greater security. You can use an authentication plugin alongside statements like ALTER USER or CREATE USER.

For example:

CREATE USER ‘user_7’@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘password’;

This query uses native password hashing to implement authentication.

Restrict or Disable Database Visibility

The SHOW DATABASES command allows users to view sensitive information. You should restrict the use of this command to prevent remote users and malicious actors from collecting data about your databases. You can restrict or remove this function in the MYSQL configuration file by specifying “skip-show-database” in the [mysqld] section.

Encrypt Data at Rest and in Transit

By default, MySQL uses unencrypted communication between the server and client, which provides an opportunity for attackers to intercept data via a man-in-the-middle (MitM) attack. Likewise, any user data left unencrypted in the database could compromise the user’s integrity and privacy.

You can use TLS/SSL encryption to protect MySQL communication between networks. Communications within a protected network might not require encryption. MySQL also lets you encrypt data at rest to secure your stored data in the event of a server breach.

Enforce Secure Password Policies

MySQL allows you to apply password policies, requiring non-privileged users to set a new password while entering their current password. This function can protect your database from several threats, such as attackers who have compromised the host machine and are trying to access user database sessions with a Web Shell. With password policy changes enabled, the attackers cannot lock the target user out of the compromised account unless they have the user’s existing password.

MySQL does not enable the Change Current Password policy by default. You can control this policy on an individual user basis or globally (enforcing it across all non-privileged user accounts). It is recommended to set the policy globally, or at least to cover all non-privileged users. Use the my.cnf server file to enable a global current password policy.

Conclusion

In this article, I explained the basics of MySQL security and provided several best practices that can make your database more secure:

1. Modify port mappings – avoid keeping default ports because they are known by attackers.
2. Avoid running MySQL with root privileges – this can allow the compromise of the database to lead to privilege escalation on the host machine.
3. Secure MySQL in the cloud – accidental exposure to cloud-based databases can have catastrophic consequences.
4. Disable and delete MySQL history – this prevents attackers who gain access to a database account from gleaning valuable data.
5. Lock users’ accounts on suspicious activity – use built-in MySQL features to lock accounts after too many failed login attempts.
6. Use authentication plugins – ensure your database has strong authentication.
7. Restrict or disable database visibility – disable the SHOW DATABASES command that can provide sensitive information to attackers.
8. Encrypt data at rest and in transit – encryption can prevent exposure of sensitive data even if the database is compromised.
9. Enforce secure password policies – MySQL lets you set policies to ensure that users and administrators have passwords that are not easily guessable.

I hope this will be useful as you improve the security posture of your MySQL databases.