用户权限管理

一、用户管理

1.1 用户概念

1.1.1 MySQL用户

MySQL用户：连接MySQL服务器的账户，存储在mysql.user表中。

东巴文理解：

用户 = 门禁卡
不同门禁卡有不同权限
有的卡只能进大门
有的卡能进所有房间
用户权限决定能做什么操作

1.1.2 用户标识

用户标识：用户名@主机名

示例：

• 'root'@'localhost'：本地root用户
• 'admin'@'%'：任意主机admin用户
• 'user1'@'192.168.1.%'：192.168.1网段用户

1.2 创建用户

1.2.1 CREATE USER语法

语法格式：

CREATE USER [IF NOT EXISTS] '用户名'@'主机名'
IDENTIFIED BY '密码'
[PASSWORD EXPIRE {DEFAULT|NEVER|INTERVAL 数字 DAY|AT TIMESTAMP}]
[ACCOUNT {LOCK|UNLOCK}];

参数说明：

• IF NOT EXISTS：用户不存在时创建
• IDENTIFIED BY：设置密码
• PASSWORD EXPIRE：密码过期策略
• ACCOUNT LOCK/UNLOCK：锁定/解锁账户

1.2.2 创建用户示例

示例：

-- 创建本地用户
CREATE USER 'user1'@'localhost' IDENTIFIED BY 'password123';

-- 创建远程用户
CREATE USER 'user2'@'%' IDENTIFIED BY 'password123';

-- 创建特定网段用户
CREATE USER 'user3'@'192.168.1.%' IDENTIFIED BY 'password123';

-- 创建带密码过期策略的用户
CREATE USER 'user4'@'localhost' 
IDENTIFIED BY 'password123'
PASSWORD EXPIRE INTERVAL 90 DAY;

-- 创建锁定用户
CREATE USER 'user5'@'localhost' 
IDENTIFIED BY 'password123'
ACCOUNT LOCK;

1.3 修改用户

1.3.1 修改密码

语法格式：

ALTER USER '用户名'@'主机名' IDENTIFIED BY '新密码';

示例：

-- 修改用户密码
ALTER USER 'user1'@'localhost' IDENTIFIED BY 'newpassword123';

-- 修改当前用户密码
ALTER USER USER() IDENTIFIED BY 'newpassword123';

1.3.2 修改用户属性

示例：

-- 锁定用户
ALTER USER 'user1'@'localhost' ACCOUNT LOCK;

-- 解锁用户
ALTER USER 'user1'@'localhost' ACCOUNT UNLOCK;

-- 设置密码过期
ALTER USER 'user1'@'localhost' PASSWORD EXPIRE;

-- 设置密码永不过期
ALTER USER 'user1'@'localhost' PASSWORD EXPIRE NEVER;

1.3.3 重命名用户

语法格式：

RENAME USER '旧用户名'@'旧主机名' TO '新用户名'@'新主机名';

示例：

-- 重命名用户
RENAME USER 'user1'@'localhost' TO 'newuser1'@'localhost';

1.4 删除用户

1.4.1 DROP USER语法

语法格式：

DROP USER [IF EXISTS] '用户名'@'主机名' [, '用户名2'@'主机名2', ...];

示例：

-- 删除单个用户
DROP USER IF EXISTS 'user1'@'localhost';

-- 删除多个用户
DROP USER IF EXISTS 'user2'@'%', 'user3'@'192.168.1.%';

1.5 查看用户

1.5.1 查看所有用户

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

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

1.5.2 查看用户详细信息

-- 查看用户详细信息
SELECT * FROM mysql.user WHERE User = 'user1';

-- 查看用户权限
SHOW GRANTS FOR 'user1'@'localhost';

二、权限系统

2.1 权限类型

2.1.1 常用权限

数据操作权限：

管理权限：

2.2 权限级别

2.2.1 全局权限

作用范围：整个MySQL服务器

语法格式：

GRANT 权限列表 ON *.* TO '用户名'@'主机名';

示例：

-- 授予全局查询权限
GRANT SELECT ON *.* TO 'user1'@'localhost';

-- 授予全局所有权限
GRANT ALL ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;

2.2.2 数据库权限

作用范围：指定数据库

