MySQL教程 / 第 290 节

第29章：用户与权限管理

数据库安全的第一道防线

29.1 用户管理 ⭐⭐⭐⭐⭐

29.1.1 创建用户

基本语法：

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

-- 示例：
-- 创建本地用户
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'App@123456';

-- 创建远程用户
CREATE USER 'app_user'@'192.168.1.100' IDENTIFIED BY 'App@123456';

-- 创建任意主机可访问的用户
CREATE USER 'app_user'@'%' IDENTIFIED BY 'App@123456';

-- 创建指定网段可访问的用户
CREATE USER 'app_user'@'192.168.1.%' IDENTIFIED BY 'App@123456';

29.1.2 查看用户

查看所有用户：

-- 查看用户列表
SELECT user, host FROM mysql.user;

-- 查看当前用户
SELECT USER(), CURRENT_USER();

-- 查看用户详细信息
SELECT * FROM mysql.user WHERE user = 'app_user'\G

29.1.3 修改用户

修改密码：

-- 修改自己的密码
ALTER USER USER() IDENTIFIED BY 'NewPassword@123';

-- 修改其他用户密码（需要权限）
ALTER USER 'app_user'@'localhost' IDENTIFIED BY 'NewPassword@123';

-- 使用SET PASSWORD（旧方法）
SET PASSWORD FOR 'app_user'@'localhost' = PASSWORD('NewPassword@123');

修改用户名：

RENAME USER 'old_user'@'localhost' TO 'new_user'@'localhost';

29.1.4 删除用户

删除用户：

DROP USER 'app_user'@'localhost';

-- 删除多个用户
DROP USER 'user1'@'localhost', 'user2'@'localhost';

-- 如果用户不存在不报错
DROP USER IF EXISTS 'app_user'@'localhost';

29.2 权限管理 ⭐⭐⭐⭐⭐

29.2.1 权限类型

常用权限：

ALL PRIVILEGES：所有权限
SELECT：查询权限
INSERT：插入权限
UPDATE：更新权限
DELETE：删除权限
CREATE：创建数据库/表权限
DROP：删除数据库/表权限
ALTER：修改表结构权限
INDEX：创建/删除索引权限
EXECUTE：执行存储过程/函数权限
GRANT OPTION：授权权限

29.2.2 授予权限

全局权限：

-- 授予所有权限
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost';

-- 授予特定权限
GRANT SELECT, INSERT, UPDATE ON *.* TO 'app_user'@'localhost';

数据库权限：

-- 授予数据库所有权限
GRANT ALL PRIVILEGES ON mydb.* TO 'app_user'@'localhost';

-- 授予数据库特定权限
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'app_user'@'localhost';

表权限：

-- 授予表所有权限
GRANT ALL PRIVILEGES ON mydb.users TO 'app_user'@'localhost';

-- 授予表特定权限
GRANT SELECT, INSERT ON mydb.users TO 'app_user'@'localhost';

列权限：

-- 授予列权限
GRANT SELECT (id, name), UPDATE (name) ON mydb.users TO 'app_user'@'localhost';

存储过程权限：

-- 授予执行存储过程权限
GRANT EXECUTE ON PROCEDURE mydb.sp_get_user TO 'app_user'@'localhost';

授权并允许转授：

-- WITH GRANT OPTION：允许用户将权限授予其他用户
GRANT SELECT, INSERT ON mydb.* TO 'app_user'@'localhost' WITH GRANT OPTION;

29.2.3 查看权限

查看用户权限：

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

-- 查看指定用户权限
SHOW GRANTS FOR 'app_user'@'localhost';

-- 查看权限详细信息
SELECT * FROM mysql.user WHERE user = 'app_user'\G
SELECT * FROM mysql.db WHERE user = 'app_user'\G
SELECT * FROM mysql.tables_priv WHERE user = 'app_user'\G

29.2.4 撤销权限

撤销权限：

-- 撤销所有权限
REVOKE ALL PRIVILEGES ON *.* FROM 'app_user'@'localhost';

-- 撤销特定权限
REVOKE INSERT, UPDATE ON mydb.* FROM 'app_user'@'localhost';

-- 撤销表权限
REVOKE SELECT ON mydb.users FROM 'app_user'@'localhost';

29.2.5 刷新权限

刷新权限：

-- 刷新权限（使权限立即生效）
FLUSH PRIVILEGES;

-- 注意：使用GRANT/REVOKE会自动刷新，直接修改mysql.user表需要手动刷新

29.3 角色管理（MySQL 8.0+）⭐⭐⭐⭐

29.3.1 创建角色

创建角色：

-- 创建角色
CREATE ROLE 'app_read', 'app_write', 'app_admin';

-- 授予角色权限
GRANT SELECT ON mydb.* TO 'app_read';
GRANT INSERT, UPDATE, DELETE ON mydb.* TO 'app_write';
GRANT ALL PRIVILEGES ON mydb.* TO 'app_admin';

