MySQL File Privilege

While preparing a set of student instructions to create a MySQL 8 (8.0.21) Windows 10 instance I found an error with LOAD command and the --secure-file_priv variable set in the my.ini file. After granting the global FILE permission to the previously provisioned student user:

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

Any attempt to run the following command failed:

LOAD DATA INFILE 'C:ProgramDataMySQLMySQL Server 8.0Uploadsavenger.csv'
INTO TABLE avenger
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY ''
LINES TERMINATED BY 'rn';

and, raise this error message:

ERROR: 1290: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

The following covers my exploration to try and fix this error without removing a designated directory for secure file uploads. While MySQL 5.7 contains the request for he server-file-priv variable, there is nothing in the MySQL System Server Variables document on how to troubleshoot the server-file-priv variable when set. Somehow, I think there should be some mention of how to resolve this error without unsetting the server-file-privy variable.

I checked and fixed all Windows 10 sharing and read-write privileges on the secure-file-priv designated directory. They Windows 10 settings allowed for global sharing and both read and write privileges, but the LOAD command failed to load the file contents from the authorized Uploads directory.

The MySQL FILE privilege is a global privilege to read and write files on the local server. MySQL 8 installation on Windows 10 sets the following directory as the target for uploading files in the my.ini file:

# Secure File Priv.
secure-file-priv="C:/ProgramData/MySQL/MySQL Server 8.0/Uploads"

You can find the setting in the C:ProgramDataMySQLMySQL Server 8.0my.ini file. You can find this value without referencing the my.ini file by querying the data:

show variables like 'secure_file_priv';

A new installation should return:

+------------------+------------------------------------------------+
| Variable_name    | Value                                          |
+------------------+------------------------------------------------+
| secure_file_priv | C:ProgramDataMySQLMySQL Server 8.0Uploads |
+------------------+------------------------------------------------+
1 row in set (0.2253 sec)

You can find the my.ini file in the C:ProgramDataMySQLMySQL Server 8.0 directory of a standard Windows 10 file system.

1. Using the Windows’ File Explorer, I checked the Uploads directory’s privileges by right clicking the Uploads directory to check the Properties of the directory in the File Explorer dialog box:

2. The General tab indicates that the files are Read-only, as shown:

   

   I unchecked the Read-only checkbox. Then, I retested it with the same negative results.

3. The Sharing tab indicates that the files are Shared, as shown:

   

4. Clicking the Share … button, the files in this directory are shared with Read/Write permissions to Everyone, as shown below.

   

5. The Security tab indicates that the files Everyone has Full control of the files in this directory, as shown:

   

Unfortunately, with all these set appropriately the secure-file-priv variable appears to block reading files from the designated secure directory. It appears that I may have to simply remove the secure-file-priv setting from the my.ini file and reboot the server.

The only way that the LOAD command would work required the following steps:

1. Replace the installation default value of the secure-file-privs value in the my.ini file with the following:

   secure-file-priv=""
   

2. Put the sample avenger.csv file in the following directory:

   C:ProgramDataMySQLMySQL Server 8.0Datastudentdb
   

3. Modified the LOAD command to exclude any directory path before the comma separated value (csv) file after creating an avenger table:

   LOAD DATA INFILE 'avenger.csv'
   INTO TABLE avenger
   FIELDS TERMINATED BY ','
   ENCLOSED BY '"'
   ESCAPED BY ''
   LINES TERMINATED BY 'rn';
   

   The LOAD command worked and put the CSV file contents into the avenger table, as shown in the query results below:

   +------------+------------+------------+-------------------+
   | avenger_id | first_name | last_name  | character_name    |
   +------------+------------+------------+-------------------+
   |          1 | 'Anthony'  | 'Stark'    | 'Iron Man'        |
   |          2 | 'Thor'     | 'Odinson'  | 'God of Thunder'  |
   |          3 | 'Steven'   | 'Rogers'   | 'Captain America' |
   |          4 | 'Bruce'    | 'Banner'   | 'Hulk'            |
   |          5 | 'Clinton'  | 'Barton'   | 'Hawkeye'         |
   |          6 | 'Natasha'  | 'Romanoff' | 'Black Widow'     |
   +------------+------------+------------+-------------------+
   6 rows in set (0.0005 sec)
   

While this works, the “... MySQL Server 8.0StudentdbData” directory is where all InnoDB tables are stored. The inability to use the secure-file-privs to designate a CSV repository in a different directory appears to be a bug. It doesn’t seem appropriate to put CSV files in the core Data directory when innoDB files are stored.