英文:
Why can't make dumps of MySQL DB even though I have ALL privileges?
问题
只是好奇，为什么我无法制作数据库的转储，尽管我已经被授予“ALL PRIVILEGES”。
+----------------------------------------------------------+
| 对于 me@% |
+----------------------------------------------------------+
| 授予 me@% 的 USAGE 权限 |
| 授予 me@% 的 me.* 的 ALL PRIVILEGES 权限 |
+----------------------------------------------------------+
共 2 行（用时 0.01 秒）
尝试时
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
它报错
mysqldump: 无法执行 'FLUSH TABLES': 拒绝访问；您需要 (至少其中之一) RELOAD 或 FLUSH_TABLES 权限来执行此操作 (1227)
ALL 不是指 ALL 吗（因此，它也将授予 RELOAD 和 FLUSH_TABLES 权限）？
嗯，我确保这是我连接到的正确数据库（确实如此）。再次检查了我的权限。
英文:
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.
答案1
得分: 0
PROCESS 是一个全局特权。您的 me 用户没有此特权，因为您只在您的 me 数据库上授予了所有特权，这并不授予全局特权。
您可以使用以下 SQL 命令授予全局特权：
GRANT PROCESS TO `me`@`%`
然而，这将使您的 me 用户能够访问数据库服务器上所有数据库和表的信息，这可能不是您想要的。
与授予此特权不同，您可以在您的 mysqldump 命令中使用 --no-tablespaces 选项。虽然这将导致表空间不被导出，但您的用户不再需要全局的 PROCESS 特权。
引用 mysqldump 文档：
> mysqldump 至少需要对要导出的表具有 SELECT 特权，对要导出的视图具有 SHOW VIEW 特权，对要导出的触发器具有 TRIGGER 特权，如果未使用 --single-transaction 选项，则需要 LOCK TABLES 特权，以及（从 MySQL 8.0.21 开始）如果未使用 --no-tablespaces 选项则需要 PROCESS 特权。某些选项可能需要其他特权，如选项描述中所述。
英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论