MySQL用户管理与安全实践

1 MySQL用户管理实操指南

1.1 创建用户

创建本地用户的基础语法：

CREATE USER 'martin'@'localhost' IDENTIFIED BY 'password';

若需为同事创建martin库的远程只读用户（IP为192.168.1.3），可执行：

CREATE USER 'martin_r'@'192.168.1.3' IDENTIFIED BY 'password';

若需允许某一网段（如192.168.1.%）的客户端访问，可使用通配符：

CREATE USER 'martin_r'@'192.168.1.%' IDENTIFIED BY 'password';

1.2 查询用户

查看当前数据库中所有用户及对应的主机信息：

select user,host from mysql.user;

1.3 删除用户

删除指定用户（需精确匹配用户名和主机）：

DROP USER 'martin_r'@'192.168.1.%';

2 MySQL权限控制策略

2.1 权限层级分类

• 全局权限：覆盖整个数据库系统的操作权限（如创建/删除数据库、修改系统配置等），仅授予核心系统管理员。
• 数据库权限：针对单个数据库的访问控制（如数据读写），按职责分配以保护敏感信息。
• 表/列权限：最细粒度的权限设置，可限制对特定表或列的访问（例如：仅允许销售团队查看客户联系方式，禁止访问支付记录）。

2.2 常用权限查询

查看MySQL支持的所有权限类型：

show privileges;

2.3 权限管理操作

2.3.1 用户授权

授予用户对martin库的增删改查权限：

GRANT insert,delete,select,update ON martin.* TO 'martin'@'localhost';

创建测试表并插入数据（用于权限验证）：

create table grant_test (id int , name varchar(20),age int);
insert into grant_test values (1,'a',1);

若需限制用户仅能查询特定列（如id和name），可执行：

CREATE USER martin_r_1@localhost IDENTIFIED BY 'password';
grant select(id,name) on martin.grant_test to martin_r_1@localhost;

使用该用户登录后，允许的查询：

select id,name from grant_test; -- 正常执行

超出权限的查询会报错：

select id,name,age from grant_test; -- 执行失败

2.3.2 查询权限

查看指定用户的权限明细：

show grants for martin_r_1@localhost;

2.3.3 权限回收

撤销用户对martin库的插入权限：

REVOKE INSERT ON martin.* FROM 'martin'@'localhost';

2.2.4 权限授予原则

• 为不同场景创建独立用户，避免共用账号
• 遵循“最小权限原则”，仅授予完成工作必需的权限
• 禁止日常操作使用root账号

3 MySQL角色管理机制

3.1 创建角色

CREATE ROLE 'app_developer'; -- 创建应用开发者角色

3.2 为角色赋权

授予角色对app_db库的完整操作权限：

GRANT select,update,delete,insert ON app_db.* TO 'app_developer';

3.3 角色关联用户

1. 先创建用户：

create user 'martin_role'@'localhost' identified by 'IHB87Edsa';

2. 将角色赋予用户（替代直接授予权限）：

grant 'app_developer' to 'martin_role'@'localhost';

3. 查看权限关联：

show grants for martin_role@localhost; -- 查看用户权限
show grants for app_developer; -- 查看角色权限

3.4 角色管理其他操作

撤销用户的角色：

revoke app_developer from 'martin_role'@'localhost';

删除角色：

drop role app_developer;

4 MySQL密码安全管理

4.1 密码修改操作

• 修改当前登录用户（如root）的密码：

ALTER USER user() IDENTIFIED BY 'auth_string';

• 修改指定用户的密码：

alter user 'martin'@'localhost' identified by 'xxx';

• 创建用户时自动生成随机密码：

create user 'martin'@'localhost' identified by random password;

• 为已有用户重置随机密码：

alter user 'martin'@'localhost' identified by random password;

4.2 密码安全最佳实践

4.2.1 强制使用强密码

通过安装validate_password插件实现密码强度控制：

install component 'file://component_validate_password';

查看密码策略参数：

show global variables like 'validate_password%';

关键参数说明：

• validate_password.check_user_name：禁止密码与用户名相同
• validate_password.length：密码最小长度限制
• validate_password.policy：强度等级（0/LOW：仅检查长度；1/MEDIUM：检查长度、数字、大小写及特殊字符；2/STRONG：额外检查字典文件）
• validate_password.mixed_case_count：大小写字母最少数量
• validate_password.number_count：最少数字数量
• validate_password.special_char_count：最少特殊字符数量

4.2.2 密码试错限制

创建用户时限制密码错误次数（如连续输错4次锁定3天）：

create user 'test_pass'@'localhost' identified by '1QAZ@wsx' failed_login_attempts 4 password_lock_time 3;
grant select on *.* to 'test_pass'@'localhost';

验证锁定机制（故意输入错误密码）：

