2017-04-10

数据库 / MySQL

14 分钟读完 (大约 2089 个字) 0次访问

170410-mysql用户管理

MySQL 用户管理完全指南

MySQL 用户管理完全指南

一、用户管理概述

MySQL 用户管理是数据库运维的基础工作，主要包括：

创建用户：添加新的数据库用户账号
授权管理：为用户分配适当的操作权限
密码维护：定期更新用户密码保障安全
用户删除：清理不再需要的用户账号

合理的用户管理能够有效保障数据库安全，避免权限滥用和数据泄露风险。

二、创建用户

2.1 基本语法

1	CREATE USER 'username'@'host' IDENTIFIED BY 'password';

参数说明：

username：用户名
host：允许登录的主机地址（localhost、%、具体 IP）
password：用户密码

2.2 示例演示

以 root 用户登录数据库后执行：

1 2	-- 创建用户 laosan，密码为 laosan CREATE USER 'laosan'@'%' IDENTIFIED BY 'laosan';

创建成功后，可以在 mysql.user 表中查看新增用户的信息：

user

三、用户授权

3.1 授权语法详解

1 2	GRANT privileges ON database.table TO 'username'@'host' IDENTIFIED BY 'password'; FLUSH PRIVILEGES;

示例：

1
2
3

-- 将 user 数据库的所有权限授予用户 laosan
GRANT ALL PRIVILEGES ON user.* TO 'laosan'@'%' IDENTIFIED BY 'laosan';
FLUSH PRIVILEGES;

授权成功后，可以在 mysql.db 表中查看新增的权限信息：

grants

3.2 权限类型说明

权限类型	说明	适用场景
`ALL PRIVILEGES`	所有权限	管理员账号
`SELECT`	读取权限	报表查询、数据分析
`INSERT`	插入权限	数据录入
`UPDATE`	更新权限	数据修改
`DELETE`	删除权限	数据清理
`CREATE`	创建权限	建表、建库
`DROP`	删除权限	删表、删库
`ALTER`	修改表结构	表结构变更
`INDEX`	索引操作	性能优化
`EXECUTE`	执行存储过程	业务逻辑调用

3.3 授权范围配置

授权范围	语法	说明
全局权限	`.`	授予服务器所有数据库的权限
数据库权限	`database.*`	授予指定数据库所有表的权限
表级权限	`database.table`	授予指定数据库中特定表的权限
列级权限	`table(column)`	授予表中特定列的权限

示例：

-- 全局权限：所有数据库
GRANT SELECT ON *.* TO 'laosan'@'%';

-- 数据库权限：user 数据库所有表
GRANT ALL PRIVILEGES ON user.* TO 'laosan'@'%';

-- 表级权限：user 数据库的 users 表
GRANT SELECT, INSERT ON user.users TO 'laosan'@'%';

3.4 主机访问控制

通过 host 参数可以精确控制用户的登录来源：

Host 值	说明	安全性
`localhost`	仅允许本地登录	⭐⭐⭐⭐⭐
`192.168.52.32`	仅允许指定 IP 登录	⭐⭐⭐⭐
`192.168.52.%`	允许指定网段登录	⭐⭐⭐
`%`	允许任意远程主机登录	⭐⭐

建议：

生产环境尽量避免使用 %，应指定具体的 IP 或网段
本地应用优先使用 localhost
远程访问建议使用 VPN + 固定 IP

3.5 查看用户权限

-- 查看指定用户的权限
SHOW GRANTS FOR 'laosan'@'%';

-- 查看所有用户
SELECT user, host FROM mysql.user;

-- 查看当前用户权限
SHOW GRANTS FOR CURRENT_USER();

showgrants

四、修改密码

4.1 修改方法

方法一：使用 UPDATE 语句（适用于 MySQL 5.7 及以下）

UPDATE mysql.user 
SET password = PASSWORD('laosan@123') 
WHERE user = 'laosan' AND host = '%';
FLUSH PRIVILEGES;

updatepasswd

方法二：使用 ALTER USER 语句（推荐，适用于 MySQL 5.7+）

1 2	ALTER USER 'laosan'@'%' IDENTIFIED BY 'laosan@123'; FLUSH PRIVILEGES;

方法三：使用 SET PASSWORD 语句

1 2	SET PASSWORD FOR 'laosan'@'%' = PASSWORD('laosan@123'); FLUSH PRIVILEGES;

4.2 注意事项

✅ 修改密码后必须执行 FLUSH PRIVILEGES 刷新权限
✅ 密码应包含大小写字母、数字和特殊字符，长度不少于 8 位
✅ 定期更换密码（建议 90 天）
❌ 避免使用简单密码或与用户名相同的密码

五、删除用户

5.1 删除命令

1	DROP USER 'laosan'@'%';

drop

5.2 影响说明

执行 DROP USER 命令后：

✅ 删除用户账号
✅ 清除用户所有权限
✅ mysql.user 表中的记录消失
✅ mysql.db 表中的权限记录消失

注意：删除用户是不可逆操作，执行前请确认该用户确实不再需要。

六、常用场景示例

6.1 Web 应用用户创建

适用于 Web 应用连接数据库的场景：

-- 1. 创建用户
CREATE USER 'webapp'@'192.168.1.%' IDENTIFIED BY 'WebApp@2024!';

-- 2. 授予指定数据库的全部权限
GRANT ALL PRIVILEGES ON myapp_db.* TO 'webapp'@'192.168.1.%';

-- 3. 刷新权限
FLUSH PRIVILEGES;

6.2 本地登录用户

如果用户需要从本机登录，可以额外赋予 localhost 权限：

1
2
3

-- 授予 localhost 权限
GRANT ALL PRIVILEGES ON zhangsanDb.* TO 'zhangsan'@'localhost' IDENTIFIED BY 'zhangsan';
FLUSH PRIVILEGES;

6.3 只读用户创建

适用于报表查询、数据分析等只读场景：

-- 创建只读用户
CREATE USER 'readonly'@'192.168.1.%' IDENTIFIED BY 'ReadOnly@2024!';

-- 仅授予 SELECT 权限
GRANT SELECT ON myapp_db.* TO 'readonly'@'192.168.1.%';
FLUSH PRIVILEGES;

七、最佳实践

7.1 安全建议

✅ 推荐做法：

最小权限原则：只授予用户完成工作所需的最小权限
限制登录来源：尽量指定具体的 IP 或网段，避免使用 %
强密码策略：密码长度 ≥ 12 位，包含大小写 + 数字 + 特殊字符
定期审计：定期检查用户列表和权限分配情况
及时清理：离职员工或废弃应用的账号应及时删除
分离权限：开发、测试、生产环境使用不同的数据库账号

❌ 避免做法：

❌ 不要给应用账号授予 SUPER、FILE 等高危权限
❌ 不要在代码中硬编码数据库密码
❌ 不要多个应用共用同一个数据库账号
❌ 不要长期使用 root 账号进行日常操作

7.2 常见问题

Q1: 授权后权限不生效？

解决方案：执行 FLUSH PRIVILEGES; 刷新权限缓存

Q2: 如何撤销已授予的权限？

1 2	REVOKE ALL PRIVILEGES ON user.* FROM 'laosan'@'%'; FLUSH PRIVILEGES;

Q3: 忘记 root 密码怎么办？

解决方案：

停止 MySQL 服务
使用 --skip-grant-tables 参数启动
免密码登录后重置 root 密码
重启 MySQL 服务

Q4: 如何查看某个用户有哪些权限？

1	SHOW GRANTS FOR 'username'@'host';

参考资料

# MySQL