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

Question

I am trying to backup mysql using the command

mysqldump -u root -p  database_name > backup.sql

, but it is throwing an error:

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

Also, I wanted to make a backup my database with all tables seperately stored as a file. How can I do it?

mysql version = 5.7.31

Answer 1

use --no-tablespaces

see mysqldump-Documentation

mysqldump requires at least the SELECT privilege for dumped tables, SHOW VIEW for dumped views, TRIGGER for dumped triggers, LOCK TABLES if the --single-transaction option is not used, and (as of MySQL 8.0.21) PROCESS if the --no-tablespaces option is not used. Certain options might require other privileges as noted in the option descriptions.

and see documentation of param no-tablespaces

--no-tablespaces, -y

This option suppresses all CREATE LOGFILE GROUP and CREATE TABLESPACE statements in the output of mysqldump.

Answer 2

tl;dr Breaking change introduced in minor MySQL update, use --no-tablespaces option in mysqldump from now on (recommended) or add the global PROCESS privilege to the user running the command.

I experienced the same issue on some of my machines. Not all at once, not on all commands, not on all users.

Why not all at once?

Turns out this is a breaking change introduced in MySQL minor updates 5.7.31 and 8.0.21 in 2020/07.

See 5.7.31 Release Notes / 8.0.21 Release Notes

Security Notes

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)

The said issue number should be available here http://mybug.no.oracle.com/orabugs/bug.php?id=30350829. Oracles bug tracker seems to be down however. Another public issue is https://bugs.mysql.com/bug.php?id=100219.

So this issue has to happen on all machines having MySQL >= 5.7 and getting system updates eventually.

Breaking Changes in minor updates are unexpected. This is a security issue however. It's a pity that this issue and how to solve it is not communicated better. Imho a more practical solution for this breaking change would be to removed tablespaces in mysqldump by default and add a --add-tablespaces option instead.

Why not on all commands?

The said privilege PROCESS is not needed for most use cases. mysqldump however tries to access the INFORMATION_SCHEMA.FILES table, which - as said above - now requires the PROCESS privilege.

So mysqldump now needs to be started by a user with enough privileges or using the --no-tablespaces option.

See mysqldump documentation

mysqldump requires at least the SELECT privilege for dumped tables, SHOW VIEW for dumped views, TRIGGER for dumped triggers, LOCK TABLES if the --single-transaction option is not used, and (as of MySQL 5.7.31) PROCESS if the --no-tablespaces option is not used. Certain options might require other privileges as noted in the option descriptions.

See --no-tablespaces documentation

This option suppresses all CREATE LOGFILE GROUP and CREATE TABLESPACE statements in the output of mysqldump.

See process documentation

The PROCESS privilege controls access to information about threads executing within the server (that is, information about statements being executed by sessions). The PROCESS privilege also enables use of the SHOW ENGINE statement, access to the INFORMATION_SCHEMA InnoDB tables (tables with names that begin with INNODB_), and (as of MySQL 5.7.31) access to the INFORMATION_SCHEMA FILES table.

What a MySQL tablespace is why it is used is explained very well in another question on StackOverflow already.

General advice: Most users do not need the tablespace information and may therefore safely ignore it using the --no-tablespace option.

If tablespaces are configured knowingly and willingly, then the user doing the mysqldumps should get sufficient privileges.

Why not on all users?

The MySQL root user is not affected of course. Other users may or may not be affected depending on the way they are given privileges.

The GRANT option adds privileges on a global level, or a single database, a single table, or even columns or routines. Some privileges, like the said PROCESS privilege, need to be added on a global level.

See grant documentation

The following table summarizes the permissible privilege types that can be specified for the GRANT and REVOKE statements, and the levels at which each privilege can be granted.

| Privilege | Meaning and Grantable Levels |

| PROCESS | Enable the user to see all processes with SHOW PROCESSLIST. Level: Global. |

So the sql command GRANT ALL PRIVILEGES ON somedatabase.* TO someuser@localhost; will add all available privileges to the user on one database only. Global privileges are added using ON *.*.

In my case most databases users had privileges for selected databases only. Check the users privileges with SHOW GRANTS for someuser@localhost;.

To add the global privilege use the SQL command GRANT PROCESS ON *.* TO someuser@localhost;. Don't do this unless you know about the consequences of this privilege. To the revoke the privilege use REVOKE PROCESS ON *.* FROM someuser@localhost;.

Answer 3

Resolve by adding:

--column-statistics=0 --no-tablespaces

to your dump command. I had to add the column-statistics also as just no-tablespaces was not working.

Answer 4

I got the same error below:

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

Also, I got the error below:

mysqldump: Got error: 1044: Access denied for user 'john'@'localhost' to database 'apple' when selecting the database

When I tried to export apple database to backup.sql with the user john as shown below:

mysqldump -u john -p apple > backup.sql

So, I gave PROCESS privilege on all the tables in all databases (*.*) to the user john with GRANT by login with the user root as shown below:

GRANT PROCESS ON *.* TO 'john'@'localhost';

Also, I gave all privileges on all the tables in apple database (apple.*) to the user john with GRANT by login with the user root as shown below, then I could solve these errors:

GRANT ALL ON apple.* TO 'john'@'localhost';

Or:

GRANT ALL PRIVILEGES ON apple.* TO 'john'@'localhost';

In addition, you could solve these errors by giving all privileges on all the tables in all database (*.*) to the user john with GRANT by login with the user root as shown below:

GRANT ALL ON *.* TO 'john'@'localhost';

Or:

GRANT ALL PRIVILEGES ON *.* TO 'john'@'localhost';

Answer 5

If this is failing for you in Workbench, try to use the command line instead.

First find the path of your mysqldump tool

find / -name "*mysqldump*"
# probably /usr/bin/mysqldump 

Then the following command (Replace DB_HOST, DB_PORT and DB_USER).You can then export the password:

export MYSQL_PWD=DB_PASSWORD

And then run this command

/usr/bin/mysqldump \
--host=DB_HOST \
--port=DB_PORT \
--default-character-set=utf8 \
--user=DB_USER \
--protocol=tcp \
--single-transaction=TRUE > mydump.sql