This article focuses on the auditing and security of your MySQL database and provides nine tips to harden its security.
1. Avoid Unnecessary Privilege Grants
MySQL allows several privilege statements which when assigned unnecessarily to an underprivileged user can lead to reading/writing files and subverting other user privileges. Some of the most potentially risky privilege statements are FILE, GRANT, SUPER, ALTER, DELETE, SHUTDOWN, and so on. You can read more about these from MySQL official documentation. Hence, do not assign superuser privileges like FILE, GRANT, SUPER, and PROCESS to non-administrative accounts. You can revoke these unnecessary global, database, and table-level permissions as follows:
2. Restrict Remote Logins
Remote access eases the job of database administrators, but it risks the server to potential vulnerabilities and exploits. You can disable remote access for all types of MySQl user accounts by adding a skip-networking variable to the main configuration file and restarting the service.
Similarly, you must disable root account access, if not all to restrict root account remote logins. This precaution prevents bruteforcing the MySQL root account.
3. Disable Functions (load_file, outfile, dumpfile)
Another precaution to secure MySQL against local file injection is to disable functions accessible only via the FILE grant privilege. The FILE is an option that enables low-privilege users with global command options to read or write files on the server.
load_file
The load_file function loads the file content from the server as a string. For instance, the following command will load all content from the /etc/passwd file as follows:
outfile
Similarly, the outfile function writes content to the local server files. Attackers can use this function to write a payload to the file in the server, as follows:
Output:
dumpfile
This function uses the select cause to write to the file without returning output to the screen.
Output:
You can disable these functions by revoking the FILE privilege as follows:
4. Disable Default Port
We know that MySQL services run on port 3306, and attackers scan the ports to check services running on the network. To add security by obscurity and changes the default MySQL port by editing the port system variable in its main configuration file, you’ll need to enter the following:
5. Avoid Wildcards in Account Names
Account names in MySQL consist of two parts that are a user and a hostname “user_name”@“host_name”. It enables the administrator to create accounts for the users with the same name who connect from different hosts. However, the host part of an account name permits wildcards conventions that can be a point of access to the database from anywhere.
The optional use of the hostname or IP address value is equivalent to ‘user_name’@’%’ where the % matches MySQL pattern matching LIKE operation, and % means any hostname. Meanwhile, access from the ‘192.168.132.%’ means any attempt from the class C network. Besides, anyone can access the database by naming the host part as ‘192.18.132.mysql.com’.
To avoid such attempts, MySQL allows defining a netmask with the host value to identify the network bits of an IP address:
The syntax to create a hostname is host_ip/netmask:
The above host value enables the user john to access the database from any IP within the range of 192.168.132.0-192.168.132.255. Similarly, the host values of 192.168.132.0/255.0.0.0, 192.168.132.0/255.255.0.0 will allow hosts from the class A and B networks. While 192.168.132.5 will only allow access from the specific IP.
6. Disable Explicit Access
The username in MySQL is either a name with which the databases accept incoming connections or a blank username “@“host_name” that creates an anonymous user. However, the presence of an anonymous user can leverage attackers to access the database server. Besides, MySQL versions before MySQL 5.7, create an anonymous set of users, and installation after version upgrade still adds these users.
You can note that the user and password columns are empty, and the access is limited to localhost. However, you do not want anyone to access the database. Use the following command to delete anonymous users:
7. Set Non-Root Account as an Owner or Group
Setting a non-root user account is not related to the MySQL root user. MySQL installation in Linux/Unix systems from tar and tar.gz packages allows the server to run by any underprivileged user. This is a security drawback because any user with the FILE grant option can edit or create files at the server. However, it returns an error when a user tries to access it without the -user=root error.
You can avoid this by practicing the thumb rule of accessing the database server as a separate Linux user. To run mysqld as a regular Linux user, stop the server and change read/write permissions of the MySQl server to mysql, as follows:
Open the MySQL main configuration file, add a new mysql user, and restart the service to avoid unrequired server access:
8. Set Password for Root Account
MySQL installation via an interactive shell in Debian-based Linux distributions creates the root user account and asks you to set a password. However, this does not happen in non-interactive shell installation and Red-Hat-based distributions. As stated above, a non-root user of a Linux machine can access mysql root user account by using the -user=root option. You can avoid that by setting the password as follows:
9. Ensure Data Encryption in Transit and at Rest
The default unencrypted communication between the client and the server poses a risk of data interception by any man-in-the-middle. Similarly, unencrypted user data in the database risks the user’s confidentiality and integrity. MySQL supports data encryption between the client and the server over TLS/SSL protocol, while unencrypted communication is only acceptable when both communicating parties are within the same network.
MySQL now supports encryption at rest to protect data stored in the server even when the system is breached.
MySQL Advanced Security: Protect Yourself
Ensuring that you’ve got the highest levels of online security is critical, and this article will have given you some useful pointers in the right direction. The above steps are useful for securing your database server, but learning how to assign minimum permissions to non-administrative users is also essential.
