技术 · 思考 · 实践 · 成长

首页/MySQL8专栏/正文

MySQL 8 用户与权限管理详解:账号、角色、认证与安全实践

MySQL8专栏553 次阅读

MySQL 8 的用户管理不仅是创建账号和设置密码,还包括登录来源、认证插件、权限边界、角色、密码策略、连接限制与账号审计。生产环境应遵循“先创建账号,再按需授权,定期复核,及时回收”的原则。

一、理解 MySQL 账号模型

MySQL 账号由用户名和主机名共同确定,标准形式是:

'username'@'host'

下面三个账号是彼此独立的:

'app'@'localhost'
'app'@'192.168.10.%'
'app'@'%'
  • localhost:只允许从数据库服务器本机连接。
  • 192.168.10.%:允许指定网段连接。
  • %:允许任意来源连接,生产环境应谨慎使用。

当多个账号都能匹配同一次连接时,MySQL 会选择更具体的主机规则。因此排查“密码正确却无法登录”时,应先确认服务器最终匹配的是哪一个 user@host。

二、查看现有用户

SELECT User, Host, plugin, account_locked, password_expired
FROM mysql.user
ORDER BY User, Host;

查看当前登录身份和权限匹配身份:

SELECT USER(), CURRENT_USER();
  • USER():客户端提交的用户名和来源。
  • CURRENT_USER():MySQL 实际用于权限检查的账号。

三、创建用户

3.1 创建本机用户

CREATE USER 'report'@'localhost'
IDENTIFIED BY 'Use-A-Strong-Password!';

3.2 限制来源地址

CREATE USER 'app_user'@'192.168.10.%'
IDENTIFIED BY 'Use-A-Strong-Password!';

应用账号应尽量限定到具体服务器或网段,不要为了方便直接使用 '%'。

3.3 明确认证插件

MySQL 8.0 通常使用 caching_sha2_password。新版本驱动一般都支持该插件,老旧客户端连接失败时应优先升级驱动,而不是直接降低认证安全性。

CREATE USER 'api_user'@'10.0.0.%'
IDENTIFIED WITH caching_sha2_password
BY 'Use-A-Strong-Password!';

查看服务器默认认证插件:

SHOW VARIABLES LIKE 'default_authentication_plugin';

3.4 创建时增加安全约束

CREATE USER 'analyst'@'192.168.20.%'
IDENTIFIED BY 'Use-A-Strong-Password!'
PASSWORD EXPIRE INTERVAL 90 DAY
FAILED_LOGIN_ATTEMPTS 5
PASSWORD_LOCK_TIME 2
COMMENT '数据分析只读账号';

不同 MySQL 8.0 小版本支持的账号属性可能不同,上线前应以当前实例的官方文档和实际测试为准。

四、修改用户与密码

4.1 修改密码

ALTER USER 'app_user'@'192.168.10.%'
IDENTIFIED BY 'New-Strong-Password!';

4.2 当前用户修改自己的密码

ALTER USER USER() IDENTIFIED BY 'New-Strong-Password!';

4.3 设置密码过期策略

-- 下次登录必须修改密码
ALTER USER 'report'@'localhost' PASSWORD EXPIRE;

-- 每 90 天过期
ALTER USER 'report'@'localhost'
PASSWORD EXPIRE INTERVAL 90 DAY;

-- 密码永不过期,不建议用于普通人工账号
ALTER USER 'service_account'@'10.0.0.%'
PASSWORD EXPIRE NEVER;

4.4 设置密码历史与重用间隔

ALTER USER 'report'@'localhost'
PASSWORD HISTORY 5
PASSWORD REUSE INTERVAL 365 DAY;

五、锁定、解锁和删除用户

5.1 手动锁定与解锁

ALTER USER 'report'@'localhost' ACCOUNT LOCK;
ALTER USER 'report'@'localhost' ACCOUNT UNLOCK;

人员离职或账号暂时停用时,优先锁定并观察,再根据管理制度删除账号。

5.2 重命名用户

RENAME USER
'report'@'localhost'
TO 'report_readonly'@'localhost';

5.3 删除用户

DROP USER IF EXISTS 'report_readonly'@'localhost';

不要直接删除 mysql.user 表中的记录。使用 DROP USER 可以让 MySQL 正确处理相关权限数据。

六、权限管理基础

MySQL 权限可作用于全局、数据库、表、列、存储程序等不同层级。授权范围越小,账号泄露后的影响越有限。

6.1 数据库级权限

GRANT SELECT, INSERT, UPDATE, DELETE
ON app_db.*
TO 'app_user'@'192.168.10.%';

6.2 只读账号

GRANT SELECT, SHOW VIEW
ON app_db.*
TO 'analyst'@'192.168.20.%';

6.3 表级和列级权限

GRANT SELECT
ON app_db.orders
TO 'auditor'@'10.0.0.%';

GRANT SELECT (id, order_no, created_at)
ON app_db.orders
TO 'limited_reader'@'10.0.0.%';

6.4 查看权限

SHOW GRANTS FOR 'app_user'@'192.168.10.%';
SHOW GRANTS FOR CURRENT_USER();

6.5 回收权限

REVOKE DELETE
ON app_db.*
FROM 'app_user'@'192.168.10.%';

REVOKE ALL PRIVILEGES, GRANT OPTION
FROM 'temporary_user'@'10.0.0.%';

MySQL 8 中应先使用 CREATE USER 创建账号,再使用 GRANT 授权。不要依赖旧版本中“GRANT 顺便创建用户”的写法。

