MySQL global configuration--secure-file-priv

MySQL global configuration --secure-file-priv

A question

When exporting the INFORMATION_SCHEMA.OPTIMIZER_TRACE to a local file:

SELECT TRACE INTO DUMPFILE "optimizer_trace.txt" FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

The error message is as follows:

(1290, 'The MySQL server is running with the --secure-file-priv option so it cannot execute this statement')

Cause Analysis

View the system variable secure_file_priv :

SHOW VARIABLES LIKE "secure_file_priv";

+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_file_priv | NULL  |
+------------------+-------+

MySQL has restrictions on the imported and exported directories, and only the specified directories can be imported and exported.

The variable value here is NULL , that is, no directory allowed to operate is set, so it cannot be exported to a file.

Knowledge interpretation

noun	meaning
Command-Line Format	--secure-file-priv=dir_name
System Variable	secure_file_priv
Scope	Global
Dynamic	No
Type	String
Default Value	platform specific
Valid Values	empty string、dirname、NULL

secure_file_priv variable is used to limit the import and export data directories, such as the LOAD DATA and SELECT ... INTO OUTFILE statements, and the LOAD_FILE() function. These operations restrict which users have file operation permissions.

secure_file_priv Some setting options:

• If it is empty, there is no directory restriction, that is, any directory is fine.
• If a directory is specified, MySQL will restrict only importing from or exporting to that directory. The directory must already exist, MySQL will not automatically create the directory.
• If set to NULL , MySQL server prohibits import and export functions.

The default value of this variable is determined by the CMake option at compile time. For details, please refer to the official document .

When the MySQL server starts, it will check the secure_file_priv variable. If the value is not safe, it will write a WARNING level log in the error log. The following situations are considered unsafe settings:

• Value is empty
• The value is --datadir directory or its subdirectories
• A directory that all users have access to

For example, when I set it to empty, there are more entries in the error log:

[Warning] Insecure configuration for --secure-file-priv: Current value does not restrict location of generated files. Consider setting it to a valid, non-empty path.

If the configured directory does not exist, the MySQL server will also write an error log. E.g:

[ERROR] Failed to access directory for --secure-file-priv. Please make sure that directory exists and is accessible by MySQL Server. Supplied value : /path/not/exist

solution

• If the variable value is NULL , you can only modify the server configuration:

  Modify the my.cnf file, under the [mysqld] block, add it if there is no secure_file_priv

  Designated directory: secure_file_priv=/path/to/data

  Unlimited catalog: secure_file_priv=

  Operation prohibited: secure_file_priv=NULL

• If the variable value is a specific directory, you can consider exporting to or from the specified directory.
• If the variable value is empty, any directory can be used.

Reference

• MySQL 5.7 Reference Manual: Server System Variables

---

Thank you for reading, think the content is good, please like it 😆
Original address: https://shockerli.net/post/mysql-secure-file-priv/