29.3.2 分配角色

分配角色给用户：

-- 创建用户
CREATE USER 'user1'@'localhost' IDENTIFIED BY 'Password@123';

-- 分配角色
GRANT 'app_read' TO 'user1'@'localhost';

-- 分配多个角色
GRANT 'app_read', 'app_write' TO 'user1'@'localhost';

-- 激活角色（重要）
SET DEFAULT ROLE ALL TO 'user1'@'localhost';

29.3.3 查看角色

查看角色：

-- 查看所有角色
SELECT user, host FROM mysql.user WHERE account_locked = 'Y';

-- 查看用户的角色
SHOW GRANTS FOR 'user1'@'localhost';

-- 查看角色的权限
SHOW GRANTS FOR 'app_read';

29.3.4 删除角色

删除角色：

-- 撤销用户的角色
REVOKE 'app_read' FROM 'user1'@'localhost';

-- 删除角色
DROP ROLE 'app_read';

29.4 权限最佳实践

29.4.1 最小权限原则

原则：

只授予必要的权限
不要使用root用户
不要授予ALL PRIVILEGES

示例：

-- ❌ 不推荐：授予所有权限
GRANT ALL PRIVILEGES ON *.* TO 'app_user'@'%';

-- ✅ 推荐：只授予必要权限
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'app_user'@'192.168.1.%';

29.4.2 用户分类

只读用户：

CREATE USER 'readonly'@'%' IDENTIFIED BY 'ReadOnly@123';
GRANT SELECT ON mydb.* TO 'readonly'@'%';

读写用户：

CREATE USER 'readwrite'@'192.168.1.%' IDENTIFIED BY 'ReadWrite@123';
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'readwrite'@'192.168.1.%';

管理员用户：

CREATE USER 'admin'@'localhost' IDENTIFIED BY 'Admin@123';
GRANT ALL PRIVILEGES ON mydb.* TO 'admin'@'localhost';

备份用户：

CREATE USER 'backup'@'localhost' IDENTIFIED BY 'Backup@123';
GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON *.* TO 'backup'@'localhost';

监控用户：

CREATE USER 'monitor'@'localhost' IDENTIFIED BY 'Monitor@123';
GRANT PROCESS, REPLICATION CLIENT ON *.* TO 'monitor'@'localhost';

29.4.3 密码策略

设置密码策略：

-- 查看密码策略
SHOW VARIABLES LIKE 'validate_password%';

-- 设置密码策略（my.cnf）
[mysqld]
validate_password.policy = MEDIUM
validate_password.length = 8
validate_password.mixed_case_count = 1
validate_password.number_count = 1
validate_password.special_char_count = 1

-- 密码过期策略
ALTER USER 'app_user'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;

-- 密码重用策略
ALTER USER 'app_user'@'localhost' PASSWORD HISTORY 5;

29.5 常见场景

29.5.1 应用程序用户

创建应用程序用户：

-- 1. 创建用户
CREATE USER 'app_user'@'192.168.1.%' IDENTIFIED BY 'App@123456';

-- 2. 授予权限
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'app_user'@'192.168.1.%';

-- 3. 刷新权限
FLUSH PRIVILEGES;

29.5.2 主从复制用户

创建复制用户：

-- 主库创建复制用户
CREATE USER 'repl'@'192.168.1.%' IDENTIFIED BY 'Repl@123456';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.%';
FLUSH PRIVILEGES;

29.6 本章总结

本章学习内容：

✅ 用户管理（创建、修改、删除）⭐⭐⭐⭐⭐
✅ 权限管理（授予、撤销、查看）⭐⭐⭐⭐⭐
✅ 角色管理（MySQL 8.0+）⭐⭐⭐⭐
✅ 权限最佳实践
✅ 常见场景

重点掌握：

CREATE USER创建用户
GRANT授予权限
REVOKE撤销权限
最小权限原则
角色管理（MySQL 8.0+）

用户管理：

CREATE USER：创建用户
ALTER USER：修改用户
DROP USER：删除用户
RENAME USER：重命名用户

权限管理：

GRANT：授予权限
REVOKE：撤销权限
SHOW GRANTS：查看权限
FLUSH PRIVILEGES：刷新权限

面试重点：

如何创建用户
如何授予权限
最小权限原则
角色和用户的区别
如何创建只读用户

下一章预告： MySQL安全加固

练习题

如何创建一个用户？
如何授予用户SELECT权限？
如何查看用户权限？
如何撤销用户权限？
什么是最小权限原则？
如何创建只读用户？
如何创建角色？（MySQL 8.0+）
GRANT和REVOKE有什么区别？
如何修改用户密码？
实战：创建一个应用程序用户并授予权限

继续学习： 第30章：MySQL安全加固