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.