云扬のBlog

与你共享美好生活

【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;
权限解释
insert允许写入数据
delete允许删除数据
select允许查询数据
update允许更新数据
create允许创建库和表
create role允许创建角色
create user启用创建用户、删除用户、重命名用户和撤销所有权限
drop允许删除库、表、视图等
drop role开启删除角色
alter允许修改表结构
lock tables在具有select权限的表上启用锁表权限
show databases开启查看所有库的权限
super允许使用其他管理操作，比如： CHANGE REPLICATION SOURCE TO, CHANGE MASTER TO, KILL, PURGE BINARY LOGS, SET GLOBAL，还包括mysqladmin debug 命令
all除grant option 和proxy权限外，赋予其他所有权限
更多 MySQL 权限参考可参考官方文档: https://dev.mysql.com/doc/refman/8.0/en/grant.html

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';
  1. 将角色赋予用户（替代直接授予权限）：
grant 'app_developer' to 'martin_role'@'localhost';
  1. 查看权限关联：
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  -- 跳过权限校验
  1. 重启MySQL服务：
/etc/init.d/mysql.server restart
  1. 免密登录并清空root密码：
mysql -uroot -p  -- 直接回车登录（无需密码）

执行SQL清空密码：

update mysql.user set authentication_string = '' 
where user = 'root' and host='localhost';
  1. 恢复权限验证配置：
vim /data/mysql/conf/my.cnf  -- 删除skip-grant-tables参数
/etc/init.d/mysql.server restart  -- 重启服务
  1. 重置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"
  1. 配置文件持久化（推荐）：
vim /data/mysql/conf/my.cnf

在[mysqld]节点添加：

sql-mode='XXX'  -- 例如：sql-mode='STRICT_TRANS_TABLES,ONLY_FULL_GROUP_BY'
  1. 运行时修改全局模式（重启后失效）：
SET GLOBAL sql_mode = "XXX";
  1. 运行时修改当前会话模式：
SET SESSION sql_mode = "xxx";
  1. 新增模式（保留现有配置）：
SET SESSION sql_mode = sys.list_add(@@session.sql_mode, 'ONLY_FULL_GROUP_BY');
  1. 移除特定模式：
SET SESSION sql_mode = sys.list_drop(@@session.sql_mode, 'ONLY_FULL_GROUP_BY');
  1. 清空模式：
SET SESSION sql_mode = '';

7.2 常见SQL MODE说明

SQL MODE解释
STRICT_TRANS_TABLES为事务存储引擎启用严格的SQL模式，在插入不合法数据时，MySQL将不再插入“默认值”，而是抛出错误。这样可以确保数据的完整性和一致性。这种模式比较适合对数据完整性要求严格的场景
ANSI等同于REAL_AS_FLOAT、PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE和ONLY_FULL_GROUP_BY
TRADITIONAL可以理解为让MySQL像传统的SQL数据库系统一样运行，等同于STRICT_TRANS_TABLES、STRICT_ALL_TABLES、NO_ZERO_IN_DATE、NO_ZERO_DATE、ERROR_FOR_DIVISION_BY_ZERO和NO_ENGINE_SUBSTITUTION
ONLY_FULL_GROUP_BY如果某个列不在group by列表中，此时如果不对该列进行聚合处理，则该列不能出现在select列表、having条件中以及order by列表中（下一节内容会通过实验来理解这个SQL MODE的作用）
NO_BACKSLASH_ESCAPES启用此模式将禁止使用反斜杠字符“\”作为字符串和标识符中的转义字符，反斜杠将像其他字符串一样变成普通字符
PIPES_AS_CONCAT将“||”视为字符串连接操作符
NO_ENGINE_SUBSTITUTION启用此模式时，如果创建或者更改表时指定了不可用的存储引擎，则报错不执行；未启用此模式时，会替换成默认存储引擎
NO_ZERO_DATE启用此模式，’0000-00-00′ 则允许插入并产生警告；如果启用此模式和严格模式，则不允许’0000-00-00’插入，并会报错；除非 使用IGNORE。对于 INSERT IGNORE and UPDATE IGNORE，’0000-00-00’是允许的，插入会产生警告
ERROR_FOR_DIVISION_BY_ZERO启用此模式，则除以零会插入 NULL并产生警告；启用此模式和严格模式，除以零会产生错误，除非IGNORE 也给出。对于INSERT IGNORE 和 UPDATE IGNORE，除以零插入NULL并产生警告
NO_ZERO_IN_DATE如果启用此模式，则零部分的日期将被插入’0000-00-00’并产生警告；如果启用此模式和严格模式，则不允许包含0的日期插入，会直接报错
REAL_AS_FLOAT把REAL类型（存储大小4个字节，可精确到小数点后第7位）看成FLOAT类型（存储大小为8字节，可精确到小数点后第15位）
ANSI_QUOTES双引号将被视为标识符的引号，而不是字符串的引号
IGNORE_SPACE函数名称和(之间允许有空格，会导致内置的函数变成保留字
更多SQL MODEhttps://dev.mysql.com/doc/refman/8.0/en/sql-mode.html
  • 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个字符
  1. 严格模式（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
  1. 传统模式（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)不匹配
  1. 启用后（强制规范查询）：
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'; -- 角色赋权
  1. 创建user1并关联角色：
CREATE user 'user1'@'192.168.1.3' identified by random password; -- 随机密码
GRANT 'product_rw_role' TO 'user1'@'192.168.1.3'; -- 关联角色
  1. 创建user2并关联角色：
CREATE user 'user2'@'192.168.2.%' identified by random password;
GRANT 'product_rw_role' TO 'user2'@'192.168.2.%';
  1. 创建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';
  1. 创建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 章末总结

知识点需要掌握的内容
用户管理创建用户、查询用户、删除用户
权限管理权限的分类、常用的一些权限、权限赋予、权限查询、权限回收、权限授予的原则
角色管理创建角色、给角色赋权、使用角色、撤销角色、删除角色
密码管理密码修改、密码管理的最佳实践
限制用户资源资源限制的几个参数、怎么增加资源限制、重置资源使用计数
忘记root密码怎么处理配置免密登录、root用户修改成空密码、重新修改密码
SQL MODE的使用查询SQL MODE、设置SQL MODE、常见SQL MODE解释
SQL MODE对查询结果的影响字段超过长度在不同sql_mode下的表现、写入错误的时间格式在不同sql_mode下的表现、ONLY_FULL_GROUP_BY对一些结果的影响

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

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

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

Tags:
Previous Article
Next Article

发表回复

Your email address will not be published. Required fields are marked *.

*
*

近期文章

站点统计

  • 0
  • 23
  • 58
  • 1
  • 59

归档

分类

Java (4) Linux (2) MySQL (44) 网站建设 (5) 读书笔记 (4)