MySQL权限管理：GRANT与REVOKE命令详解及故障排查

授权语法与基础操作

MySQL赋予用户权限的命令格式概括如下：

GRANT 权限列表 ON 数据库对象 TO 用户名@'主机地址' IDENTIFIED BY '密码';

执行授权后，建议立即执行 FLUSH PRIVILEGES; 刷新权限缓存，使更改生效。

最典型的用例是在建立主从复制时，为从库 slave 用户授予所有权限，但限制其仅能从特定IP（如 192.168.0.2）登录：

GRANT ALL ON *.* TO slave@'192.168.0.2' IDENTIFIED BY 'funsion';
FLUSH PRIVILEGES;

常见角色权限配置场景

根据不同用户的职责，可灵活配置以下权限组合：

第1步：配置普通数据用户

目的：允许用户查询、插入、更新、删除指定库中的表数据。

操作：

GRANT SELECT, INSERT, UPDATE, DELETE ON testdb.* TO common_user@'%';
FLUSH PRIVILEGES;

第2步：配置数据库开发人员

目的：允许开发人员创建、修改、删除表结构、索引、视图及存储过程。

操作：

-- 基础结构权限
GRANT CREATE, ALTER, DROP ON testdb.* TO developer@'192.168.0.%';

-- 外键与索引操作权限
GRANT REFERENCES ON testdb.* TO developer@'192.168.0.%';
GRANT INDEX ON testdb.* TO developer@'192.168.0.%';

-- 临时表权限
GRANT CREATE TEMPORARY TABLES ON testdb.* TO developer@'192.168.0.%';

-- 视图相关权限（创建与查看源码）
GRANT CREATE VIEW ON testdb.* TO developer@'192.168.0.%';
GRANT SHOW VIEW ON testdb.* TO developer@'192.168.0.%';

-- 存储过程与函数权限
GRANT CREATE ROUTINE ON testdb.* TO developer@'192.168.0.%'; -- 允许创建存储过程
GRANT ALTER ROUTINE ON testdb.* TO developer@'192.168.0.%'; -- 允许修改/删除存储过程
GRANT EXECUTE ON testdb.* TO developer@'192.168.0.%';       -- 允许执行存储过程

FLUSH PRIVILEGES;

第3步：配置DBA与管理员

普通DBA：管理某个具体数据库的权限（可省略privileges关键字）。

GRANT ALL PRIVILEGES ON testdb TO dba@'localhost';

高级DBA：管理MySQL服务器中所有数据库的全局权限。

GRANT ALL ON *.* TO dba@'localhost';
FLUSH PRIVILEGES;

风险提醒：以下命令将开放所有IP的Root超级管理员权限，存在极大安全风险，生产环境严禁使用，仅作原理参考：

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;

权限的作用范围层级

MySQL的授权粒度可分为三个层次，精确控制用户的数据访问边界：

1. 全局层面：作用于整个MySQL服务器。

   GRANT SELECT ON *.* TO dba@'localhost';  -- 可查询所有库的所有表

2. 数据库层面：作用于指定数据库中的所有对象。

   GRANT SELECT ON testdb.* TO dba@'localhost'; -- 仅可查询testdb

3. 表/列层面：作用于具体某张表或字段。

查看与撤销权限

检查用户权限

查看当前登录用户的权限：

SHOW GRANTS;

查看其他特定用户（如dba用户）的权限：

SHOW GRANTS FOR dba@'localhost';

撤销权限(REVOKE)

撤销权限的语法与GRANT类似，只需将 TO 替换为 FROM。

-- 先授权再撤销示例
GRANT ALL ON *.* TO dba@'localhost';
REVOKE ALL ON *.* FROM dba@'localhost';

常见问题与排错指南

问题：调用存储过程时报 “SELECT command denied”

错误信息通常为：Select command denied to user 'xxx'@'...' for table 'proc'。

原因分析：在MySQL内部实现中，调用存储过程会隐式读取 mysql.proc 系统表的信息。即使你赋予了用户在业务库上的所有权限，如果未显式授予对 proc 表的SELECT权限，也会导致拒绝访问。

解决方法：需要为该用户手动授予 mysql.proc 表的SELECT权限（或在较低版本MySQL中，有时需通过特定的全局权限绕过此检查，建议升级至5.7+版本以改善体验，或使用 DROP PROCEDURE 时的权限分离机制）。

例如，尝试给予用户对proc表的只读权限：

GRANT SELECT ON mysql.proc TO username@'host';
FLUSH PRIVILEGES;

MySQL授权表的底层逻辑

了解MySQL的权限模型有助于更精准地进行权限分配。MySQL主要依赖五张授权表：

• user 表：定义连接服务器的用户及其口令，包含全局超级用户权限。
• db 表：记录用户对特定数据库的访问权限。
• host 表：结合 db 表控制特定主机对数据库的访问（较少直接使用）。
• tables_priv 表：定义表级权限。
• columns_priv 表：定义列级权限。

以上就是MySQL权限管理中GRANT与REVOKE命令的详细内容，更多关于数据库用户安全配置的资料请关注本站其它相关文章！