MySQL LOAD DATA INFILE user privilege settings

Question

I understand that users must have FILE privilege on *.* in order to use the LOAD DATA INFILE command without using LOCAL—which sends the file data through the client—but when GRANTing FILE privilege to a MySQL user, how can you restrict the directories from which a user can load data from (similar to secure_file_priv, but user-specific)?

What is best-practice in this regard? To simply force users to use the LOCAL keyword and sacrifice a little performance for security, or grant FILE privilege to database users?

Answer 1

I'll start off by saying that FILE is by far the most dangerous privileges you can give to a application. FILE is much more dangerous than GRANT because in sql injection for mysql you cannot stack quires, thus you cannot turn a SELECT into a GRANT statement and there for this privilege is completely useless for sql injection. By contrast FILE privileges are commonly used by exploits to upload a backdoor.

For instance here is an example of sql injection using into outfile select name from user where id=1 union select "<?php eval($_GET[e])?>" into outfile "/var/www/backdoor.php"

If your try this query on an Ubuntu system it will fail. This is because AppArmor is denying MySQL write access to /var/www/. You could modify AppArmor's rules to deny read/write access to any folder you choose. AppArmor's configuration is pretty straight forward and you can modify it here: /etc/apparmor.d/usr.sbin.mysqld.

If you are on a distro that doesn't support AppArmor you could still use the built-in Linux file permissions, keep in mind that these file io functions are going to be run by the user account that is executing MySQL. chown user -R /some/dir && chmod 700 -R /some/dir.