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?

MySQL will not overwrite a file that already exists as a security precaution. It will only create NEW files. — Marc B, Sep 01 '11 at 14:11

score 12 · Answer 1 · edited May 23 '17 at 12:08

12

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.

edited May 23 '17 at 12:08

Community

1
1

answered Sep 20 '11 at 00:05

Ferrel Navia

121
2

Great answer. Should be at the top, not the other one. – Adam Nov 07 '12 at 20:00
1

but beware for by doing grant file on \*.\* you are essentially giving that user full file access to anything via the server. Make sure you trust him/her THAT much – cucu8 Nov 22 '13 at 13:23

score 1 · Answer 2 · answered Sep 01 '11 at 14:42

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

I still couldn't use the query sql_query("SELECT mail FROM users INTO OUTFILE 'test.txt'"); even when the file doens't exist.... So the solution must be to give the rights to my session/user, but how do I have to do that? — Francis Michels, Sep 01 '11 at 15:30

FILE privilege to MySQL session/user

2 Answers2

Linked