mysqlsqlpermissions

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

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.

Solution

  • 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.