FILE privilege to MySQL session/user

Question

When someone is registerd in my MySQL database, this function must work:

mysql_query("SELECT mail 
               FROM users 
               INTO OUTFILE 'test.txt'");

But I get the error

Access denied for user 'registerdb'@'%' (using password: YES)

So how I give the FILE writing permission to the session/user?

Answer 1

You need to delete the file outside of MySQL first, there is no function in MySQL that can delete or overwrite a file.
This is a security measure and you should be thankful for that fact.

Note that it can be tricky to figure out where exactly MySQL stores its files.

I usually use

LOAD DATA INFILE 'nonexistingfile' INTO validtable

The error that generates, gives you the full path for the current database that SELECT ... INTO OUTFILE will write to.
You can use a variety of ways to delete (or better move) the file, a cron-job comes to mind.
You could even write a custom UDF that will do the deleting for you although that is a mayor security risk and programmers who do this deserve a fate worse than something horrid.

Another option is to do

START TRANSACTION;
SELECT @today:= CURDATE();
INSERT INTO saved_mail_log (filename, whensaved, user_id) 
  VALUES (CONCAT(@today,'mailsave.csv'), @today, '1234');
SELECT mail FROM users WHERE user_id = '1234' INTO OUTFILE CONCAT(@today,'mailsave.csv');
COMMIT;    

I'm not 100% sure you can use a function to create the OUTFILE parameter, if not you will have to select that value and inject it into the query using dynamic SQL.

You can get the latest file using:

SELECT * FROM saved_mail_log WHERE user_id = '1234' ORDER BY whensaved DESC LIMIT 1

Answer 2

Chek for permissions of that user:

SHOW GRANTS FOR 'registerdb'@'%'

If there no listed FILE permission, just add it:

GRANT FILE ON . to 'registerdb'@'%'

and then:

FLUSH PRIVILEGES;

But beware for by doing granting the FILE permission on *.* you are essentially giving that user full access to any file the server.

To limit limit the location in which files can be read and written, set the secure_file_priv system to a specific directory.