`GRANT FILE ON` just one database

Question

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.

Answer 1

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';

Answer 2

You should grant FILE on all the tables in all databases (*.*) as shown below:

GRANT FILE ON *.* TO 'user';

Actually, the doc says below:

Because any static global privilege is considered a privilege for all databases, ...

And, you cannot give FILE privilege on only one table (e.g., person) in all databases (*.person) as shown below becuase there is error:

GRANT FILE ON *.person TO 'user';

So, we need to grant static global privilege like FILE, PROCESS, REPLICATION SLAVE etc on all the tables in all databases (*.*) as shown below, otherwise there is error:

GRANT [STATIC_GLOBAL_PRIVILEGES] ON *.* TO <USER>;

Answer 3

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 *.*.