语法格式：

GRANT 权限列表 ON 数据库名.* TO '用户名'@'主机名';

示例：

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

-- 授予数据库查询和插入权限
GRANT SELECT, INSERT ON mydb.* TO 'user2'@'localhost';

2.2.3 表权限

作用范围：指定表

语法格式：

GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';

示例：

-- 授予表查询权限
GRANT SELECT ON mydb.users TO 'user1'@'localhost';

-- 授予表所有权限
GRANT ALL ON mydb.orders TO 'user2'@'localhost';

2.2.4 列权限

作用范围：指定列

语法格式：

GRANT 权限(列名列表) ON 数据库名.表名 TO '用户名'@'主机名';

示例：

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

-- 授予列更新权限
GRANT UPDATE(salary) ON mydb.employees TO 'user2'@'localhost';

2.3 授权操作

2.3.1 GRANT语法

语法格式：

GRANT 权限列表 
ON 权限级别 
TO '用户名'@'主机名' 
[WITH GRANT OPTION]
[WITH {MAX_QUERIES_PER_HOUR 数量|MAX_UPDATES_PER_HOUR 数量|...}];

参数说明：

• WITH GRANT OPTION：允许用户授权给其他用户
• MAX_QUERIES_PER_HOUR：每小时最大查询次数
• MAX_UPDATES_PER_HOUR：每小时最大更新次数
• MAX_CONNECTIONS_PER_HOUR：每小时最大连接次数
• MAX_USER_CONNECTIONS：最大并发连接数

2.3.2 授权示例

示例：

-- 授予查询权限
GRANT SELECT ON mydb.* TO 'user1'@'localhost';

-- 授予多个权限
GRANT SELECT, INSERT, UPDATE ON mydb.* TO 'user2'@'localhost';

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

-- 授予权限并限制资源
GRANT SELECT ON mydb.* TO 'user3'@'localhost'
WITH MAX_QUERIES_PER_HOUR 1000
MAX_UPDATES_PER_HOUR 500
MAX_CONNECTIONS_PER_HOUR 100;

2.4 撤销权限

2.4.1 REVOKE语法

语法格式：

REVOKE 权限列表 ON 权限级别 FROM '用户名'@'主机名';
REVOKE ALL ON 权限级别 FROM '用户名'@'主机名';
REVOKE ALL PRIVILEGES, GRANT OPTION FROM '用户名'@'主机名';

2.4.2 撤销权限示例

示例：

-- 撤销查询权限
REVOKE SELECT ON mydb.* FROM 'user1'@'localhost';

-- 撤销多个权限
REVOKE SELECT, INSERT ON mydb.* FROM 'user2'@'localhost';

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

-- 撤销所有权限和授权权限
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'admin'@'localhost';

2.5 查看权限

2.5.1 查看用户权限

-- 查看用户权限
SHOW GRANTS FOR 'user1'@'localhost';

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

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

2.5.2 查看权限表

-- 查看全局权限
SELECT * FROM mysql.user WHERE User = 'user1';

-- 查看数据库权限
SELECT * FROM mysql.db WHERE User = 'user1';

-- 查看表权限
SELECT * FROM mysql.tables_priv WHERE User = 'user1';

-- 查看列权限
SELECT * FROM mysql.columns_priv WHERE User = 'user1';

三、角色管理

3.1 角色概念

3.1.1 什么是角色

角色：权限的集合，可以授予用户。

东巴文理解：

角色 = 职位
职位对应一系列权限
给员工分配职位
就自动拥有职位的权限
角色简化权限管理

3.2 创建角色

3.2.1 CREATE ROLE语法

语法格式：

CREATE ROLE [IF NOT EXISTS] '角色名'@'主机名' [, '角色名2'@'主机名2', ...];

示例：

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

-- 创建多个角色
CREATE ROLE 'role1'@'localhost', 'role2'@'localhost';

3.3 授予角色权限

3.3.1 给角色授权

示例：

-- 给角色授予数据库查询权限
GRANT SELECT ON mydb.* TO 'app_read'@'localhost';

-- 给角色授予数据库写入权限
GRANT INSERT, UPDATE, DELETE ON mydb.* TO 'app_write'@'localhost';

