How to make sure your MySQL database is secured
Auditing and logging for information systems
Logs play a crucial role for security when there is a suspected cyberattack. A manual review of logs is painstaking for security personnel, and they must use log review tools to extract information and analyze it. Logs should use a WORM (write once read many) storage technology and encryption to avoid corruption and losing log data. Also, logs should have a standardized format for ease of maintenance, access and comparison.
Ensure “log_error” is not empty
command:
SHOW variables LIKE 'log_error';
Error logs contains data on events when mysqld starts or stops. It also shows when a table needs to be assessed or repaired. It must generate a “value”. The reason for enabling error logging is it helps increase the ability to detect malicious attempts against MySQL and other vital messages.
Ensure log files are stored on a non-system partition
command:
SELECT @@global.log_bin_basename;
Log files of MySQL can be stored anywhere in the filesystem and set using the MySQL configuration. Also, it is a best practice is to ensure that the logs in the filesystem are not cluttered with other logs such as application logs. You must ensure that the value returned does not indicate that it is in the root “(‘/’)”, “/var”, or “/usr”. The reason for this is that partitioning will decrease the likelihood of denial of service if the available disk space to the operating system is depleted.
Ensure “log_error_verbosity” is not set to “1”
command:
SHOW GLOBAL VARIABLES LIKE 'log_error_verbosity';
This check provides additional information to what functionalities the MySQL log has or has enabled on error messages. A value of 1 enables the logging of error messages. A value of 2 enables both the logging of error and warning messages. A value of 3 enables logging of error, warning, and note messages. This helps detect malicious behavior by logging communication errors and aborted connections.
Ensure audit logging is enabled
Enabling audit logging is crucial for production environment for interactive user sessions and application sessions. With audit logging, it helps identify who changed what and when. It can also help to identify what an attacker has done and can even be used as evidence in investigations.
command:
SELECT NAME FROM performance_schema.setup_instruments WHERE NAME LIKE ‘%/alog/%’;
show global status like 'AUDIT_version';
No audit log plugins
show plugins
General log query
SET GLOBAL general_log = 'ON' ;
CREATE USER ‘user1’@’localhost’ IDENTIFIED BY PASSWORD ‘not-so-secret’;
The log’s path in Windows 10 can be found by using Services application, looking to see if MySQL is running, and right-click properties.
The log in the author’s system was located in: C:\ProgramData\MySQL\MySQL Server 5.7\Data\DJ-JASON-CLARK.log
Authentication for information system
Authentication makes sure the credentials provided by the user or machine are matched with the database of authorized users in a local operating system or in an authentication server. Authentication is then followed by authorization, which is granted by an administrator to users or machines. An authentication that is commonly used in both private and public networks is password-based authentication.
Ensure passwords are not stored in the global configuration
The [client] section of a MySQL configuration file allows the creation of a user and password to be set. The check is important because allowing a user and password in the configuration file impacts the confidentiality of the user’s password negatively.
To audit, open MySQL configuration file and examine the [client] section — it must not have any password stored. No password was set in the author’s system (see figure below). If a password was set in the configuration file, use mysql_config_editor to store passwords in the encrypted form in .mylogin.cnf.
[client] section of MySQL configuration file
[client]
# pipe=
# socket=0.0
port=3306
Ensure ‘sql_mode’ contains ‘NO_AUTO_CREATE_USER’
The “no_auto_create_user” is an option to prevent the auto creation of user when authentication information is not provided.
SELECT @@global.sql_mode;
SELECT @@session.sql_mode;
Ensure passwords are set for all MySQL accounts
A user can create a blank password. Having a blank password is risky as anyone can just assume the user’s identity, enter the user’s loginID and connect to the server. This bypasses authentication, which is bad.
SELECT User,host FROM mysql.user WHERE authentication_string=’’;
Ensure ‘default_password_lifetime’ is less than or equal to ‘90’
Changing the password lifetime to 90 days decreases the time available for the attacker to compromise the password, and thus decreases the likelihood of getting attacked.
SHOW VARIABLES LIKE ‘default_password_lifetime’;
SET GLOBAL default_password_lifetime=90;
Ensure password complexity is in place
Password complexity adds security strength to authentications and includes adding or increasing length, case, numbers and special characters. The more complex the password, the harder for attackers to use brute force to obtain the password. Weak passwords are easily obtained in a password dictionary.
SHOW VARIABLES LIKE ‘validate_password%’;
Implement password complexity
my.ini
plugin-load=validate_password.so
validate-password=FORCE_PLUS_PERMANENT
validate_password_length=14
validate_password_mixed_case_count=1
validate_password_number_count=1
validate_password_special_char_count=1
validate_password_policy=MEDIUM
Ensure no users have wildcard hostnames
Users with wildcard hostnames (%) are granted permission to any location. It is best to avoid creating wildcard hostnames. Instead, create users and give them specific locations from which a given user may connect to and interact with the database.
Wildcard hostname
SELECT user, host FROM mysql.user WHERE host = ‘%’;
Change wildcard hostname
update mysql.user SET host='3306' WHERE user='kwikl3arn';
select user,host from mysql.user where host='3306';
Ensure no anonymous accounts exist
Users can have an anonymous (empty or blank) username. These anonymous usernames have no passwords and any other user can use that anonymous username to connect to the MySQL server. Removal of these anonymous accounts ensures only identified and trusted users can access the MySQL server.
SELECT user,host FROM mysql.user WHERE user = ‘’;
Network connection to MySQL server
The network connection plays an important role for communication between the user and the MySQL server. Insecure network connections are very vulnerable to attacks. The following are checks for network connection security.
Ensure ‘have_ssl’ is set to ‘YES’
To avoid malicious attackers peeking inside your system, it is best to use SLL/TLS for all network traffic when using untrusted networks.
show WHERE variable_name = ‘have_ssl’;
Ensure ‘ssl_type’ is set to ‘ANY’, ‘X509’, or ‘SPECIFIED’ for all remote users
SSL/TLS should be configured per user. This further prevents eavesdropping of malicious attackers.
SELECT user, host, ssl_type FROM mysql.user WHERE NOT HOST IN (‘::1’, ‘127.0.0.1’, ‘localhost’);
Replication
Checking for replication status lets you monitor performance and security vulnerabilities. Microsoft SQL Server Management Studio has the following tools to monitor replication:
- view snapshot agent status,
- view log reader agent status, and
- view synchronization status.
Ensure replication traffic is secured
Replication traffic between servers must be secured. During replication transfers, passwords could leak.
To audit, check if they’re using: a private network, a VPN, SSL/TLS or a SSH Tunnel. Hopefully the author’s system is using a private network. Correct if otherwise, and secure by using the private network, a VPN, SSL/TLS or a SSH Tunnel.
Ensure ‘MASTER_SSL_VERIFY_SERVER_CERT’ Is Set to ‘YES’ or ‘1’
‘MASTER_SSL_VERIFY_SERVER_CERT’ checks whether the replica should verify the primary's certificate or not. The replica should verify the primary's certificate to authenticate the primary before continuing the connection.
SELECT ssl_verify_server_cert FROM mysql.slave_master_info;
Ensure ‘master_info_repository’ is set to ‘TABLE’
The ‘master_info_repository’ determines where the replica logs the primary's status and connection information. The password is stored in the primary info repository that is a plain text file. Storing the password in the TABLE master_info is a safer.
SHOW GLOBAL VARIABLES LIKE ‘master_info_repository’;
Ensure ‘super_priv’ is not set to ‘Y’ for replication users
The “SUPER” privilege (‘super_priv’) located in the “mysql.user” table has functions like “CHANGE”, “MASTER TO”, “KILL”, “mysqladmin kill”, “PURGE BINARY LOGS”, “SET GLOBAL”, “mysqladmin debug”, and other logging controls. Giving a user the “SUPER” privilege allows the user to view and terminate currently executing SQL statements, even for password management. If the attacker exploits and gains the “SUPER” privilege, they can disable, alter, or destroy logging data.
SELECT user, host FROM mysql.user WHERE user='repl' and Super_priv = 'Y';
Ensure no replication users have wildcard hostnames
MySQL allows you to grant permissions to wildcard hostnames. Wildcard hostnames should be avoided, and you should create or modify users and give them specific locations from which a given user may connect to and interact with the database.
select user,host from mysql.user where user='repl' and host='%';
