文章目录第一章 用户与权限体系概述第二章 用户创建与属性配置2.1 创建用户的基本语法2.1.1 密码加密机制2.2 用户信息存储的本质第三章 密码管理与安全策略3.1 修改用户密码3.1.1 SET PASSWORD 与 PASSWORD() 函数的兼容性3.1.2 MySQL 8.0 及之后版本的正确做法第四章 权限分配与回收4.1 给用户授权GRANT4.2 查看用户权限4.3 刷新权限FLUSH PRIVILEGES4.4 回收用户权限REVOKE第五章 用户删除与管理5.1 删除用户DROP USER5.1.1 关于 % 通配符的说明第六章 常见问题排查与特殊场景6.1 MySQL 配置参数 skip-grant-tables 的影响第七章 最佳实践总结第一章 用户与权限体系概述在生产环境中直接使用root用户操作数据库是一项高风险行为。root用户拥有无限权限一旦密码泄露或发生误操作后果不堪设想。因此建立精细化的用户管理与权限控制体系是数据库安全的第一道防线。MySQL 将所有用户信息和权限数据存储在系统数据库mysql的user表中。这张表是 MySQL 权限体系的核心我们可以通过查询它来了解当前所有用户的基本情况。-- 切换到 mysql 数据库USEmysql;-- 查看用户的基本信息主机、用户名、密码密文SELECThost,user,authentication_stringFROMuser;查询结果中的关键字段含义如下host表示该用户允许从哪个主机或 IP 登录。localhost代表仅限本机登录%则是通配符代表允许从任意主机远程登录。user登录用户名。authentication_string/ɔːˌθentɪˈkeɪʃən strɪŋ/用户密码经过加密算法处理后的密文字符串。需要注意的是mysql.user表中的权限是全局级别的。一个用户能否连接服务器取决于host、user和password这三者的组合是否匹配。第二章 用户创建与属性配置2.1 创建用户的基本语法创建用户的标准 SQL 语句是CREATE USER。CREATEUSER用户名登录主机/主机名IDENTIFIEDBY密码;用户名登录主机/主机名这是 MySQL 中用户的唯一标识。MySQL 通过“用户名 主机名”的组合来区分不同的账户。如果省略主机名部分则默认为%即允许从任何主机登录。IDENTIFIED BY 密码用于设置该用户的登录密码。2.1.1 密码加密机制这是一个非常关键的问题。在CREATE USER语句中我们直接输入的是明文密码。这个明文密码在存入mysql.user表的authentication_string字段之前会由 MySQL 服务器自动进行加密处理。开发者不需要也不应该手动调用加密函数。MySQL 会根据当前配置的认证插件如mysql_native_password或caching_sha2_password自动完成加密。案例创建一个新用户-- 创建一个名为 app_user只允许从本机 (localhost) 登录的用户CREATEUSERapp_userlocalhostIDENTIFIEDBYStrongPssw0rd!;-- 创建一个名为 remote_user允许从任何主机 (%) 登录的用户CREATEUSERremote_user%IDENTIFIEDBYAnotherPss!;-- 查看创建后的用户信息密码已被自动加密SELECThost,user,authentication_stringFROMuserWHEREuserIN(app_user,remote_user);安全提示不要轻易添加一个可以从任意地方登录%的用户这会显著增加被攻击的风险。2.2 用户信息存储的本质“创建用户是不是就是在mysql.user表里进行 CURD 操作”这个问题触及了用户管理的本质。答案是从最终效果上看是的。但官方强烈推荐使用专门的 SQL 语句如CREATE USER而不是直接对mysql.user表进行INSERT、UPDATE或DELETE操作。直接操作系统表风险极高容易破坏数据完整性或导致权限系统混乱。使用CREATE USER、DROP USER等专用 DDL 语句是更安全、更规范的做法。第三章 密码管理与安全策略3.1 修改用户密码修改用户密码有两种常见场景1. 用户修改自己的密码在 MySQL 5.7 及更早版本中可以使用SETPASSWORDPASSWORD(新密码);2. 管理员如 root修改指定用户的密码同样在旧版本中可以使用SETPASSWORDFOR用户名主机名PASSWORD(新密码);3.1.1SET PASSWORD与PASSWORD()函数的兼容性“改密码语法在新版本里面是否已失效”结论是在 MySQL 8.0 中PASSWORD()函数已被移除SET PASSWORD ... PASSWORD(...)的语法已不再可用。MySQL 5.7PASSWORD()函数已被标记为废弃deprecated但仍可使用。MySQL 8.0PASSWORD()函数被彻底移除上述语法会直接报错。3.1.2 MySQL 8.0 及之后版本的正确做法在 MySQL 8.0 中修改密码统一使用 ALTER USER 用户主机 IDENTIFIED BY 新密码普通用户修改自己的密码和管理员修改其他用户密码语法相同只是权限不同。-- 修改自己的密码ALTERUSER用户名主机名IDENTIFIEDBY新密码;-- root 用户修改指定用户的密码ALTERUSERapp_userlocalhostIDENTIFIEDBYNewStrongPss!;SET PASSWORD语句本身在 MySQL 8.0 中并未被完全废弃但其用法已经改变。现在SET PASSWORD在内部会被重写为ALTER USER语句。为了更好的兼容性和清晰性直接使用ALTER USER是官方推荐的最佳实践。第四章 权限分配与回收4.1 给用户授权GRANT新创建的用户默认没有任何权限。我们需要使用GRANT语句来赋予其操作数据库的权限。授权语法GRANT权限列表ON库.对象名TO用户名登录位置[IDENTIFIEDBY密码];权限列表要授予的权限如SELECT,INSERT,UPDATE,DELETE等多个权限用逗号分隔。使用ALL [PRIVILEGES]表示授予该对象上的所有权限。库.对象名权限作用范围。*.*代表本系统中的所有数据库及其所有对象。库名.*代表指定数据库中的所有对象表、视图、存储过程等。库名.表名代表指定数据库中的特定表。[IDENTIFIED BY 密码]可选。如果用户存在则在授权的同时修改其密码如果用户不存在则创建该用户。案例授予查询权限-- 使用 root 账号-- 授予 app_userlocalhost 对 test 数据库下所有表的 SELECT 权限GRANTSELECTONtest.*TOapp_userlocalhost;-- 授予 app_userlocalhost 对 test 数据库的 SELECT, INSERT, UPDATE 权限GRANTSELECT,INSERT,UPDATEONtest.*TOapp_userlocalhost;-- 授予 remote_user% 对 test 数据库的所有权限GRANTALLPRIVILEGESONtest.*TOremote_user%;授权后用户需要重新连接才能使部分权限生效或者执行权限刷新操作。4.2 查看用户权限可以使用SHOW GRANTS命令查看特定用户的权限。-- 查看 app_userlocalhost 的权限SHOWGRANTSFORapp_userlocalhost;4.3 刷新权限FLUSH PRIVILEGES截图中的flush privileges;命令其核心作用是重新从mysql系统库的权限表中加载最新的用户信息和权限设置到内存中。何时需要执行正常情况下使用GRANT、REVOKE、CREATE USER、DROP USER等专用 SQL 语句修改权限后MySQL 会自动感知变更并更新内存中的缓存通常不需要手动执行FLUSH PRIVILEGES。特殊情况下如果通过INSERT、UPDATE、DELETE等语句直接修改了mysql.user等系统权限表那么内存中的权限缓存并不会自动更新。此时必须执行FLUSH PRIVILEGES来强制服务器重新加载权限表使更改生效。4.4 回收用户权限REVOKEREVOKE语句用于回收已授予用户的权限。回收权限语法REVOKE权限列表ON库.对象名FROM用户名登录位置;案例回收权限-- 使用 root 账号-- 回收 app_userlocalhost 对 test 数据库的所有权限REVOKEALLONtest.*FROMapp_userlocalhost;第五章 用户删除与管理5.1 删除用户DROP USERDROP USER语句用于移除一个或多个 MySQL 账户。删除用户语法DROPUSER用户名主机名;5.1.1 关于%通配符的说明删除用户时必须指定完整主机名的情况。MySQL 中whb和whb%是两个完全不同的账户。如果执行DROP USER whb;MySQL 会默认将主机名部分视为%即尝试删除whb%这个用户。如果该用户不存在就会报错ERROR 1396 (HY000)。要删除whblocalhost必须精确指定完整名称。-- 错误示例尝试删除 whb但默认匹配的是 whb%DROPUSERwhb;-- 如果 whb% 不存在则报错-- 正确示例精确指定主机名DROPUSERwhblocalhost;第六章 常见问题排查与特殊场景6.1 MySQL 配置参数skip-grant-tables的影响“MySQL 配置文件选项会让 netstat -nltp 查不到”这个现象与skip-grant-tables参数有关。功能当 MySQL 服务器以--skip-grant-tables选项启动时它将不读取权限表也不实施任何访问控制。这意味着任何用户都可以无密码登录并拥有所有权限通常用于紧急情况下的密码重置。副作用启用该参数后MySQL 为了安全考虑会默认开启skip-networking即禁用所有 TCP/IP 网络连接。服务器仅允许本地 socket 连接因此使用netstat -nltp命令将无法看到 MySQL 服务监听在 3306 端口上。解决方法在完成紧急操作后务必从配置文件如/etc/my.cnf中移除或注释掉skip-grant-tables这一行然后重启 MySQL 服务恢复正常权限检查和网络监听。第七章 最佳实践总结最小权限原则仅授予用户完成其工作所必需的最小权限集。避免随意授予ALL PRIVILEGES。限制登录主机尽量指定具体的 IP 或主机名避免使用%通配符以减少攻击面。使用专用 DDL 语句始终使用CREATE USER、GRANT、REVOKE、DROP USER等专用语句管理用户和权限不要直接修改mysql.user表。关注版本差异注意 MySQL 不同版本间的语法差异尤其是在 8.0 版本中应使用ALTER USER来修改密码。谨慎使用维护参数skip-grant-tables仅用于紧急故障恢复切忌在生产环境中长期开启。
