DEV Community

yutianle
yutianle

Posted on

MySQL secure_file_priv limits paths, not the FILE privilege

MySQL secure_file_priv limits paths, not the FILE privilege

FILE is one of the most consequential privileges in MySQL. It allows a user to perform import and export operations, including LOAD DATA, SELECT ... INTO OUTFILE, and LOAD_FILE(). secure_file_priv narrows where those operations can touch the server filesystem. It does not make an account that holds FILE safe, and it does not grant or remove the privilege.

The MySQL 8.4 manual defines three states for the variable. An empty value has no effect and is explicitly described as insecure. A directory value limits import and export operations to files in that directory, which must already exist. A NULL value disables import and export operations. The server checks the setting at startup and logs a warning when a non-NULL value is empty, points at the data directory or one of its subdirectories, or points at a directory accessible by all users.

The privilege and the path are separate controls

An account with FILE can read files that the MySQL server process can read, subject to the secure_file_priv directory boundary. That makes the server's operating-system identity part of the database security model. A file that is unreadable to the database user stays unreadable through SQL. A file that is readable to the database user may become an import source if the path rules permit it.

The write side has the same shape. A directory allowed by secure_file_priv can receive exported data if the server can write there. Do not point it at the data directory, a web root, a plugin directory, a backup target, or a directory writable by an unprivileged local user. A dedicated directory with restrictive ownership and no executable content is easier to reason about.

The variable is global and not dynamic. Changing it requires a configuration change and restart, which makes drift detection important. An operator may set the value in one configuration file while another file supplies a later override. Check the effective value and the startup log, not just the intended template.

Review grants before paths

Start with the accounts that hold FILE. Routine application accounts should not need it. A migration or reporting account may need a narrow import path, but it should be a separate identity with an expiration date, a controlled source directory, and logging. Avoid granting FILE to a shared administrator account that many people use.

Check for indirect paths to elevated file operations. An account that can create or modify stored programs with DEFINER privileges, or an account with broad SUPER-style authority, may be able to make another module perform work. The exact privilege set depends on the MySQL version and deployment. Review the effective grants and the program definitions rather than assuming a single system variable covers every path.

Also inspect the server's operating-system account. A database process that can read private keys, application configuration, or backup credentials has a larger blast radius even when SQL file paths are constrained. File permissions, mount options, and container volumes are part of the same boundary.

A practical validation

Record the effective secure_file_priv value and classify it as disabled, restricted, or insecure. Confirm that a restricted directory exists, is owned by the database service identity or a dedicated service group, and is not inside the data directory or a world-writable location. Verify that the directory is not mounted into a web server or a container that hosts application code.

List the accounts with FILE. For each one, capture the owner, purpose, connection path, and the operational reason for the privilege. Remove it from accounts that cannot demonstrate a current need. For the remaining accounts, test the allowed import and export path in a lab with a harmless file. Confirm that an attempt outside the directory fails and that the failure is logged.

Monitor changes to grants and to the effective startup configuration. A schema migration tool or a temporary support account can reintroduce the privilege. If the organization uses managed MySQL, verify how the provider exposes the setting and whether the value is guaranteed across failover nodes.

secure_file_priv is useful because it turns a broad filesystem capability into a smaller one. It is not a substitute for least privilege, service-account separation, filesystem permissions, or monitoring. Treat the variable and the grant list as one control package.

References

Top comments (0)