Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

LOAD DATA INFILE fails with 'Access denied' for a normal MySQL user

ErrorERROR 1045 (28000): Access denied for user

Short answer

A server-side LOAD DATA INFILE needs the global FILE privilege on top of the usual table rights. An account that can INSERT into the table is still refused. Grant FILE to the account you really connect as, or read the file from the client with LOCAL, which does not need FILE but must be enabled on both sides.

  • Applies to MySQL 8.4, MySQL 8.0
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    See exactly who the server thinks you are and what that account may do.

    SELECT CURRENT_USER();
    SHOW GRANTS;
  2. 2Match what you see, then jump to its fix

    1. 1SHOW GRANTS has no FILE, and the statement uses LOAD DATA INFILE without LOCALThe account has no FILE privilegeFix
    2. 2FILE is granted to 'user'@'localhost' but CURRENT_USER() shows another host partThe grant went to a different account than the one you connect asFix
    3. 3You are loading from a laptop to a remote server and cannot or should not give out FILEThe file is on your machine, not on the serverFix

What does the error look like?

  • ERROR 1045 (28000): Access denied for user
  • LOAD DATA INFILE works as root but not as the application user

Cause 1 of 3

The account has no FILE privilege

1Check

For a load that is not LOCAL, the server itself reads the file, so the manual requires the FILE privilege. It is an administrative privilege that can only be granted globally.

SHOW GRANTS;

2Fix

Grant FILE globally, as an administrator, to the account that runs the load. Grant it only to accounts that really need it: it allows reading any file the server can read.

GRANT FILE ON *.* TO 'loader'@'localhost';

3Verify

Reconnect as that account and run SHOW GRANTS; FILE is listed and the load runs.

SHOW GRANTS FOR 'loader'@'localhost';

Confirmed by dev.mysql.com

Did this fix it?

Cause 2 of 3

The grant went to a different account than the one you connect as

1Check

An account is a user name plus a host. A grant for one host does not apply to a connection that matches a different account.

SELECT CURRENT_USER();
SHOW GRANTS FOR CURRENT_USER();

2Fix

Grant FILE to the exact account that CURRENT_USER() returned.

GRANT FILE ON *.* TO 'loader'@'%';

3Verify

SHOW GRANTS for that account lists FILE.

SHOW GRANTS FOR 'loader'@'%';

Confirmed by dev.mysql.com

Did this fix it?

Cause 3 of 3

The file is on your machine, not on the server

1Check

A LOCAL load reads the file on the client and sends it over the connection, so the FILE privilege is not needed. It works only if both the server and the client allow it; with local_infile disabled it returns an error.

SHOW GLOBAL VARIABLES LIKE 'local_infile';

2Fix

Use LOCAL, and enable local_infile on the server and in the client if it is off.

LOAD DATA LOCAL INFILE '/path/on/client/data.csv' INTO TABLE t1 FIELDS TERMINATED BY ',' IGNORE 1 LINES;

3Verify

The statement reports the number of rows loaded.

SELECT COUNT(*) FROM t1;

Confirmed by dev.mysql.com

Did this fix it?

How do I stop it coming back?

  • Give FILE only to a dedicated import account, not to the application user.
  • Prefer LOCAL loads for files that live on the client, and keep local_infile off elsewhere.
  • Keep secure_file_priv pointed at one import directory.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Projects of mine built on the same technologies:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.