How to fix the mysqldump access denied process privilege error

How to fix the mysqldump process privilege error after applying a recent MySQL update.

You may receive a new ‘Access denied’ error when trying to dump your MySQL database:

mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces

You were able to export database before so what caused this? Here’s the answer: updates for MySQL 5.7.31 and MySQL 8.0.21 in July 2020 introduced an incompatible change:

Incompatible Change: Access to the INFORMATION_SCHEMA.FILES table now requires the PROCESS privilege.

This change affects users of the mysqldump command, which accesses tablespace information in the FILES table, and thus now requires the PROCESS privilege as well. Users who do not need to dump tablespace information can work around this requirement by invoking mysqldump with the --no-tablespaces option. (Bug #30350829)

This error appears when running mysqldump directly from the command line, exporting the database using a client like MySQL Workbench or if you’re managing the WordPress database through WP-CLI’s export command.

In my case, I encountered the problem when running a routine Python script for a Drupal to WordPress migration client. My script uses WP-CLI to export a database dump file and deploy it to a remote server.

Solutions for fixing the mysqldump process privilege error

The mysqldump command requires at least the following privilege assigned to the user:

• SELECT privilege for dumped tables
• SHOW VIEW for dumped views
• TRIGGER for dumped triggers
• LOCK TABLES if you don’t use the --single-transaction option
• PROCESS if you don’t use the --no-tablespaces option

The last PROCESS privilege is new as of MySQL 5.7.31 and MySQL 8.0.21 and may be the root source of your problem. You can solve the mysqldump process privilege error in two ways:

1. Updating the privileges for your database user.
2. Runing mysqldump with the --no-tablespaces option.

Solution 1: Update the user privileges

Granting the PROCESS privilege for the user is perhaps the simplest option for fixing the mysqldump process privilege error. Keep in mind that this option presents security issues. You should therefore really only use this option for your own local development server installation.

To grant the PROCESS privilege, log in as an administrator user and run the following query:

GRANT PROCESS ON *.* TO user@localhost;

Note that PROCESS is a global level privilege. It can’t apply to individual databases. Global privileges are either administrative or apply to all databases on your MySQL server. Trying to grant them on individual databases deplays the following error:

ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

To grant the privilege to all databases you must use the ON *.* ... syntax.

Solution 2: Use the --no-tablespaces option

If you cannot assign global level privileges to your user, for example, when doing so presents unacceptable security issues, you must specify the --no-tablespaces option when dumping your database.

mysqldump --no-tablespaces -u user -ppass dbname > db_backup_file.sql

What are MySQL tablespaces?

We are usually only concerned with logical database objects when working with databases. However, the data must be physically stored somewhere. This is where tablespaces come in. Tablespaces are physical datafiles stored in the host file system holding data for one or more tables and indexes.

The diagram below provides a handy illustration. It’s from the Oracle Concepts documentation, Introduction to Tablespaces, Datafiles, and Control Files and refers to Oracle databases. Nevertheless, it may help you understand how tablespaces relate to logical database objects and datafiles.

You can therefore use the --no-tablespaces option if you don’t need to dump tablespace information. This may be the case for routine database dumps, for example when exporting databases for WordPress migrations.

About the access mysqldump denied PROCESS privilege error

mysqldump accesses tablespace information in the FILES table. Prior to MySQL 5.7.31 and 8.0.21, your user could run mysqldump without the PROCESS privilege. However, users running mysqldump after the update need PROCESS privileges to access the INFORMATION_SCHEMA.FILES table. Running mysqldump without PROCESS privilege ends up giving you an Access denied error.

Be careful with the PROCESS privilege

According to the MySQL documentation, the PROCESS privilege controls access to information about statements being executed by sessions.

It is a server administration privilege and should not be given to all users. This is because it may show text from currently executing queries. Any user with the PROCESS privilege may therefore see queries issued by others. Here’s the danger: these queries, such as UPDATE user SET password=PASSWORD, may show secrets.

For more information, see General Security Issues and the MySQL Access Privilege System from the O’Reilly MySQL Reference Manual.