第29章：用户与权限管理
第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安全加固