Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

up vote 2 down vote accepted

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.

share|improve this answer
add comment

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.

share|improve this answer
    
Disabling AppArmor isn't a good idea, it's better to instruct users to go with 2. –  Giovanni Toraldo Apr 19 '13 at 18:39
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.