Minimum database privileges to use load data infile

Question

I'm using mysqlimport to replace data in a table with values from a TSV file. Everything is working fine (importing data using the DB superuser name and password), so now I want to lock it down so that the database user doing the import can only affect the single table I want to replace.

I know I need to grant FILE on . in order to use "LOAD DATA INFILE" (which mysqlimport wraps), but I'm having trouble finding the minimum set of permissions I can grant on the database.

I've tried;

grant FILE on *.* to ...
grant ALL on dbname.tablename to ....

But that gives me an error when I run mysqlimport;

mysqlimport: Error: Access denied for user ...

Does anyone know if it's possible to isolate the database so that only the relevant table can be altered by this user, or do I have to allow them broader access to make the mysqlimport possible?

Tampere

You know FILE is NOT in ALL? It's a bit of a misnomer. Then again, if you get an access denied, it fails on either username, host, or password before that. – Nigrosine

possible duplicate of access denied for load data infile in MySQL – Brazell

Answer 1

Unhopefully , FILE right is not bound to a database, it is a global Mysql right that is needed to perform INFILE operations.

see http://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html#priv_file

Mercier

Answer 2

To answer my own question, I don't think it's possible to use mysqlimport to replace data in a table without using a database user that has permissions to at least drop and create any table in the database.

mysqlimport uses the name of the file you're loading to decide which table the data goes into, so it makes sense that it needs to be able to drop and recreate any table, not just the table I want it to be able to replace.

Pity.

Tampere