Securing LOAD DATA LOCAL

I need to periodically import significant number of rows from a CSV file. I’d like to be able to open up the least amount of access to the user (automation script) for this job.

Is it possible to limit LOAD DATA LOCAL to a specific table and user? If not, I’m thinking of creating a “staging database”, load-data-local into it then INSERT-SELECT across two databases under the same MySQL instance.

Any other suggestions?

1. Create stored procedure which performs LOAD DATA INFILE.
2. Specify its DEFINER attribute as a user who have rights to import data (root@localhost, for example).
3. Specify SQL SECURITY DEFINER attribute.
4. Grant the user to execute this procedure.

Result: when user calls this SP it is executed with the rights of its definer – it is enough to import data. Even if the user called SP have no rights to this table at all (I’m not talking about the FILE right).

You can check any additional conditions in the procedure (current username, hostname, day of week, etc.) and perform import or not dependent by them.

Filename, tablename, additional data can be transferred into SP as parameters and/or via user-defined variables (which existence and value can be additional checkpoint).

Case 1: You are replacing an entire table.

CREATE TABLE new LIKE real;
LOAD DATA ... INTO new;
clean up the data if needed
RENAME TABLE real TO old, new TO REAL;
DROP TABLE old;

The RENAME is fast and atomic; users won’t notice anything was going on.

Case 2: You need to add data to an existing table

CREATE TEMPORARY TABLE incremental LIKE real;
LOAD DATA ... INTO incremental;
clean up the data if needed
INSERT [IGNORE] INTO real SELECT ... FROM incremental;
DROP TEMPORARY TABLE incremental;

Note: Since the INSERT will be somewhat invasive, you may need to iterate through incremental, perhaps 100-1000 rows at a time, preferably in the order of real‘s PRIMARY KEY.

If you need normalization or other things, say so.

Yes, you could put this stuff in a Stored Procedure, but that’s not much better than building a script in some other language that you run when needed. The script would connect as a user with limited permissions and a password that is not broadcast.