mysql -utest_pass -p'aaaaa' -- 多次执行将触发锁定

5 MySQL用户资源限制

5.1 资源限制参数说明

可限制的用户资源包括：

• 每小时最大查询次数（MAX_QUERIES_PER_HOUR）
• 每小时最大更新次数（MAX_UPDATES_PER_HOUR）
• 每小时最大连接次数（MAX_CONNECTIONS_PER_HOUR）
• 并发连接数上限（MAX_USER_CONNECTIONS）

5.2 资源限制配置

5.2.1 创建用户时限制资源

CREATE USER 'martin_02'@'localhost' identified by 'Udapadg999a^'
WITH MAX_QUERIES_PER_HOUR 500  -- 每小时最多500次查询
MAX_UPDATES_PER_HOUR 100      -- 每小时最多100次更新
MAX_CONNECTIONS_PER_HOUR 50   -- 每小时最多50次连接
MAX_USER_CONNECTIONS 50;      -- 最多50个并发连接

5.2.2 修改现有用户资源限制

ALTER USER 'martin'@'localhost' WITH MAX_QUERIES_PER_HOUR 100;

取消限制（将值设为0）：

ALTER USER 'martin'@'localhost' WITH MAX_QUERIES_PER_HOUR 0;

5.2.3 重置资源使用计数

FLUSH USER_RESOURCES;  -- 单独重置资源计数
-- 或
FLUSH PRIVILEGES;      -- 刷新权限时同步重置

6 找回MySQL root密码

6.1 操作步骤

1. 修改配置文件跳过权限验证：

vim /data/mysql/conf/my.cnf  -- 编辑配置文件

在[mysqld]节点添加：

skip-grant-tables  -- 跳过权限校验

2. 重启MySQL服务：

/etc/init.d/mysql.server restart

3. 免密登录并清空root密码：

mysql -uroot -p  -- 直接回车登录（无需密码）

执行SQL清空密码：

update mysql.user set authentication_string = '' 
where user = 'root' and host='localhost';

4. 恢复权限验证配置：

vim /data/mysql/conf/my.cnf  -- 删除skip-grant-tables参数
/etc/init.d/mysql.server restart  -- 重启服务

5. 重置root密码：

mysql -uroot -p  -- 使用空密码登录

执行修改密码语句：

alter user user() identified by 'dup81Gcda2';  -- 设置新密码

7 SQL MODE详解

7.1 SQL MODE的查询与配置

7.1.1 查询SQL MODE

select @@global.sql_mode;  -- 查看全局模式
select @@session.sql_mode; -- 查看当前会话模式

7.1.2 设置SQL MODE

1. 启动时指定（命令行）：

mysqld --sql-mode="XXX"

2. 配置文件持久化（推荐）：

vim /data/mysql/conf/my.cnf

在[mysqld]节点添加：

sql-mode='XXX'  -- 例如：sql-mode='STRICT_TRANS_TABLES,ONLY_FULL_GROUP_BY'

3. 运行时修改全局模式（重启后失效）：

SET GLOBAL sql_mode = "XXX";

4. 运行时修改当前会话模式：

SET SESSION sql_mode = "xxx";

5. 新增模式（保留现有配置）：

SET SESSION sql_mode = sys.list_add(@@session.sql_mode, 'ONLY_FULL_GROUP_BY');

6. 移除特定模式：

SET SESSION sql_mode = sys.list_drop(@@session.sql_mode, 'ONLY_FULL_GROUP_BY');

7. 清空模式：

SET SESSION sql_mode = '';

7.2 常见SQL MODE说明

• STRICT_TRANS_TABLES：严格模式，插入无效值时报错（而非警告）
• ONLY_FULL_GROUP_BY：限制GROUP BY查询中非聚合列的使用
• TRADITIONAL：传统模式，模拟“严格SQL”行为，Invalid值直接报错
• ANSI：兼容ANSI标准，宽松模式（无效值可能被截断或转换）

8 SQL MODE对数据的影响

8.1 实验验证

8.1.1 字段长度超限的处理差异

1. 宽松模式（ANSI）：

set session sql_mode='ansi';
create table sql_mode_test(id int auto_increment,name varchar(5),primary key(id));
insert into sql_mode_test values(1,'克里斯蒂亚诺·罗纳尔多'); -- 超长字符串
show warnings; -- 提示“数据被截断”
select * from sql_mode_test; -- name字段被截断为5个字符

2. 严格模式（STRICT_TRANS_TABLES）：

set session sql_mode='STRICT_TRANS_TABLES';
insert into sql_mode_test values(2,'abcefghi'); -- 直接报错
-- 错误信息：ERROR 1406 (22001): Data too long for column 'name' at row 1

8.1.2 无效时间格式的处理差异

1. 宽松模式（ANSI）：

