第13课: 用户权限管理

CREATE USER、GRANT、REVOKE、权限体系

MySQL 权限体系

MySQL 采用分层的权限管理系统，从全局到表级、列级，提供细粒度的访问控制。

MySQL权限管理层次结构示意图

创建用户

-- 示例1: 创建用户
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password123';
CREATE USER 'app_user'@'%' IDENTIFIED BY 'secure_pass';  -- % 表示任意主机
CREATE USER 'readonly'@'192.168.1.%' IDENTIFIED BY 'read_pass';  -- 特定网段

-- 示例2: 修改用户密码
ALTER USER 'username'@'localhost' IDENTIFIED BY 'new_password';
SET PASSWORD FOR 'username'@'localhost' = 'new_password';

授予权限 (GRANT)

-- 示例3: 授予所有权限
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost';
GRANT ALL PRIVILEGES ON mydb.* TO 'dbadmin'@'localhost';

-- 示例4: 授予特定权限
GRANT SELECT, INSERT, UPDATE ON mydb.users TO 'app_user'@'%';
GRANT SELECT ON mydb.* TO 'readonly'@'%';

-- 示例5: 授予表级权限
GRANT SELECT, INSERT ON mydb.orders TO 'order_manager'@'localhost';
GRANT UPDATE (price, stock) ON mydb.products TO 'inventory'@'localhost';

-- 示例6: 授予列级权限
GRANT SELECT (id, username, email) ON mydb.users TO 'support'@'%';
GRANT UPDATE (status) ON mydb.orders TO 'customer_service'@'%';

常用权限类型

权限	说明	适用范围
SELECT	查询数据	表、列
INSERT	插入数据	表、列
UPDATE	更新数据	表、列
DELETE	删除数据	表
CREATE	创建数据库、表	数据库、表
DROP	删除数据库、表	数据库、表
ALTER	修改表结构	表
INDEX	创建/删除索引	表
EXECUTE	执行存储过程	存储过程

撤销权限 (REVOKE)

-- 示例7: 撤销权限
REVOKE INSERT, UPDATE ON mydb.users FROM 'app_user'@'%';
REVOKE ALL PRIVILEGES ON mydb.* FROM 'temp_user'@'localhost';

-- 示例8: 撤销特定表的权限
REVOKE DELETE ON mydb.orders FROM 'order_manager'@'localhost';

查看权限

-- 示例9: 查看用户权限
SHOW GRANTS FOR 'username'@'localhost';
SHOW GRANTS FOR CURRENT_USER();

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

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

删除用户

-- 示例10: 删除用户
DROP USER 'username'@'localhost';
DROP USER IF EXISTS 'temp_user'@'%';

角色管理（MySQL 8.0+）

-- 创建角色
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';

-- 将角色分配给用户
GRANT 'app_read' TO 'user1'@'localhost';
GRANT 'app_read', 'app_write' TO 'user2'@'localhost';

-- 激活角色
SET DEFAULT ROLE ALL TO 'user1'@'localhost';

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

实战场景：电商系统权限设计

-- 1. 创建应用用户（读写权限）
CREATE USER 'ecommerce_app'@'192.168.1.%' IDENTIFIED BY 'app_secure_pass';
GRANT SELECT, INSERT, UPDATE ON ecommerce.* TO 'ecommerce_app'@'192.168.1.%';
GRANT DELETE ON ecommerce.order_items TO 'ecommerce_app'@'192.168.1.%';

-- 2. 创建只读用户（报表查询）
CREATE USER 'report_user'@'%' IDENTIFIED BY 'report_pass';
GRANT SELECT ON ecommerce.* TO 'report_user'@'%';

-- 3. 创建备份用户
CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'backup_pass';
GRANT SELECT, LOCK TABLES, SHOW VIEW, RELOAD ON *.* TO 'backup_user'@'localhost';

-- 4. 创建管理员用户
CREATE USER 'db_admin'@'localhost' IDENTIFIED BY 'admin_pass';
GRANT ALL PRIVILEGES ON ecommerce.* TO 'db_admin'@'localhost' WITH GRANT OPTION;

-- 5. 刷新权限
FLUSH PRIVILEGES;

安全加固

-- 删除匿名用户
DELETE FROM mysql.user WHERE user = '';

-- 删除测试数据库
DROP DATABASE IF EXISTS test;

-- 禁用 root 远程登录
DELETE FROM mysql.user WHERE user = 'root' AND host != 'localhost';

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

-- 限制连接数
ALTER USER 'app_user'@'%' WITH MAX_CONNECTIONS_PER_HOUR 1000;

-- 刷新权限
FLUSH PRIVILEGES;