Disabling Dangerous Functions

Another mandatory security measure to avoid local file SQL injection is to disable functions offered through FILE privilege to low-level users. This grant enables users with global commands like load_file, outfile, and dumpfile to read or make changes to the filesystem accessed through the server.

However, if an attacker does get access to the database through an application layer vulnerability (e.g., SQL injection), disabling the function will prevent the attacker read/write privileges on local files on the system.

load_file

The load_file function lets a user load all the data from a file accessed through the server. For instance, a user with the FILE privilege can load all the file content with the command,

mysql> select load_file('/etc/passwd');

whereas a user without a FILE grant will receive the output:

mysql> select load_file('/etc/passwd');
+--------------------------+
| load_file('/etc/passwd') |
+--------------------------+
| NULL                     |
+--------------------------+
1 row in set (0.000 sec)

outfile

The outfile function allows the user to overwrite all the files accessed through the server:

mysql> select 'Hello2' into outfile '/tmp/hello.txt';
$ cat /tmp/hello.txt
Hello2

However, the following error is received by non-privileged users:

ERROR 1045 (28000): Access denied for user 'user'@'localhost' (using password: YES)

dumpfile

The dumpfile function is a select clause that writes to the file without separators in an unformatted row. However, the resulting output does not return to the client.

$ cat /tmp/world
Hello world!
mysql> select 'Hello world!' into dumpfile '/tmp/world';
Query OK, 1 row affected (0.001 sec)

To read/write to the system files, disable these functions by revoking the client FILE privileges:

mysql> revoke FILE on *.* from 'user'@'localhost';

Once revoked, the function's command will generate an error or NULL output.

No Root Privileges

MySQL must never be run as a root user. This precaution isn't related to the MySQL root user. Running MySQL as a root user enables any account with the FILE privilege to modify and create server files as root.

Accessing MySQL as a root user generally returns an error. However, this restriction can be overridden by starting MySQL with the -user=root option. The ideal practice is to access MySQL as a separate Unix user by editing the MySQL configuration file:

$ vim /etc/mysql/my.cnf
user=mysql
$ sudo service mysql restart

Disable Remote Login, Particularly Root (Optional)

Remote root logins can expose MySQL databases to high risk. To disable remote root access, sign in to the server and run the commands:

mysql> delete from mysql.user where user='root' and host not in ('localhost', '127.0.0.1', '::1');
mysql> flush privileges;

Similarly, disallow all remote logins if not required by adding a skip-networking system variable to MySQL configuration files:

[mysqld]
port=XXXX
skip-networking
sudo service mysql restart

Enable Explicit Deny (Optional)

MySQL identifies any client connection by username, host value, and password. A host value allows the wildcard character % in the hostname or IP address, such that hostname % can enable connection from any client with a similar username over the Internet.

Similarly, the IP wildcard value 192.168.100.% allows connection from anyone on the subnet that can be easily exploited by naming the host 198.168.100.example.com . Hence, it is recommended to specify the host value as an IP address with a netmask that identifies bits to use for the network address:

mysql> create user 'user'@'192.168.100.0/255.255.255.0';

This host value enables the user to connect from any IP address within a user_ip ranging from 192.168.100.0 to 192.168.100.255, such that the following condition holds:

user_ip && netmask=host_ip

Change Default Port (Optional)

By default, the MySQL service runs on TCP port 3306. To check that port in the system, enter:

$ netstat -tanp | grep 3306

Attackers and IoT search engines normally scan the default port ranges and index them in their database. As a security precaution, open the MySQL configuration file located in the /etc/mysql folder to change the default port:

$ vim /etc/mysql/mysql.conf.d/mysql.cnf
#change port
port=XXXX
#restart service
$ sudo service mysql restart

Conclusion

In this article, I discussed advanced security tips for MySQL server protection. You can refer to the official MySQL documentation [4] for more information.