Uhm, this is questionable. One needs FILE privilege for LOAD DATA INFILE or LOAD_FILE() function. That is, FILE privilege is normally required both for reading and writing (SELECT ... OUTFILE).

We must require FILE to read a file outside of datadir. And SELECT from a CONNECT table, is just it — reading a file outside of datadir.

On the other hand, one can argue that FILE should be only needed to create such a table, not later on.

Isn't it secure_file_priv that makes a difference for where the file is read from?
In any case, currently for CONNECT it doesn't seem to matter where the file resides, it doesn't allow reading without FILE privilege even when the file is in the same datadir/db/ folder. Otherwise I suppose it could be a viable workaround, even if not very elegant, to put the files that are needed for public tables into the datadir (or into secure_file_priv directory).

Discussed with Serg and Elena.
Summary:
1. It's fine to require FILE privilege even if the file is in secure_file_priv.

2. It would be nice to support this syntax:

GRANT FILE ON '/usr/home/xxx' TO username;

Wildcard question aren't perfectly clear here. Using % and _ would be
consistent and logical. On the other hand, using * and ? would be also
consistent and logical, right? Supporting all the four is kind of silly.

3. Changing priority to Minor, waiting for more user feedback on this topic.

For CONNECT table_type=JDBC it really should not require the FILE priv. If that isn't possible or easy, at least make it so that FILE is only needed to define the the connect JDBC table, not select/insert/update/delete from it.

Is this possible?

Many MANY years later, and this issue is still hanging open.

I ran into this issue using the CONNECT engine with the MySQL table type. I understand that most of the connect engine uses an all-or-nothing approach regardless of what data source it is using, but it does seem like a security issue needing to have the FILE permission when proxying data from an external MySQL instance, something that doesn't touch any local files whatsoever.