« Previous 1 2 3 4 5
Advanced MySQL security tips (a complete guide)
Guard Duty
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.
Infos
- require_secure_transport: https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_require_secure_transport
- Privileges provided by MySQL: https://dev.mysql.com/doc/mysql-security-excerpt/8.0/en/privileges-provided.html
- Privilege-granting guidelines: https://dev.mysql.com/doc/refman/5.7/en/privileges-provided.html#privileges-provided-guidelines
- MySQL security: https://dev.mysql.com/doc/refman/8.0/en/security.html
« Previous 1 2 3 4 5
Buy this article as PDF
(incl. VAT)