set session sql_mode='ansi';
create table time_test(createtime timestamp);
insert into time_test values('2017-03-20 00:00:60'); -- 无效时间（秒数超限）
show warnings; -- 提示“时间值无效”
select * from time_test; -- 存储为0000-00-00 00:00:00

2. 传统模式（TRADITIONAL）：

set session sql_mode='TRADITIONAL';
insert into time_test values('2017-03-20 00:00:60'); -- 直接报错
-- 错误信息：Invalid datetime value: '2017-03-20 00:00:60'

8.1.3 ONLY_FULL_GROUP_BY的影响

1. 未启用时（可能产生错误结果）：

create table group_by_test (id int auto_increment primary key,name varchar(10),address varchar(10),score int);
insert into group_by_test(name,address,score) values 
('a','beijing',90),('b','shanghai',88),('c','beijing',86),('d','shanghai',92);

-- 错误查询（非聚合列name未在GROUP BY中）
select address,name,max(score) from group_by_test group by address; 
-- 结果中name可能与max(score)不匹配

2. 启用后（强制规范查询）：

SET SESSION sql_mode = sys.list_add(@@session.sql_mode, 'ONLY_FULL_GROUP_BY');
select address,name,max(score) from group_by_test group by address; -- 直接报错
-- 错误信息：Expression #2 of SELECT list is not in GROUP BY clause

8.2 最佳实践建议

• 启用严格模式（如STRICT_TRANS_TABLES），避免无效数据写入
• 强制开启ONLY_FULL_GROUP_BY，规范GROUP BY查询逻辑
• 数据库迁移或升级时，确保新旧环境SQL MODE一致

9 实战案例：用户与角色配置

9.1 需求与实现

需创建4个用户，权限要求如下：

• user1@192.168.1.3：product库读写权限
• user2@192.168.2.%：product库读写权限
• user3@192.168.3.5：product库只读，密码输错5次永久锁定
• user4@192.168.3.6：product.inventory_info表只读，每小时最多100次查询，并发连接≤2

实现步骤：

1. 创建共享角色（用于user1和user2）：

CREATE ROLE 'product_rw_role'; -- 定义读写角色
GRANT SELECT,UPDATE,DELETE,INSERT ON product.* TO 'product_rw_role'; -- 角色赋权

2. 创建user1并关联角色：

CREATE user 'user1'@'192.168.1.3' identified by random password; -- 随机密码
GRANT 'product_rw_role' TO 'user1'@'192.168.1.3'; -- 关联角色

3. 创建user2并关联角色：

CREATE user 'user2'@'192.168.2.%' identified by random password;
GRANT 'product_rw_role' TO 'user2'@'192.168.2.%';

4. 创建user3（带密码锁定）：

CREATE user 'user3'@'192.168.3.5' identified by random by password failed_login_attempts 5 password_lock_time unbounded; -- 永久锁定
GRANT select ON product.* TO 'user3'@'192.168.3.5';

5. 创建user4（带资源限制）：

CREATE user 'user4'@'192.168.3.6' identified by random password
WITH MAX_QUERIES_PER_HOUR 100 
MAX_USER_CONNECTIONS 2;
GRANT select ON product.inventory_info to 'user4'@'192.168.3.6';

10 借助ChatGPT高效管理MySQL用户

10.1 创建用户与赋权

需求：MySQL8.0中创建用户rw_user@%，授予所有库的增删改查权限。
生成SQL：

CREATE USER 'rw_user'@'%' IDENTIFIED BY 'your_password_here';
GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'rw_user'@'%';
FLUSH PRIVILEGES; -- 刷新权限

10.2 权限回收

需求：MySQL8.0，回收用户rw_user@%对所有库的delete权限。
生成SQL：

-- 先查询当前权限
show grants for 'rw_user'@'%';
-- 执行回收
REVOKE DELETE ON *.* FROM 'rw_user'@'%';
FLUSH PRIVILEGES;

10.3 批量生成权限查询语句

需求：MySQL8.0，为每个用户构造一条show grants for ‘user_name’@'host’的语句。
生成SQL：

SELECT CONCAT("SHOW GRANTS FOR '", user, "'@'", host, "';") 
AS show_grants_statement 
FROM mysql.user 
ORDER BY user, host;

11 章末总结

MySQL安全管理的核心在于“最小权限+精细控制”，需重点关注：

1. 用户与角色分离，通过角色批量管理权限
2. 强制密码强度与试错限制，防范暴力破解
3. 合理配置资源限制，避免单个用户耗尽系统资源
4. 启用严格SQL MODE（如STRICT_TRANS_TABLES、ONLY_FULL_GROUP_BY），保障数据一致性
5. 定期审计权限，及时回收冗余权限

通过规范化的安全策略，可显著降低数据库被未授权访问、数据泄露或误操作的风险。