I want to allow LOAD DATA command for the john mysql user. So I logged into mysql terminal as root and issued the following statement:
GRANT FILE ON johndatabase.* TO 'john'@'localhost';
But I got the following error:
ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES
If I replaced johndatabase.* with *.*, then everything works. But doesn't *.* mean all databases? I want to limit the john mysql user to just johndatabase.
You can't grant FILE privileges on just a single database. That logically doesn't make any sense. Consider what the docs say:
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. (This implies the user can read any file in any database directory, because the server can access any of those files.)
Thus, the FILE privilege is a global privilege. It affects all files on the server and allows access only to global commands (e.g. LOAD DATA INFILE, etc...), not scoped to any database. The only way to grant FILE privileges is on all databases, using this syntax:
GRANT FILE ON *.* TO 'john'@'localhost';