Roman AgabekovDatabases 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.
MySQL Security Check Categories
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:
Authentication and Access Control
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
User and Privilege Management
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
Configuration and Maintenance
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 and Connection Security
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
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.
1. Skip Grant Tables
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.
How to Check if skip-grant-tables is Enabled
To determine if your MySQL server is running with this risky setup, use the following command:
SHOW VARIABLES LIKE 'skip_grant_tables';
Interpreting the Results
- PASSED – If skip-grant-tables value is OFF/Empty.
Recommended Solution
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.
2. Strict SQL Mode
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.
How to Check if STRICT_ALL_TABLES is Enabled
To determine whether your MySQL server is running in Strict SQL Mode, you can examine the current configuration with this SQL command:
SHOW VARIABLES LIKE 'sql_mode';
Interpreting the Results
- PASSED – If STRICT_ALL_TABLES is found within sql_mode.
Recommended Solution
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:
sql_mode=STRICT_ALL_TABLES
Or, you can set this mode dynamically using:
SET GLOBAL sql_mode='STRICT_ALL_TABLES';
3. Automatically Creating a User
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.
How to Check if NO_AUTO_CREATE_USER is Enabled
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:
SHOW VARIABLES LIKE 'sql_mode';
Look for NO_AUTO_CREATE_USER in the output. The presence of this setting will indicate whether the security measure is actively enforced.
Interpreting the Results
- PASSED – If NO_AUTO_CREATE_USER is found within sql_mode.
Recommended Solution
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:
SET GLOBAL sql_mode=(SELECT CONCAT(@@sql_mode,',NO_AUTO_CREATE_USER'));
Now, any attempt to create a user via the GRANT statement without explicit authentication details will be blocked.
4. Symbolic Link Support
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.
How to Check if have_symlink is Enabled
To check if your MySQL server allows symbolic links:
SHOW VARIABLES LIKE 'have_symlink';
Interpreting the Results
- PASSED – If the value of have_symlink is NO.
Recommended Solution
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:
SET GLOBAL have_symlink = 'NO';
5. Test Database Existence
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.
How to Check if the Test Database Exists
To determine whether the 'test' database still exists on your MySQL server, run the following SQL command:
SHOW DATABASES LIKE 'test';
Interpreting the Results
PASSED – If the value is NO for a test database.
Recommended Solution
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;
6. MySQL Bind Address
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.
How to Check the Bind Address Configuration
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:
SHOW VARIABLES LIKE 'bind_address';
This command will display the IP address or addresses on which MySQL is set to listen for incoming connections.
Interpreting the Results
- FAILED - if value is '0.0.0.0' or '::'
- PASSED - otherwise or If the result shows a specific IP address or 127.0.0.1 (localhost) or empty.
Recommended Solution
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.
7. Password Complexity
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.
How to Check Password Validation Plugin Configuration
To check if your MySQL server has the password validation plugin enabled and properly configured, you can use the following SQL command:
SHOW VARIABLES LIKE 'validate_password%';
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.
Interpreting the Results
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
Recommended Solution
To maximize security, it's recommended that the password validation plugin be enabled and configured with strong settings:
- 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';
- 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
How Releem Helps with Security Checks
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.