七、使用角色简化权限管理

角色是一组可复用的权限集合。多人拥有相同权限时,使用角色比逐个账号授权更容易维护。

7.1 创建角色并授权

CREATE ROLE 'app_readonly', 'app_writer';

GRANT SELECT, SHOW VIEW
ON app_db.*
TO 'app_readonly';

GRANT SELECT, INSERT, UPDATE, DELETE
ON app_db.*
TO 'app_writer';

7.2 把角色授予用户

GRANT 'app_readonly'
TO 'analyst'@'192.168.20.%';

GRANT 'app_writer'
TO 'app_user'@'192.168.10.%';

7.3 设置默认角色

SET DEFAULT ROLE 'app_readonly'
TO 'analyst'@'192.168.20.%';

如果没有设置默认角色,用户登录后可能需要执行 SET ROLE 才能激活角色权限。

7.4 查看当前角色

SELECT CURRENT_ROLE();
SHOW GRANTS FOR 'analyst'@'192.168.20.%';

八、TLS 与连接安全

远程连接应尽量启用 TLS,避免账号和业务数据在网络中明文传输。

-- 要求加密连接
ALTER USER 'app_user'@'192.168.10.%' REQUIRE SSL;

-- 要求客户端证书
ALTER USER 'admin_remote'@'10.0.0.%' REQUIRE X509;

-- 取消证书要求
ALTER USER 'app_user'@'192.168.10.%' REQUIRE NONE;

查看当前连接是否使用 TLS:

SHOW STATUS LIKE 'Ssl_cipher';

九、限制账号资源

可以限制单个账号的查询、更新和并发连接数量,降低异常程序耗尽数据库资源的风险。

ALTER USER 'api_user'@'10.0.0.%'
WITH MAX_QUERIES_PER_HOUR 10000
     MAX_UPDATES_PER_HOUR 3000
     MAX_CONNECTIONS_PER_HOUR 1000
     MAX_USER_CONNECTIONS 20;

资源限制不是完整的限流系统,仍应结合连接池、应用限流、监控和告警使用。

十、常用账号设计示例

10.1 应用读写账号

CREATE USER 'shop_app'@'10.10.1.%'
IDENTIFIED BY 'Use-A-Strong-Password!'
REQUIRE SSL;

GRANT SELECT, INSERT, UPDATE, DELETE
ON shop.*
TO 'shop_app'@'10.10.1.%';

10.2 备份账号

CREATE USER 'backup'@'localhost'
IDENTIFIED BY 'Use-A-Strong-Password!';

GRANT SELECT, SHOW VIEW, TRIGGER, EVENT,
      LOCK TABLES, RELOAD, PROCESS,
      REPLICATION CLIENT
ON *.*
TO 'backup'@'localhost';

备份工具、备份参数和 MySQL 版本不同,所需权限也不同。应通过测试确认后再收敛到最小权限。

10.3 运维只读账号

CREATE USER 'ops_readonly'@'10.20.0.%'
IDENTIFIED BY 'Use-A-Strong-Password!'
REQUIRE SSL;

GRANT SELECT, SHOW VIEW, PROCESS,
      REPLICATION CLIENT
ON *.*
TO 'ops_readonly'@'10.20.0.%';

十一、用户管理常见问题

11.1 Access denied

按顺序检查:

  1. 账号是否以正确的 user@host 形式存在。
  2. 客户端来源 IP 是否匹配 Host 规则。
  3. 密码是否正确,账号是否锁定或密码过期。
  4. 客户端驱动是否支持当前认证插件。
  5. 是否要求 TLS,而客户端未启用加密连接。

11.2 能登录但无法操作表

SELECT USER(), CURRENT_USER(), CURRENT_ROLE();
SHOW GRANTS FOR CURRENT_USER();

重点检查数据库名、授权层级、角色是否激活,以及应用是否连接到了预期实例。

11.3 修改权限后是否需要 FLUSH PRIVILEGES

使用 CREATE USER、ALTER USER、GRANT、REVOKE 和 DROP USER 等标准语句时,权限会立即生效,通常不需要执行 FLUSH PRIVILEGES。只有直接修改授权表时才可能需要刷新,而生产环境不建议直接修改系统授权表。

十二、安全管理建议

  • 应用禁止使用 root 账号连接数据库。
  • 为不同应用、环境和用途创建独立账号。
  • 限制 Host 范围,尽量避免使用 %。
  • 只授予业务真正需要的权限,不轻易授予 GRANT OPTION。
  • 远程连接启用 TLS,并保护好证书和私钥。
  • 密码不要写入代码仓库,使用密钥管理服务或受控配置文件。
  • 定期检查长期未使用、离职人员和临时账号。
  • 监控登录失败、权限变更和异常连接数量。
  • 权限调整前后保存 SHOW GRANTS 结果,便于审计和回滚。

十三、总结

MySQL 8 用户管理的核心是身份、来源和权限三者的组合。先用 CREATE USER 建立明确的账号,再通过角色或 GRANT 分配最小权限,同时配合密码策略、账号锁定、TLS 和资源限制,才能形成可维护的安全体系。

日常管理中最值得坚持的习惯是:不共用账号、不使用 root 跑应用、不授予多余权限,并定期复核账号与授权。

人生于世,就得有点兴致,闲暇之时,或乐游山水、或尽享食味、或寄情岁时,它让我们的生活变得充盈。

评论交流

0 条评论 · 支持多级回复
验证码
暂无评论,来说说你的想法吧

相关文章

继续阅读