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：

• 基础权限：
• 高级选项依赖权限：

注意：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

结果：备份成功。

小结

三、解决方案

为备份账号追加必要的全局权限：

-- 最小权限授权（推荐）
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 表查看。
• 但对于传统的备份场景，静态权限 <strong>PROCESS</strong> 仍是必不可少的核心权限。
• 缺少全局 PROCESS 权限，即便拥有数据库内所有权限，也会导致 mysqldump 备份失败。