Understanding secure_file_priv in MySQL Security

The secure_file_priv server variable is a critical security feature in MySQL that controls the directory paths from which data can be imported or exported. This article explains the significance of secure_file_priv in protecting database servers from unauthorized file access, details its three operational states, and outlines how it prevents malicious exploits such as SQL injection and directory traversal attacks.

What is secure_file_priv?

In MySQL, users can import data from external files into tables using the LOAD DATA INFILE statement, or export query results to the server’s file system using the SELECT ... INTO OUTFILE statement.

Without restrictions, these operations pose a massive security risk. The secure_file_priv system variable mitigates this risk by limiting the effect of data import and export operations to a specific, designated directory on the server.

The Three Configuration States

The secure_file_priv variable can be configured in one of three ways, each carrying different security implications:

• NULL: When set to NULL, the server disables all import and export operations. This is the default and most secure setting for environments where file transfers are not required.
• Specific Directory Path (e.g., /var/lib/mysql-files/): When set to a specific directory path, the server restricts all LOAD DATA INFILE and SELECT ... INTO OUTFILE operations to that directory only. The directory must exist, and the MySQL server process must have the appropriate operating system-level permissions to read and write within it.
• Empty String (""): When left blank, the variable has no effect. The MySQL server is permitted to read and write files anywhere on the file system, provided the underlying operating system user running MySQL has the necessary permissions. This setting is highly discouraged in production environments.

Why secure_file_priv is Crucial for Data Security

1. Mitigation of SQL Injection Exploits

SQL injection (SQLi) is one of the most common database vulnerabilities. If an attacker successfully injects SQL commands into an application, they could attempt to read sensitive operating system files (such as /etc/passwd or configuration files) using the LOAD_FILE() function. Alternatively, they might attempt to write a malicious script, such as a PHP web shell, to the web server’s root directory using INTO OUTFILE. Properly configuring secure_file_priv prevents these actions by strictly blocking file operations outside the designated folder, even if an attacker gains administrative database access.

2. Prevention of Directory Traversal

By locking file operations to a specific directory, secure_file_priv prevents directory traversal attacks. Users cannot use relative paths (like ../../) to navigate out of the permitted directory to access or overwrite system-critical files.

3. Separation of Duties and Operating System Isolation

Restricting file exports to a dedicated directory allows system administrators to implement strict Operating System (OS) permissions. Administrators can restrict access to the export folder so that only authorized system users or automated scripts can retrieve the exported data, preventing unauthorized local users from viewing sensitive database dumps.

Checking and Configuring secure_file_priv

To check the current status of the variable on your MySQL server, execute the following SQL command:

SHOW VARIABLES LIKE 'secure_file_priv';

To modify this variable, you must edit the MySQL configuration file (my.cnf on Linux or my.ini on Windows) under the [mysqld] section:

[mysqld]
secure_file_priv="/var/lib/mysql-files"

Because secure_file_priv is a read-only variable at runtime, any changes made to the configuration file require a restart of the MySQL service to take effect.