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?
- Create stored procedure which performs LOAD DATA INFILE.
- Specify its
DEFINERattribute as a user who have rights to import data (
root@localhost, for example).
SQL SECURITY DEFINERattribute.
- 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;
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
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.