MySQL Security Checks

Databases power everything from e-commerce platforms to mobile apps, making their security a top priority. Effective security measures help prevent unauthorized access and stave off data breaches, which could have serious legal repercussions and damage a company's reputation. Regular security checks are essential for upholding the integrity and safety of databases by enforcing security protocols, identifying vulnerabilities, and proactively enhancing security.

To maintain a secure and robust MySQL database environment, it’s important to use the built-in MySQL security parameters with the correct settings. Each category listed below focuses on a specific area of security to protect data integrity, prevent unauthorized access, and ensure that the database operates within the security best practices framework:

This category focuses on mechanisms that control and verify access to the database system so that only authorized users and processes can interact with the database content. When effective, this acts as the first line of defense against unauthorized access.

• Skip Grant Tables
• Strict SQL Mode

Proper management of user accounts and their privileges guarantees that only authorized users have the appropriate access levels. This category helps prevent excessive privileges that could lead to data leaks or unauthorized database changes.

• Automatically Creating User
• Validated Password Plugin

This category assesses how databases are configured and maintained, which is essential for protecting the database environment from both internal misconfigurations and external threats.

• Symbolic Link Support
• Test Database Existence

Network security settings control how the database interacts with other systems and manages incoming and outgoing network traffic. Properly configured settings prevent malicious access while ensuring that the database can efficiently process legitimate requests.

• MySQL Bind Address

Security checks can be leveraged to verify that your database adheres to top security standards. They offer a systematic approach to identifying potential vulnerabilities within your database settings, helping to fine-tune your database configuration to block unauthorized access and safeguard data integrity. By routinely conducting these assessments, you can pinpoint and mitigate security risks early.

The Skip Grant Tables (--skip-grant-tables) setting is a security feature in MySQL that controls whether the server enforces its privilege system at startup. Activating this option means the server entirely bypasses the grant tables – effectively disabling all authentication mechanisms.

This leaves the database completely open, allowing anyone with server access to connect and manipulate databases freely. While this may be useful for recovery operations, it poses a significant security risk during regular usage.

To determine if your MySQL server is running with this risky setup, use the following command:

The best practice is that the MySQL database does not start with the --skip-grant-tables option unless absolutely necessary for recovery purposes. Make sure this option is not included in your MySQL configuration files or startup scripts.

Strict SQL Mode (sql_mode) governs how the server processes invalid or missing values in data modification statements like INSERT or UPDATE. When activating this mode, MySQL actively rejects errors or improper value adjustments. This setting can prevent data corruption and avoid unexpected operational behaviors.

To determine whether your MySQL server is running in Strict SQL Mode, you can examine the current configuration with this SQL command:

PASSED – If STRICT_ALL_TABLES is found within sql_mode.

To best secure and manage your database, it's recommended to enable Strict SQL Mode for all operations. You can do this by adding STRICT_ALL_TABLES to your MySQL configuration. Update your configuration file (my.cnf or my.ini) with the following line:

Or, you can set this mode dynamically using:

In earlier versions of MySQL, particularly before 8.0.11, a critical security setting known as NO_AUTO_CREATE_USER was available. This setting falls under the SQL mode options and is key for controlling how new user accounts are created.

When enabled, it prevents the GRANT statement from automatically creating a user unless explicit authentication details, such as a password or an authentication plugin, are specified. This feature is deprecated in newer versions of MySQL, meaning it's automatically set to prevent auto-creation without requiring explicit inclusion in the SQL mode.

If you are using an older version of MySQL (<8.0.11), you can run the following command to view your current SQL mode settings:

Look for NO_AUTO_CREATE_USER in the output. The presence of this setting will indicate whether the security measure is actively enforced.

For versions of MySQL where NO_AUTO_CREATE_USER is applicable (prior to 8.0.11), it is recommended to enforce this setting. You can add NO_AUTO_CREATE_USER to the SQL mode settings in your MySQL configuration file or dynamically with the following command:

Now, any attempt to create a user via the GRANT statement without explicit authentication details will be blocked.

