2

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?

Lèse majesté
  • 359
  • 1
  • 2
  • 11

2 Answers2

4

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.

Rook
  • 2,615
  • 5
  • 26
  • 34
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.

Nikolay Chuprina
  • 151
  • 3