MySQL 数据库授权操作汇总

发表于 2026-09-02 分类于数据库， MySQL 阅读次数：

汇总 MySQL 常见授权操作及注意事项，包含权限模型说明、典型报错分析与最佳实践。

一、连接 MySQL

mysql -u<用户名> -h<数据库IP> -P<端口> -p<密码> -A

-A 参数表示禁用自动补全（--no-auto-rehash），在数据库表较多时可加快连接速度。

二、查看用户权限

-- 查看当前用户权限
SHOW GRANTS;

-- 查看指定用户权限
SHOW GRANTS FOR 'username'@'10.%';

-- 查看所有用户（需有 mysql 库的查询权限）
SELECT user, host FROM mysql.user;

三、创建用户

-- 创建用户，允许从 10.x.x.x 网段连接
CREATE USER 'newuser'@'10.%' IDENTIFIED BY 'StrongPassword123!';

-- 创建用户，允许从任意 IP 连接
CREATE USER 'newuser'@'%' IDENTIFIED BY 'StrongPassword123!';

-- 创建用户，仅允许本机连接
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'StrongPassword123!';

四、授权操作

4.1 基本语法

GRANT <权限列表> ON <数据库>.<表> TO '<用户>'@'<主机>' [WITH GRANT OPTION];

4.2 常见授权场景

-- 授予某库所有权限
GRANT ALL PRIVILEGES ON mydb.* TO 'username'@'10.%';

-- 授予某表的只读权限
GRANT SELECT ON mydb.mytable TO 'username'@'10.%';

-- 授予某库的读写权限（不含结构变更）
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'username'@'10.%';

-- 授予全局所有权限（慎用）
GRANT ALL PRIVILEGES ON *.* TO 'username'@'10.%';

-- 授予权限并允许该用户将权限转授给其他用户
GRANT ALL PRIVILEGES ON mydb.* TO 'username'@'10.%' WITH GRANT OPTION;

4.3 刷新权限

-- 修改权限后需刷新生效
FLUSH PRIVILEGES;

通过 GRANT / REVOKE 语句操作权限时，MySQL 会自动刷新，通常无需手动执行。
但如果直接修改 mysql.user 等系统表，必须手动执行 FLUSH PRIVILEGES。

五、撤销权限

-- 撤销某库的所有权限
REVOKE ALL PRIVILEGES ON mydb.* FROM 'username'@'10.%';

-- 撤销特定权限
REVOKE INSERT, UPDATE ON mydb.* FROM 'username'@'10.%';

-- 撤销 GRANT OPTION（防止用户继续转授权限）
REVOKE GRANT OPTION ON mydb.* FROM 'username'@'10.%';

-- 撤销全局权限
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'username'@'10.%';

六、删除用户

DROP USER 'username'@'10.%';

DROP USER 会同时删除该用户的所有权限记录，无需提前 REVOKE。

七、常见报错分析

7.1 ERROR 1044：Access denied for user to database

现象：

mysql> GRANT ALL PRIVILEGES ON mydb.* TO 'targetuser'@'10.%';
ERROR 1044 (42000): Access denied for user 'username'@'10.%' to database 'mydb'

原因：

ALL PRIVILEGES 与 WITH GRANT OPTION 是两个独立的权限：

权限	含义
`ALL PRIVILEGES`	对数据库对象执行读写操作
`WITH GRANT OPTION`	可将自己拥有的权限授予其他用户

即使 SHOW GRANTS 显示 GRANT ALL PRIVILEGES ON *.*，如果末尾没有 WITH GRANT OPTION，该用户仍然无法执行 GRANT 操作。

解决方案：

-- 方案1：补充 GRANT OPTION（推荐，精确授权）
GRANT GRANT OPTION ON mydb.* TO 'username'@'10.%';

-- 方案2：全局补充 GRANT OPTION（权限更大，慎用）
GRANT ALL PRIVILEGES ON *.* TO 'username'@'10.%' WITH GRANT OPTION;

FLUSH PRIVILEGES;

7.2 ERROR 1045：Access denied for user (using password)

原因： 用户名、密码或 host 不匹配。

-- 检查用户 host 是否正确
SELECT user, host FROM mysql.user WHERE user = 'username';