-- 给角色授予所有权限
GRANT ALL ON mydb.* TO 'app_admin'@'localhost';

3.3.2 给用户授予角色

语法格式：

GRANT '角色名'@'主机名' TO '用户名'@'主机名';

示例：

-- 给用户授予角色
GRANT 'app_read'@'localhost' TO 'user1'@'localhost';
GRANT 'app_write'@'localhost' TO 'user2'@'localhost';
GRANT 'app_admin'@'localhost' TO 'admin'@'localhost';

3.4 激活角色

3.4.1 激活角色

语法格式：

SET ROLE '角色名'@'主机名';
SET ROLE ALL;
SET ROLE NONE;

示例：

-- 激活指定角色
SET ROLE 'app_read'@'localhost';

-- 激活所有角色
SET ROLE ALL;

-- 取消所有角色
SET ROLE NONE;

3.4.2 自动激活角色

设置自动激活：

-- 设置登录时自动激活所有角色
SET GLOBAL activate_all_roles_on_login = ON;

3.5 查看角色

3.5.1 查看角色列表

-- 查看所有角色
SELECT User, Host FROM mysql.user WHERE Account_locked = 'Y';

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

3.5.2 查看当前角色

-- 查看当前激活的角色
SELECT CURRENT_ROLE();

3.6 撤销角色

3.6.1 撤销用户角色

语法格式：

REVOKE '角色名'@'主机名' FROM '用户名'@'主机名';

示例：

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

3.6.2 撤销角色权限

示例：

-- 撤销角色权限
REVOKE SELECT ON mydb.* FROM 'app_read'@'localhost';

3.7 删除角色

3.7.1 DROP ROLE语法

语法格式：

DROP ROLE [IF EXISTS] '角色名'@'主机名' [, '角色名2'@'主机名2', ...];

示例：

-- 删除角色
DROP ROLE IF EXISTS 'app_read'@'localhost';

-- 删除多个角色
DROP ROLE IF EXISTS 'app_write'@'localhost', 'app_admin'@'localhost';

四、密码管理

4.1 密码策略

4.1.1 查看密码策略

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

4.1.2 密码策略变量

4.1.3 设置密码策略

示例：

-- 设置密码最小长度
SET GLOBAL validate_password_length = 8;

-- 设置密码策略级别
SET GLOBAL validate_password_policy = MEDIUM;

4.2 密码过期

4.2.1 设置密码过期

示例：

-- 设置密码立即过期
ALTER USER 'user1'@'localhost' PASSWORD EXPIRE;

-- 设置密码90天后过期
ALTER USER 'user1'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;

-- 设置密码永不过期
ALTER USER 'user1'@'localhost' PASSWORD EXPIRE NEVER;

4.2.2 查看密码过期状态

-- 查看用户密码过期状态
SELECT User, Host, password_expired, password_last_changed, password_lifetime
FROM mysql.user
WHERE User = 'user1';

4.3 密码重置

4.3.1 管理员重置密码

示例：

-- 重置用户密码
ALTER USER 'user1'@'localhost' IDENTIFIED BY 'newpassword123';

-- 重置密码并设置过期
ALTER USER 'user1'@'localhost' 
IDENTIFIED BY 'newpassword123'
PASSWORD EXPIRE;

4.3.2 用户自己修改密码

示例：

-- 用户修改自己的密码
ALTER USER USER() IDENTIFIED BY 'newpassword123';

-- 使用SET PASSWORD
SET PASSWORD = PASSWORD('newpassword123');

五、安全最佳实践

5.1 最小权限原则

5.1.1 只授予必要权限

不推荐：

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

推荐：

-- 只授予必要权限
GRANT SELECT, INSERT, UPDATE ON mydb.* TO 'app_user'@'localhost';

5.1.2 限制访问范围

不推荐：

-- 允许任意主机访问
CREATE USER 'app_user'@'%' IDENTIFIED BY 'password';

推荐：

-- 只允许特定主机访问
CREATE USER 'app_user'@'192.168.1.100' IDENTIFIED BY 'password';

5.2 密码安全

5.2.1 强密码策略

示例：

