什么是mysql权限管理_mysql权限体系基础解析

MySQL权限管理以“用户@主机”为单元，按全局、数据库、表、列四级分层控制，依赖mysql.user等系统表实时校验，需严格匹配作用域，误配将导致越权或拒绝访问。

MySQL权限管理不是“给个账号就能用”，而是以“用户@主机”为身份单元、按层级精细控制操作范围的安全机制。它不靠密码强弱兜底，而靠权限表（mysql.user、mysql.db等）在内存中实时校验——连不上是认证失败，连上了却报 Access denied，八成是权限没授对。

权限层级怎么分？错一层就全乱套

MySQL的权限不是扁平列表，而是严格分层的树状结构，每一级作用域不同，写错范围符号（比如该用 db.* 却写了 *.*）会导致权限过大或失效：

• .：全局权限，写进 mysql.user 表，影响整个实例（如 SHUTDOWN、CREATE USER）
• db_name.*：数据库级，存于 mysql.db，只对指定库生效（如 SELECT、DROP）
• db_name.table_name：表级，记录在 mysql.tables_priv，可精确到某张表的增删改查
• db_name.table_name(col1,col2)：列级，存在 mysql.columns_priv，例如只允许查 users.name 但不许看 users.password_hash

常见错误现象：

• 给了 GRANT SELECT ON . 却发现连不上 test 库？因为 . 是全局，但用户可能没被授权访问该库（mysql.db 中无匹配行）
• 用

  

  GRANT SELECT ON mydb.users 授了表权限，但执行 SELECT FROM users 报错？缺了 USE mydb 权限，或没显式指定库名（应写 SELECT FROM mydb.users）

用户创建和权限授予，两步不能合一步

CREATE USER 和 GRANT 是两个独立操作，MySQL 5.7.6+ 后不再支持在 CREATE USER 语句里直接带权限（旧写法 GRANT ... TO 'u'@'h' IDENTIFIED BY 'p' 已废弃）：

CREATE USER 'app_rw'@'10.20.%' IDENTIFIED BY 'P@ssw0rd2025';
GRANT SELECT, INSERT, UPDATE ON sales.* TO 'app_rw'@'10.20.%';

关键点：

• 创建用户时若不指定 host，默认是 'user'@'%'，极不安全；生产环境必须限定 IP 段或域名
• GRANT 后无需 FLUSH PRIVILEGES（MySQL 5.7+ 自动重载），但修改 mysql.user 表直写则必须刷
• 授予 ALL PRIVILEGES 要特别小心：在 . 上用等于给半个 root；在 sales.* 上用也意味着能 DROP TABLE，不是仅“读写数据”

查看和撤销权限，别信直觉，要查表

SHOW GRANTS FOR 'user'@'host' 看到的是合并后的有效权限，但实际生效可能来自多层叠加（比如全局 + 库级 + 表级）。容易误判的场景：

• 用户有 SELECT 全局权限，又在 sales 库被显式 REVOKE SELECT → 最终无权查 sales 下任何表（拒绝优先于授予）
• 执行 REVOKE INSERT ON . FROM 'u'@'h'，不会影响该用户在 sales.* 单独获得的 INSERT 权限

验证建议：

SHOW GRANTS FOR 'app_rw'@'10.20.%';
-- 再查底层权限表确认（需有 SELECT 权限）
SELECT * FROM mysql.db WHERE User='app_rw' AND Db='sales'\G

MySQL 8.0+ 的角色（ROLE）不是锦上添花，而是降低出错率的关键

手动给 20 个开发人员逐个 GRANT 相同权限，漏一个、写错一个主机名，就埋下越权或不可用隐患。角色把权限打包，再批量分配：

CREATE ROLE 'dev_readonly';
GRANT SELECT ON prod.* TO 'dev_readonly';
GRANT 'dev_readonly' TO 'dev1'@'192.168.5.%', 'dev2'@'192.168.5.%';
SET DEFAULT ROLE 'dev_readonly' TO 'dev1'@'192.168.5.%';

注意：

• 角色本身不登录，必须 SET ROLE 或 SET DEFAULT ROLE 才生效
• SHOW GRANTS 默认不显示角色权限，要加 USING role_name 才能看到组合效果
• 如果忘记激活角色，连 SELECT 都会报错 —— 这是权限没失效，只是没加载进来

权限体系真正难的不是语法，而是理解“谁在什么上下文能做什么”。user@host 是钥匙，. / db. / db.tb 是锁孔，而 mysql. 表里的每一行，都是真实生效的锁芯。漏看一行，就可能开错门。