LOAD DATA INFILE and user access

Question

I am trying insert data from file into mysql table, I am use for this LOAD DATA INFILE, this is my php code

sql = " HERE LOAD DATA INFILE QUERY ...";
$sth = $db->prepare( $sql ); 
$sth->execute();

var_dump( $sth->errorInfo() );

Data is not loaded and errorInfo() shows that Access denied for user 'blabla'@'localhost' (using password: YES)

But this user, which I connect to DB, has ALL PRIVILEGES.

Why this happened? Where I am wrong?

Answer 1

As Stephan's answer rightly states, you need to grant the FILE privilege to the user who wants to load data from external files. This privilege needs to be granted by root or a user with appropriate grant privileges.

Note, that the FILE privilege is a global privilege. See https://mariadb.com/kb/en/grant/

When you use the command grant all privileges on <dbname>.* to <user>@<host>; you are granting database privileges. Hence the file privilege will not be included in the grant statement.

After granting privileges, whether global or database or any level, it is always a good idea to execute command flush privileges; to ensure the changes are applied.

IMPORTANT: there is also a difference between the commands load data infile and load data local infile. As stated in the MariaDB documentation:

When you execute the LOAD DATA INFILE statement, MariaDB Server attempts to read the input file from its own file system. In contrast, when you execute the LOAD DATA LOCAL INFILE statement, the client attempts to read the input file from its file system, and it sends the contents of the input file to the MariaDB Server. This allows you to load files from the client's local file system into the database.

https://mariadb.com/kb/en/load-data-infile/

If you are attempting to load a CSV file from your home directory you must use load data local infile.

Answer 2

Try granting rights for files :

GRANT FILE ON *.* TO user@localhost

You can read more on this here