-- 设置强密码策略
SET GLOBAL validate_password_length = 12;
SET GLOBAL validate_password_mixed_case_count = 2;
SET GLOBAL validate_password_number_count = 2;
SET GLOBAL validate_password_special_char_count = 2;
SET GLOBAL validate_password_policy = STRONG;

5.2.2 定期更换密码

示例：

-- 设置密码90天过期
CREATE USER 'user1'@'localhost' 
IDENTIFIED BY 'password123'
PASSWORD EXPIRE INTERVAL 90 DAY;

5.3 账户锁定

5.3.1 锁定不活跃账户

示例：

-- 锁定账户
ALTER USER 'inactive_user'@'localhost' ACCOUNT LOCK;

-- 解锁账户
ALTER USER 'inactive_user'@'localhost' ACCOUNT UNLOCK;

5.3.2 失败登录锁定

MySQL 8.0+支持失败登录锁定：

-- 创建用户时设置失败登录锁定
CREATE USER 'user1'@'localhost' 
IDENTIFIED BY 'password123'
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME 1;

5.4 审计日志

5.4.1 启用审计日志

配置文件：

[mysqld]
plugin-load = audit_log.so
audit_log_format = JSON
audit_log_policy = ALL

5.4.2 查看审计日志

-- 查看审计日志状态
SHOW VARIABLES LIKE 'audit_log%';

-- 查看审计日志文件
SHOW VARIABLES LIKE 'audit_log_file';

六、实战案例

6.1 创建应用用户

-- 创建应用数据库
CREATE DATABASE app_db;

-- 创建只读用户
CREATE USER 'app_read'@'192.168.1.%' IDENTIFIED BY 'Read@123456';
GRANT SELECT ON app_db.* TO 'app_read'@'192.168.1.%';

-- 创建读写用户
CREATE USER 'app_write'@'192.168.1.%' IDENTIFIED BY 'Write@123456';
GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO 'app_write'@'192.168.1.%';

-- 创建管理员用户
CREATE USER 'app_admin'@'192.168.1.%' IDENTIFIED BY 'Admin@123456';
GRANT ALL ON app_db.* TO 'app_admin'@'192.168.1.%';

-- 刷新权限
FLUSH PRIVILEGES;

6.2 创建开发环境用户

-- 创建开发角色
CREATE ROLE 'dev_read'@'localhost', 'dev_write'@'localhost', 'dev_admin'@'localhost';

-- 给角色授权
GRANT SELECT ON dev_db.* TO 'dev_read'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE ON dev_db.* TO 'dev_write'@'localhost';
GRANT ALL ON dev_db.* TO 'dev_admin'@'localhost';

-- 创建开发用户
CREATE USER 'dev1'@'localhost' IDENTIFIED BY 'Dev@123456';
CREATE USER 'dev2'@'localhost' IDENTIFIED BY 'Dev@123456';

-- 给用户授予角色
GRANT 'dev_read'@'localhost' TO 'dev1'@'localhost';
GRANT 'dev_write'@'localhost' TO 'dev2'@'localhost';

-- 设置自动激活角色
SET GLOBAL activate_all_roles_on_login = ON;

6.3 创建报表用户

-- 创建报表用户
CREATE USER 'report_user'@'%' IDENTIFIED BY 'Report@123456';

-- 授予查询权限
GRANT SELECT ON production_db.* TO 'report_user'@'%';

-- 限制查询次数
GRANT SELECT ON production_db.* TO 'report_user'@'%'
WITH MAX_QUERIES_PER_HOUR 1000
MAX_CONNECTIONS_PER_HOUR 50;

-- 设置密码过期
ALTER USER 'report_user'@'%' PASSWORD EXPIRE INTERVAL 180 DAY;

-- 刷新权限
FLUSH PRIVILEGES;

七、本章小结

7.1 核心要点

✅ 用户管理包括创建、修改、删除用户 ✅ 权限系统支持全局、数据库、表、列级别 ✅ GRANT授权，REVOKE撤销权限 ✅ 角色简化权限管理 ✅ 密码策略确保安全 ✅ 遵循最小权限原则

7.2 验证清单

完成本章学习后，请确认您能够：

• 创建和管理用户
• 授予和撤销权限
• 创建和管理角色
• 设置密码策略
• 应用安全最佳实践