-- 重置密码（MySQL 5.7）
SET PASSWORD FOR 'username'@'10.%' = PASSWORD('NewPassword!');

-- 重置密码（MySQL 8.0+）
ALTER USER 'username'@'10.%' IDENTIFIED BY 'NewPassword!';

7.3 ERROR 1142：Command denied to user

现象：

ERROR 1142 (42000): SELECT command denied to user 'username'@'...' for table 'xxx'

原因： 用户没有该表的对应操作权限。

解决方案：

-- 确认当前权限
SHOW GRANTS FOR 'username'@'10.%';

-- 补充对应权限
GRANT SELECT ON mydb.xxx TO 'username'@'10.%';

7.4 ERROR 1410：You are not allowed to create a user with GRANT

原因： 执行 GRANT 时，目标用户不存在，且当前用户没有 CREATE USER 权限。

解决方案： 先显式创建用户，再授权：

CREATE USER 'targetuser'@'10.%' IDENTIFIED BY 'Password123!';
GRANT ALL PRIVILEGES ON mydb.* TO 'targetuser'@'10.%';

八、权限范围说明

MySQL 权限作用域从大到小：

级别	语法示例	说明
全局	`ON .`	所有库所有表
库级	`ON mydb.*`	指定库的所有表
表级	`ON mydb.mytable`	指定库的指定表
列级	`GRANT SELECT(col1) ON mydb.mytable`	仅指定列

九、host 匹配规则注意事项

MySQL 中 host 支持通配符 % 和 _，但有以下细节：

-- '%' 匹配任意字符串（包括空），以下三者含义不同：
'username'@'%'       -- 任意 IP（不包含 localhost 的 socket 连接）
'username'@'10.%'    -- 10.x.x.x 网段
'username'@'localhost' -- 仅本机 socket/127.0.0.1 连接

-- 同一用户可以存在多条不同 host 的记录，MySQL 按最精确匹配优先

注意： 'username'@'%' 不能覆盖 localhost 的 socket 连接，如需同时支持本机和远程，需分别创建两条记录。

十、最小权限原则（最佳实践）

按需授权，不要直接 GRANT ALL PRIVILEGES ON *.*。
限制 host，优先使用具体网段（如 10.168.1.%）而非 %。
读写分离，只读账号只授 SELECT，写账号按需授 INSERT/UPDATE/DELETE。
不要轻易授予 WITH GRANT OPTION，持有该权限的用户可以将权限转授，存在权限扩散风险。
不要授予 SUPER 权限给普通业务账号，SUPER 权限可绕过部分安全限制。
定期审查权限，使用以下 SQL 定期检查过大权限：

-- 查看拥有全局权限的用户
SELECT user, host, Super_priv, Grant_priv
FROM mysql.user
WHERE Super_priv = 'Y' OR Grant_priv = 'Y';

十一、修改用户密码

-- MySQL 8.0+
ALTER USER 'username'@'10.%' IDENTIFIED BY 'NewPassword!';

-- MySQL 5.7
SET PASSWORD FOR 'username'@'10.%' = PASSWORD('NewPassword!');

-- 强制用户下次登录时修改密码（MySQL 8.0+）
ALTER USER 'username'@'10.%' PASSWORD EXPIRE;

十二、常用权限速查表

权限	说明
`SELECT`	查询数据
`INSERT`	插入数据
`UPDATE`	更新数据
`DELETE`	删除数据
`CREATE`	创建库 / 表
`DROP`	删除库 / 表
`INDEX`	创建 / 删除索引
`ALTER`	修改表结构
`CREATE VIEW`	创建视图
`SHOW VIEW`	查看视图定义
`CREATE ROUTINE`	创建存储过程 / 函数
`ALTER ROUTINE`	修改/删除存储过程/函数
`EXECUTE`	执行存储过程 / 函数
`TRIGGER`	创建 / 删除触发器
`EVENT`	创建/修改/删除事件
`LOCK TABLES`	锁表
`REFERENCES`	创建外键
`RELOAD`	执行 FLUSH 操作
`REPLICATION SLAVE`	从库复制权限
`SUPER`	超级管理员权限（慎授）
`GRANT OPTION`	将自身权限转授其他用户
`ALL PRIVILEGES`	以上所有权限的集合