Home / server / Questions / 153769
Accepted
Lèse majesté
Asked: 2010-06-23 11:58:58 +0800 CST

MySQL LOAD DATA INFILE user privilege settings

  • 772

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?

mysql security permissions

2 Answers

  1. Best Answer

    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.

    • 4

  2. First we need to make sure that the user has the necessary permissions:

    GRANT FILE ON *.* TO 'mysql_user'@'hostname';

    1. If you have Ubuntu, you can simply disable AppAprmor.

    sudo /etc/init.d/apparmor kill
sudo update-rc.d -f apparmor remove

    2. But this is not the best way to solve the problem. It is best to set up a AppArmor profile for mysql.

    You just need to add a line to AppAprmor profile to enable reading-writing in the required directory. For example:

    /var/www/mysite/** rw,

    And then restart AppArmor by this cmd:

    /etc/init.d/apparmor stop
/etc/init.d/apparmor start

    3. Third way to solve this issue - disabling MySQL AppArmor profile:

    sudo ln -s /etc/apparmor.d/usr.sbin.mysqld /etc/apparmor.d/disable/
sudo apparmor_parser -R /etc/apparmor.d/usr.sbin.mysqld

    This works for me on Ubuntu Server 12.04 LTS.

    • 2