文章目录用户管理数据库安全的基石一、前言二、为什么需要用户管理2.1 只用 root 的问题2.2 用户管理的好处三、用户信息3.1 用户存储位置3.2 关键字段说明3.3 查看表结构四、用户操作4.1 创建用户基本语法示例一创建本地用户示例二创建远程用户host 的含义密码策略问题4.2 删除用户基本语法示例4.3 修改密码方式一修改自己的密码方式二root 修改其他用户密码MySQL 8.0 的变化五、权限管理5.1 MySQL 权限列表5.2 授予权限基本语法示例一授予查询权限示例二授予多个权限示例三授予所有权限示例四授予特定表的权限5.3 查看权限5.4 回收权限基本语法示例六、实战场景6.1 场景一开发环境6.2 场景二生产环境6.3 场景三只读用户6.4 场景四备份用户七、安全最佳实践7.1 用户管理原则7.2 权限分配建议7.3 定期维护八、常见问题8.1 权限不生效8.2 无法远程连接8.3 忘记 root 密码九、总结9.1 快速参考用户管理数据库安全的基石一、前言这一篇讲什么MySQL 用户和权限管理核心内容为什么需要用户管理如何创建和管理用户如何授予和回收权限用户管理的最佳实践在实际项目中不能让所有人都使用 root 用户访问数据库。这存在巨大的安全隐患。MySQL 提供了完善的用户和权限管理机制可以为不同的用户分配不同的权限确保数据库的安全性。二、为什么需要用户管理2.1 只用 root 的问题场景一个公司的数据库系统。如果只用 root 用户开发人员可以删除生产数据库。测试人员可以修改用户密码。运维人员可以看到所有敏感数据。一旦密码泄露整个系统沦陷。2.2 用户管理的好处好处说明最小权限原则每个用户只有完成工作所需的最小权限职责分离开发、测试、运维使用不同账号审计追踪可以追踪是谁进行了什么操作降低风险单个账号泄露不会影响整个系统三、用户信息3.1 用户存储位置MySQL 的所有用户信息都存储在系统数据库mysql的user表中。-- 切换到 mysql 数据库USEmysql;-- 查看用户信息SELECThost,user,authentication_stringFROMuser;输出---------------------------------------------------------------------|host|user|authentication_string|---------------------------------------------------------------------|localhost|root|*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B||localhost|mysql.session|*THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE||localhost|mysql.sys|*THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE|---------------------------------------------------------------------3.2 关键字段说明字段说明例子host允许登录的主机localhost本机、%任意主机、192.168.1.100指定 IPuser用户名root、admin、developerauthentication_string加密后的密码通过PASSWORD()函数加密*_priv各种权限Select_priv、Insert_priv、Update_priv等3.3 查看表结构-- 查看 user 表的完整结构DESCuser;输出部分----------------------------------------------------------------------|Field|Type|Null|Key|----------------------------------------------------------------------|Host|char(60)|NO|PRI||User|char(32)|NO|PRI||Select_priv|enum(N,Y)|NO|||Insert_priv|enum(N,Y)|NO|||Update_priv|enum(N,Y)|NO|||Delete_priv|enum(N,Y)|NO|||Create_priv|enum(N,Y)|NO|||Drop_priv|enum(N,Y)|NO|||...|...|...|...|----------------------------------------------------------------------四、用户操作4.1 创建用户基本语法CREATEUSER用户名登录主机IDENTIFIEDBY密码;示例一创建本地用户-- 创建只能从本机登录的用户CREATEUSERdeveloperlocalhostIDENTIFIEDBYDev12345;-- 查看创建结果SELECTuser,host,authentication_stringFROMuserWHEREuserdeveloper;输出-----------------------------------------------------------------|user|host|authentication_string|-----------------------------------------------------------------|developer|localhost|*84AAC12F54AB666ECFC2A83C676908C8BBC381B1|-----------------------------------------------------------------示例二创建远程用户-- 创建可以从任意主机登录的用户CREATEUSERremote_user%IDENTIFIEDBYRemote12345;-- 创建只能从特定 IP 登录的用户CREATEUSERadmin192.168.1.100IDENTIFIEDBYAdmin12345;host 的含义host 值说明安全性localhost只能从本机登录✅ 最安全192.168.1.100只能从指定 IP 登录✅ 安全192.168.1.%可以从 192.168.1.* 网段登录⚠️ 中等%可以从任意主机登录❌ 不安全密码策略问题如果创建用户时遇到以下错误ERROR1819(HY000): Your password does not satisfy the current policy requirements原因MySQL 的密码策略要求较高。查看密码策略SHOWVARIABLESLIKEvalidate_password%;输出----------------------------------------------|Variable_name|Value|----------------------------------------------|validate_password_check_user_name|OFF||validate_password_length|8||validate_password_mixed_case_count|1||validate_password_number_count|1||validate_password_policy|MEDIUM||validate_password_special_char_count|1|----------------------------------------------临时降低密码策略不推荐在生产环境SETGLOBALvalidate_password_policyLOW;SETGLOBALvalidate_password_length6;4.2 删除用户基本语法DROPUSER用户名主机名;示例-- 查看当前用户SELECTuser,hostFROMuser;-- 删除用户必须指定 hostDROPUSERdeveloperlocalhost;-- 错误示例不指定 hostDROPUSERdeveloper;-- 默认会尝试删除 developer%可能失败输出ERROR1396(HY000): Operation DROPUSERfailedfordeveloper%说明如果不指定hostMySQL 默认使用%如果该用户不存在就会报错。4.3 修改密码方式一修改自己的密码-- 当前用户修改自己的密码SETPASSWORDPASSWORD(新密码);方式二root 修改其他用户密码-- root 用户修改指定用户的密码SETPASSWORDFORdeveloperlocalhostPASSWORD(NewPass123);-- 验证修改SELECTuser,host,authentication_stringFROMuserWHEREuserdeveloper;输出-----------------------------------------------------------------|user|host|authentication_string|-----------------------------------------------------------------|developer|localhost|*5D24C4D94238E65A6407DFAB95AA4EA97CA2B199|-----------------------------------------------------------------说明密码已经改变authentication_string 不同了。MySQL 8.0 的变化在 MySQL 8.0 中PASSWORD()函数已被移除使用新的语法-- MySQL 8.0 修改密码ALTERUSERdeveloperlocalhostIDENTIFIEDBY新密码;五、权限管理5.1 MySQL 权限列表权限说明作用范围ALL所有权限数据库、表SELECT查询数据表、列INSERT插入数据表、列UPDATE更新数据表、列DELETE删除数据表CREATE创建数据库/表数据库、表DROP删除数据库/表数据库、表ALTER修改表结构表INDEX创建/删除索引表EXECUTE执行存储过程存储过程CREATE VIEW创建视图视图GRANT OPTION授权给其他用户所有5.2 授予权限基本语法GRANT权限列表ON数据库.对象TO用户名主机[IDENTIFIEDBY密码];语法说明权限列表多个权限用逗号分隔或使用ALL。数据库.对象*.*所有数据库的所有对象test.*test 数据库的所有对象test.usertest 数据库的 user 表IDENTIFIED BY可选如果用户不存在则创建。示例一授予查询权限场景给开发人员只读权限。-- 创建用户CREATEUSERdeveloperlocalhostIDENTIFIEDBYDev123;-- 授予 test 数据库的查询权限GRANTSELECTONtest.*TOdeveloperlocalhost;-- 刷新权限可选FLUSHPRIVILEGES;验证-- 使用 developer 用户登录mysql-u developer-p-- 查看可访问的数据库SHOWDATABASES;输出--------------------|Database|--------------------|information_schema||test|---------------------- 使用 test 数据库USEtest;-- 查询数据成功SELECT*FROMuser;-- 尝试删除失败DELETEFROMuserWHEREid1;输出ERROR1142(42000): DELETEcommanddenied to userdeveloperlocalhostfortableuser示例二授予多个权限-- 授予查询、插入、更新权限GRANTSELECT,INSERT,UPDATEONtest.*TOdeveloperlocalhost;示例三授予所有权限-- 授予 test 数据库的所有权限GRANTALLPRIVILEGESONtest.*TOdeveloperlocalhost;-- 授予所有数据库的所有权限谨慎使用GRANTALLPRIVILEGESON*.*TOadminlocalhost;示例四授予特定表的权限-- 只授予 test.user 表的查询权限GRANTSELECTONtest.userTOdeveloperlocalhost;-- 授予特定列的权限GRANTSELECT(id,name)ONtest.userTOdeveloperlocalhost;5.3 查看权限-- 查看当前用户的权限SHOWGRANTS;-- 查看指定用户的权限SHOWGRANTSFORdeveloperlocalhost;输出---------------------------------------------------------------|Grantsfordeveloperlocalhost|---------------------------------------------------------------|GRANT USAGE ON *.* TOdeveloperlocalhost||GRANT SELECT, INSERT, UPDATE ONtest.* TOdeveloperlocalhost|---------------------------------------------------------------说明GRANT USAGE表示可以登录但没有其他权限。第二行显示了在 test 数据库上的具体权限。5.4 回收权限基本语法REVOKE权限列表ON数据库.对象FROM用户名主机;示例-- 回收 developer 在 test 数据库上的所有权限REVOKEALLPRIVILEGESONtest.*FROMdeveloperlocalhost;-- 刷新权限FLUSHPRIVILEGES;验证-- 使用 developer 用户登录SHOWDATABASES;输出--------------------|Database|--------------------|information_schema|--------------------test 数据库已经看不到了。六、实战场景6.1 场景一开发环境需求开发人员需要对开发数据库有完全控制权。-- 创建开发用户CREATEUSERdev_user%IDENTIFIEDBYDevPass123;-- 授予开发数据库的所有权限GRANTALLPRIVILEGESONdev_db.*TOdev_user%;-- 允许创建临时表GRANTCREATETEMPORARYTABLESONdev_db.*TOdev_user%;6.2 场景二生产环境需求应用程序只需要读写数据不能修改表结构。-- 创建应用用户CREATEUSERapp_user192.168.1.%IDENTIFIEDBYAppPass123;-- 授予数据操作权限不包括 DDLGRANTSELECT,INSERT,UPDATE,DELETEONprod_db.*TOapp_user192.168.1.%;6.3 场景三只读用户需求数据分析师只需要查询权限。-- 创建只读用户CREATEUSERanalystlocalhostIDENTIFIEDBYAnalyst123;-- 授予查询权限GRANTSELECTONprod_db.*TOanalystlocalhost;-- 授予查看视图的权限GRANTSHOWVIEWONprod_db.*TOanalystlocalhost;6.4 场景四备份用户需求备份程序需要读取所有数据。-- 创建备份用户CREATEUSERbackup_userlocalhostIDENTIFIEDBYBackup123;-- 授予全局查询权限GRANTSELECT,LOCKTABLES,SHOWVIEWON*.*TObackup_userlocalhost;-- 授予 RELOAD 权限用于 FLUSH TABLESGRANTRELOADON*.*TObackup_userlocalhost;七、安全最佳实践7.1 用户管理原则✅ 应该做为每个应用/服务创建独立用户。使用强密码大小写字母数字特殊字符。限制用户的登录主机不要用%。定期审查和清理不用的用户。遵循最小权限原则。❌ 不应该做在生产环境使用 root 用户。创建user%这样的用户。授予不必要的权限。使用简单密码。多个应用共用一个用户。7.2 权限分配建议角色推荐权限说明应用程序SELECT, INSERT, UPDATE, DELETE只能操作数据开发人员ALL ON dev_db.*只在开发库有完全权限DBAALL ON.WITH GRANT OPTION完全控制权只读用户SELECT数据分析、报表备份程序SELECT, LOCK TABLES, RELOAD备份所需的最小权限7.3 定期维护-- 查看所有用户SELECTuser,hostFROMmysql.user;-- 查看长时间未使用的用户SELECTuser,host,password_last_changedFROMmysql.userWHEREpassword_last_changedDATE_SUB(NOW(),INTERVAL90DAY);-- 删除测试用户DROPUSERtest_userlocalhost;-- 定期修改密码ALTERUSERapp_userlocalhostIDENTIFIEDBY新密码;八、常见问题8.1 权限不生效问题授权后用户仍然没有权限。解决FLUSHPRIVILEGES;8.2 无法远程连接问题创建了user%但无法远程连接。原因MySQL 配置文件限制了远程连接。防火墙阻止了 3306 端口。解决# 修改 MySQL 配置# /etc/mysql/mysql.conf.d/mysqld.cnfbind-address0.0.0.0# 重启 MySQLsudosystemctl restart mysql# 开放防火墙端口sudoufw allow33068.3 忘记 root 密码解决步骤# 1. 停止 MySQLsudosystemctl stop mysql# 2. 跳过权限验证启动sudomysqld_safe --skip-grant-tables# 3. 无密码登录mysql-uroot# 4. 修改密码USE mysql;UPDATE user SETauthentication_stringPASSWORD(新密码)WHEREuserroot;FLUSH PRIVILEGES;# 5. 重启 MySQLsudosystemctl restart mysql九、总结现在你已经掌握了✅用户信息用户存储在 mysql.user 表中✅创建用户CREATE USER 语法和 host 的含义✅删除用户DROP USER 必须指定 host✅修改密码自己改和 root 改的方式✅权限列表SELECT、INSERT、UPDATE、DELETE 等✅授予权限GRANT 语法和各种场景✅查看权限SHOW GRANTS 命令✅回收权限REVOKE 语法✅实战场景开发、生产、只读、备份用户的配置✅最佳实践安全原则和定期维护9.1 快速参考-- 创建用户CREATEUSERusernamehostIDENTIFIEDBYpassword;-- 授予权限GRANTprivilegesONdatabase.tableTOusernamehost;-- 查看权限SHOWGRANTSFORusernamehost;-- 回收权限REVOKEprivilegesONdatabase.tableFROMusernamehost;-- 删除用户DROPUSERusernamehost;-- 刷新权限FLUSHPRIVILEGES;