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

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.