How to disable OUTFILE and INFILE?

Question

From what I understand having OUTFILE and INFILE enabled is very dangerous. Anything that allows other people to write and read files from the system is dangerous to me and since my server is hosting a public website. I know that the best solution is to prevent SQL Injection with my code, but human can make mistake sometimes. So, I want to restrict user that is used in my PHP to very limited access. If thing goes wrong, the damage will be minimized.

How to disable OUTFILE and INFILE?

Answer 1

The user permissions for FILE relate to LOAD DATA/INFILE/INTO OUTFILE type operations:

"The FILE privilege gives you permission to read and write files on the server host using the LOAD DATA INFILE and SELECT ... INTO OUTFILE statements and the LOAD_FILE() function. A user who has the FILE privilege can read any file on the server host that is either world-readable or readable by the MySQL server....

Using REVOKE to control FILE privs, from mysql CLI:

#change to mysql system db
use mysql; 
#use the REVOKE (opposite of GRANT) to disable any FILE operations 
#in all dbs for the specific user/host. use % to block all hosts.
REVOKE FILE on *.* FROM 'specificuser'@'specifichostname';

Alternatively, if you run into issues, you can selectively GRANT the FILE operations on specific databases and within them, specific tables.

Further, if not disabling completely, you can further tune control allow on a dir like /tmp to be used, limiting traversal of datafiles with the sysvar_secure_file_priv system variable.

For more info specific to these see the manual: FILE Privilege REVOKE Syntax System Variable: secure_file_priv

This question is a great example of how it is best to think of security from the "Deny Everything, Specifically Allow as needed" mindset. As opposed to first granting a user ALL rights and then selectively revoking them, which I see very often due to a lack of familiarity with the GRANT/REVOKE system in mysql.