The Symbolic Link Support (have_symlink) setting determines whether symbolic links can be used to reference tables. Enabled through the have_symlink system variable, this feature permits tables to be symbolically linked to files or directories outside the default data directory. Although useful for specific data management and migration strategies, enabling symbolic links can increase the risk of unauthorized database access and data manipulation.

To check if your MySQL server allows symbolic links:

Disabling symbolic link support is generally advised unless it's necessary for a specific, securely managed use case. Turning off this feature helps protect your database from external manipulations that could compromise data integrity.

To disable symbolic links, update the have_symlink setting to NO, either within your MySQL session or permanently in your configuration file. For example, to disable dynamically:

When setting up MySQL, particularly on production servers, it's essential to double-check for the presence of the default 'test' database. This database comes standard with every MySQL installation and is open to everyone, including anonymous users, which is a major security red flag. It shouldn’t be left accessible.

To determine whether the 'test' database still exists on your MySQL server, run the following SQL command:

PASSED – If the value is NO for a test database.

The safest approach is to remove the 'test' database unless it serves a specific, harmless purpose in a secure environment. Executing this command deletes the 'test' database:

DROP DATABASE test;

The ‘MySQL is bound all interface’ setting, officially known as the bind_address configuration, determines how MySQL handles incoming TCP/IP connections. By default, this setting may allow MySQL to accept connections on all IPv4 interfaces available to the server, which can expose your database to potential external threats.

To check how your MySQL server is configured in terms of network accessibility, you can inspect the current bind_address setting with the following SQL command:

This command will display the IP address or addresses on which MySQL is set to listen for incoming connections.

Configure the bind_address to either a specific IP address that requires remote access or 127.0.0.1 if only local access is needed. This change restricts server access to intended users and reduces the risk of attacks. Update your MySQL configuration file (my.cnf or my.ini) with the appropriate line:

bind_address = 192.168.1.100  # Example specific IP address

OR

bind_address = 127.0.0.1  # Restrict to localhost

To apply these changes, restart your MySQL server. Adjusting the bind_address limits your database's exposure to the Internet and is a critical step in securing your MySQL environment against unauthorized network access.

Password validation is key to verifying that all database passwords meet specific security standards, which significantly enhances the system's defense against brute-force attacks. This setting is managed through the password validation plugin, which enforces rules related to password complexity, such as length, character variety, and case sensitivity.

To check if your MySQL server has the password validation plugin enabled and properly configured, you can use the following SQL command:

This command will display the current configuration of the password validation settings, allowing you to verify if the plugin is active and configured according to security best practices.

The configuration of the password validation plugin is indicated by several values:
For MySQL PASSED:

• validate_password_length ≥ 14
• validate_password_check_user_name = ON
• validate_password_policy = STRONG

For MariaDB PASSED:

• simple_password_check_minimal_length ≥ 14
• simple_password_check_digits ≥ 1
• simple_password_check_letters_same_case ≥ 1
• simple_password_check_other_characters ≥ 1

To maximize security, it's recommended that the password validation plugin be enabled and configured with strong settings:

1. Enable the Plugin – Check that the plugin is activated by including it in your MySQL server's configuration:

INSTALL PLUGIN validate_password SONAME 'validate_password.so';

1. Configure Password Policies – Set the desired password validation policies by adding the following lines to your MySQL configuration file (my.cnf or my.ini):

validate_password.policy=STRONG
validate_password.length=14
validate_password.mixed_case_count=1
validate_password.number_count=1
validate_password.special_char_count=1
validate_password.check_user_name=ON

These settings require passwords to be at least 14 characters long, include mixed-case letters, numbers, and special characters, and prevent the use of the username within the password. By implementing these security measures, you ensure that all passwords used in your MySQL environment are strong.

For additional details on setting up and configuring the password validation plugin, you can refer to the MySQL documentation:

• MySQL 8.0 Reference Manual - Validate Password Plugin

And for MariaDB users:

• MariaDB Knowledge Base - Simple Password Check Plugin

Releem is now conducting these security checks across all managed servers to strengthen your MySQL database security! By consistently assessing elements like database configurations, user privileges, and network settings, Releem verifies that your server has not incorrectly enabled or disabled any settings –unintentionally leaving a backdoor open.