Why can't make dumps of MySQL DB even though I have ALL privileges?

oskarfightingthecode

Just wondering, why I can't make dumps of database even though I have ALL PRIVILEGES granted.

+----------------------------------------------------------+
| Grants for me@%                                          |
+----------------------------------------------------------+
| GRANT USAGE ON *.* TO `me`@`%`                           |
| GRANT ALL PRIVILEGES ON `me`.* TO `me`@`%`               |
+----------------------------------------------------------+
2 rows in set (0.01 sec)

When trying

mysqldump --single-transaction --skip-add-locks --routines --triggers --column-statistics=FALSE --set-gtid-purged=OFF --user=$MYSQL_USER --password=$MYSQL_PASS --host=$MYSQL_HOST --port=$MYSQL_PORT $MYSQL_DATABASE > test.sql

it is throwing an error

mysqldump: Couldn't execute 'FLUSH TABLES': Access denied; you need (at least one of) the RELOAD or FLUSH_TABLES privilege(s) for this operation (1227)

Doesn't ALL mean ALL (therefore, it will also give RELOAD and FLUSH_TABLES privilege)?

Well, I made sure that this is the good database I'm connecting to (it is). Double checked my permissions.

Holger Just

PROCESS is a global privilege. Your me user does not have this privilege as you have only granted all privileges on your me database, which does not grant global privileges.

You can grant the global privilege with

GRANT PROCESS TO `me`@`%`

This however will grant your me user access to information about all databases and all tables on the database server, which may not be what you want.

Instead of granting this privilege, you can thus use the --no-tablespaces option on your mysqldump command. While this will cause tablespaces to not be dumped, your user does not require the global PROCESS privilege anymore.

To quote the 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.

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2023-04-20

Comments

0 comments

TOP Ranking

Article

Why can't make dumps of MySQL DB even though I have ALL privileges?

Why can't make dumps of MySQL DB even though I have ALL privileges?

pump.io port in URL

How to import an asset in swift using Bundle.main.path() in a react-native native module

Failed to listen on localhost:8000 (reason: Cannot assign requested address)

Double spacing in rmarkdown pdf

SQL Server : need add a dot before two last character

Ambiguous use of 'init' with CFStringTransform and Swift 3

Resetting Value of <input type="time"> in Firefox

Retrieve Element Tag Value XML Using Bash

How to pass data to the ng2-bs3-modal?

JWT gives JsonWebTokenError "invalid token"

How to update azerothcore-wotlk docker container

C++ 16 bit grayscale gradient image from 2D array

redirect your computer port to url

Capybara Selenium Chrome opens About Google Chrome

mysql.connector.errors.InterfaceError: 2003: Can't connect to MySQL server on '127.0.0.1:3306' (111 Connection refused)

How to make thrown errors visible outside of a Promise?

JMeter: Why get error when try to save test plan

Should you provide dependent libraries in client jar?

Issue making model pop up onPress of flatlist

Message: element not interactable on accessing a tag python

Calling Doctrine clear() with an argument is deprecated