41

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.

John
  • 32,403
  • 80
  • 251
  • 422

2 Answers2

68

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';
Ben Lee
  • 52,489
  • 13
  • 125
  • 145
  • Where do you use the GRANT FILE query syntax ive not seen any example code on SO with it being used to explain how its done, as i need to grant file for outfile use. Then i need to deny file after outfile is finished. – Sir Dec 23 '13 at 01:30
  • 1
    To clarify, it makes _logical_ sense to wish to restrict the FILE privilege to one database. But that would give a false sense of security. Only the most trusted users should get it. – Bob Stein Apr 18 '17 at 23:23
0

With the FILE privilege, you give a user access to the filesystem that the mysql server can access. Because of this, it's useless to limit it to a specific database, as the user will be able to access all databases on filesystem level. That's why, it can only be set on *.*.

Lena Schimmel
  • 7,203
  • 5
  • 43
  • 58
Michiel van Baak
  • 126
  • 9