0

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?

0
  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).

  • I don't think that will work since I asked for "LOCAL" which means that the CSV file isn't directly (via the filesystem) accessible to the MySQL. In this case, the MySQL is a managed instance, like RDS on AWS. – Slawomir Aug 21 '18 at 19:52
  • 1
    Documentation claims that If LOCAL is specified, the file is read by the client program on the client host and sent to the server. [===skipped===] When using LOCAL with LOAD DATA, a copy of the file is created in the directory where the MySQL server stores temporary files. So the only difference is an additional process of copying file from client to server. And one more - documentation claims nothing about difference in DEFINER and/or SQL SECURITY DEFINER applying in case of LOAD DATA LOCAL INFILE used. – Akina Aug 22 '18 at 4:31
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.