GreatSQL备份报错"PROCESS权限不足"分析与解决
报错案例
某项目在执行备份时，运行 mysqldump 时报错：
$ /kingdee/greatdb/greatdb/bin/mysqldump -h inner-test.cndrealty.com -P 3307 -u portal_admin -p'xxxx' --set-gtid-purged=OFF cndrealty_mas_console > cndrealty_mas_console-20241224.BAK.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces
然而检查用户权限时却显示该账号拥有完整的数据库权限：
greatsql> SHOW GRANTS for 'portal_admin'@'%';
Grants for portal_admin@%
GRANT USAGE ON . TO portal_admin@%
GRANT ALL PRIVILEGES ON 3gol.* TO portal_admin@%
GRANT ALL PRIVILEGES ON kdrive.* TO portal_admin@%
GRANT ALL PRIVILEGES ON nacos.* TO portal_admin@%
GRANT ALL PRIVILEGES ON opensys.* TO portal_admin@%
GRANT ALL PRIVILEGES ON cndreality_mas_console.* TO portal_admin@%
GRANT ALL PRIVILEGES ON portal_db.* TO portal_admin@%
看似权限足够，为什么备份仍然失败？
一、深度解析 mysqldump 权限机制
mysqldump备份数据库时，实际上涉及多种权限，不只是简单的 SELECT：
- 基础权限：
SELECT（读取表数据）SHOW VIEW（导出视图定义）TRIGGER（导出触发器）
- 高级选项依赖权限：
--single-transaction模式下，一般不锁表，但在某些情况下仍可能需要LOCK TABLES权限。- 默认情况下（未使用
--no-tablespaces****）需要PROCESS权限，这是本次导致故障的关键权限。 - 对于 GreatSQL开启 GTID 的环境，还需要
RELOAD或FLUSH_TABLES权限。
注意：
PROCESS是一个全局权限，必须在全局级别授予，库级权限无法替代。
二、权限测试
测试准备
-- 创建测试用户
greatsql> CREATE USER 'testuser'@'%' IDENTIFIED BY 'TestPass123!';
-- 创建测试数据库和表
greatsql> CREATE DATABASE testdb;
greatsql> USE testdb;
greatsql> CREATE TABLE t1 (id INT PRIMARY KEY, name VARCHAR(20));
greatsql> INSERT INTO t1 VALUES (1, 'Alice'), (2, 'Bob');
场景1：全局授权
greatsql> GRANT ALL on *.* to testuser;
Query OK, 0 rows affected (0.01 sec)
greatsql> SHOW GRANTS for testuser\G
*************************** 1. row ***************************
Grants for testuser@%: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `testuser`@`%`
*************************** 2. row ***************************
Grants for testuser@%: GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ABORT_EXEMPT,AUDIT_ADMIN,AUTHENTICATION_POLICY_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FIREWALL_EXEMPT,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,GROUP_REPLICATION_STREAM,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PASSWORDLESS_USER_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SENSITIVE_VARIABLES_OBSERVER,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SKIP_QUERY_REWRITE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `testuser`@`%`
2 rows in set (0.00 sec)
-- 查询全局静态权限表
greatsql> SELECT * FROM mysql.user where User='testuser'\G
*************************** 1. row ***************************
Host: %
User: testuser
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: N
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
Event_priv: Y
Trigger_priv: Y
Create_tablespace_priv: Y
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string: *00E247AC5F9AF26AE0194B41E1E769DEE1429A29
password_expired: N
password_last_changed: 2024-12-24 17:30:30
password_lifetime: NULL
account_locked: N
Create_role_priv: Y
Drop_role_priv: Y
Password_reuse_history: NULL
Password_reuse_time: NULL
Password_require_current: NULL
User_attributes: NULL
1 row in set (0.00 sec)
-- 全局动态权限
greatsql> SELECT * FROM global_grants where User='testuser';
+----------+------+------------------------------+-------------------+
| USER | HOST | PRIV | WITH_GRANT_OPTION |
+----------+------+------------------------------+-------------------+
| testuser | % | APPLICATION_PASSWORD_ADMIN | N |
| testuser | % | AUDIT_ABORT_EXEMPT | N |
| testuser | % | AUDIT_ADMIN | N |
| testuser | % | AUTHENTICATION_POLICY_ADMIN | N |
| testuser | % | BACKUP_ADMIN | N |
| testuser | % | BINLOG_ADMIN | N |
| testuser | % | BINLOG_ENCRYPTION_ADMIN | N |
| testuser | % | CLONE_ADMIN | N |
| testuser | % | CONNECTION_ADMIN | N |
| testuser | % | ENCRYPTION_KEY_ADMIN | N |
| testuser | % | FIREWALL_EXEMPT | N |
| testuser | % | FLUSH_OPTIMIZER_COSTS | N |
| testuser | % | FLUSH_STATUS | N |
| testuser | % | FLUSH_TABLES | N |
| testuser | % | FLUSH_USER_RESOURCES | N |
| testuser | % | GROUP_REPLICATION_ADMIN | N |
| testuser | % | GROUP_REPLICATION_STREAM | N |
| testuser | % | INNODB_REDO_LOG_ARCHIVE | N |
| testuser | % | INNODB_REDO_LOG_ENABLE | N |
| testuser | % | PASSWORDLESS_USER_ADMIN | N |
| testuser | % | PERSIST_RO_VARIABLES_ADMIN | N |
| testuser | % | REPLICATION_APPLIER | N |
| testuser | % | REPLICATION_SLAVE_ADMIN | N |
| testuser | % | RESOURCE_GROUP_ADMIN | N |
| testuser | % | RESOURCE_GROUP_USER | N |
| testuser | % | ROLE_ADMIN | N |
| testuser | % | SENSITIVE_VARIABLES_OBSERVER | N |
| testuser | % | SERVICE_CONNECTION_ADMIN | N |
| testuser | % | SESSION_VARIABLES_ADMIN | N |
| testuser | % | SET_USER_ID | N |
| testuser | % | SHOW_ROUTINE | N |
| testuser | % | SKIP_QUERY_REWRITE | N |
| testuser | % | SYSTEM_USER | N |
| testuser | % | SYSTEM_VARIABLES_ADMIN | N |
| testuser | % | TABLE_ENCRYPTION_ADMIN | N |
| testuser | % | XA_RECOVER_ADMIN | N |
+----------+------+------------------------------+-------------------+
36 rows in set (0.00 sec)
-- 数据库权限
greatsql> SELECT * FROM db where User='testuser'\G
Empty set (0.00 sec)
测试备份：
$ mysqldump -utestuser -pTestPass123! testdb > backup_full_global.sql
**结果：**备份成功，无报错。
场景2：库级授权
greatsql> REVOKE ALL ON *.* FROM testuser;
Query OK, 0 rows affected (0.01 sec)
greatsql> GRANT ALL ON testdb.* TO testuser;
Query OK, 0 rows affected (0.01 sec)
greatsql> SHOW GRANTS FOR testuser;
+------------------------------------------------------+
| Grants for testuser@% |
+------------------------------------------------------+
| GRANT USAGE ON *.* TO `testuser`@`%` |
| GRANT ALL PRIVILEGES ON `testdb`.* TO `testuser`@`%` |
+------------------------------------------------------+
2 rows in set (0.00 sec)
-- 查询全局权限表
greatsql> SELECT * FROM mysql.user where User='testuser'\G
*************************** 1. row ***************************
Host: %
User: testuser
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
Create_tablespace_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string: *00E247AC5F9AF26AE0194B41E1E769DEE1429A29
password_expired: N
password_last_changed: 2024-12-24 17:30:30
password_lifetime: NULL
account_locked: N
Create_role_priv: N
Drop_role_priv: N
Password_reuse_history: NULL
Password_reuse_time: NULL
Password_require_current: NULL
User_attributes: NULL
1 row in set (0.01 sec)
-- 全局动态权限
greatsql> SELECT * FROM global_grants where User='testuser';
Empty set (0.01 sec)
-- 数据库权限
greatsql> SELECT * FROM db where User='testuser'\G
*************************** 1. row ***************************
Host: %
Db: testdb
User: testuser
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Grant_priv: N
References_priv: Y
Index_priv: Y
Alter_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Execute_priv: Y
Event_priv: Y
Trigger_priv: Y
1 row in set (0.00 sec)
测试备份：
$ mysqldump -utestuser -pTestPass123! testdb > backup_db_level.sql
$ mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s)' when trying to dump tablespaces
**结果：**备份失败，缺少全局 PROCESS 权限。
场景3：补充 PROCESS 权限
greatsql>GRANT PROCESS ON *.* TO 'testuser'@'%';
FLUSH PRIVILEGES;
再次备份：
$ mysqldump -utestuser -pTestPass123! testdb > backup_with_process.sql
**结果：**备份成功。
小结
| 授权方式 | 是否含 PROCESS | 备份结果 |
|---|---|---|
| 库级权限 ALL | 否 | 失败 |
| 全局权限含PROCESS | 是 | 成功 |
| 库级权限 + 全局PROCESS | 是 | 成功 |
三、解决方案
为备份账号追加必要的全局权限：
-- 最小权限授权（推荐）
greatsql> GRANT PROCESS, RELOAD, LOCK TABLES ON *.* TO 'portal_admin'@'%';
注意：建议只赋予备份所需最小权限，避免安全风险。
四、避坑指南
权限隔离原则
- 备份账号绝不等同应用账号，建议专用备份用户，避免因备份账号权限过大导致安全隐患。
最小权限实践示例
greatsql> CREATE USER 'backup_user'@'%' IDENTIFIED BY 'StrongPass!';
greatsql> GRANT SELECT, SHOW VIEW, TRIGGER ON *.* TO 'backup_user'@'%';
greatsql> GRANT PROCESS, RELOAD, LOCK TABLES ON *.* TO 'backup_user'@'%';
技术备注
- MySQL 引入了动态权限管理（如
BACKUP_ADMIN），可以通过global_grants表查看。 - 但对于传统的备份场景，静态权限
PROCESS仍是必不可少的核心权限。 - 缺少全局
PROCESS权限，即便拥有数据库内所有权限，也会导致 mysqldump 备份失败。
219
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
