MySQL

MySQL-05-用户管理

MySQL 用户管理围绕 “账号 - 角色 - 权限” 三层架构展开，通过身份认证确认用户合法性，通过精细化权限分配控制资源访问，支持插件化认证、密码生命周期管理等企业级特性，保障数据库安全与合规。

  1. 创建并管理用户账号与角色，实现权限的批量分配与回收
  2. 设计合理的权限结构，遵循 “最小权限原则” 分配访问权限
  3. 配置身份认证方式，支持本地 / 远程连接及企业级认证（如 PAM、LDAP）
  4. 管控账号安全，包括密码设置、过期策略、双重密码等
  5. 监控与调整用户权限，适配业务场景变化

MySQL 权限系统基础

核心作用

  • 控制用户对数据库的操作范围，避免越权访问
  • 区分管理员与普通用户权限，降低误操作风险
  • 保障数据完整性，防止未授权的数据修改或泄露
  • 支持多场景访问控制，适配本地 / 远程、应用 / 人工等不同连接需求

身份认证与授权流程

  1. 身份认证（连接阶段）：验证用户 “用户名 + 主机 + 密码 / 认证信息” 的合法性，认证失败直接拒绝连接
  2. 授权校验（查询阶段）：对已认证用户的每个操作，校验是否具备对应权限，无权限则拒绝执行

用户账号与角色管理

账号命名规则

  • 完整格式：'username'@'hostname'，用户名和主机名共同唯一标识一个账号
  • 用户名限制：最长 32 字符，含特殊字符（如短横线）需用单引号包裹
  • 主机名格式（支持多种匹配方式）：
    • 具体主机名：server1、server2.example.com
    • IP 地址：192.168.9.78
    • IP 网段：10.0.0.0/255.255.255.0
    • 通配符：%（匹配任意多个字符）、_（匹配单个字符），例：'%.example.com'（匹配该域名下所有主机）、'192.168._.7'
    • 默认值：创建账号时未指定主机名，默认为%（允许从任意主机连接）

账号创建与修改

创建账号

  • 基础语法：CREATE USER 'username'@'hostname' IDENTIFIED BY 'password';

  • 示例：创建本地连接的 web 用户，密码为 Abc123

    1

    CREATE USER webuser@localhost IDENTIFIED BY 'Abc123';

  • 安全注意事项：

    • 禁止创建无密码账号
    • 避免匿名账号（如''@localhost，无用户名）
    • 主机名尽量不使用通配符%，限制连接来源

账号重命名

  • 语法：RENAME USER 'old_user'@'old_host' TO 'new_user'@'new_host';
  • 示例：RENAME USER consultant@laptop3 TO james@laptop3;

账号删除

  • 语法：DROP USER 'username'@'hostname';
  • 作用：删除账号及其所有权限，同时从所有授权表中移除该账号记录
  • 示例：DROP USER james@laptop3;

角色管理（权限集合）

角色的核心特性

  • 本质：一组权限的集合，可批量授予用户或其他角色
  • 存储：与用户账号一样存储在mysql.user表中，但不能用于登录
  • 命名格式：同用户账号（'rolename'@'hostname'），省略主机名默认为%

角色创建与删除

  • 创建角色：

    1

    CREATE ROLE 'rolename'@'hostname' [, ...];

    • 示例：创建管理员角色和开发角色

      1

      CREATE ROLE r_admin, r_dev@localhost;

  • 删除角色：

    1

    DROP ROLE 'rolename'@'hostname' [, ...];
    • 作用：删除角色的同时，回收所有被授予该角色的用户 / 角色的相关权限

密码与账号安全管理

密码设置与修改

设置密码（创建 / 修改时）

  • 创建账号时指定密码：CREATE USER 'user'@'host' IDENTIFIED BY 'newpassword';
  • 修改已有账号密码：
    • 语法 1：ALTER USER 'user'@'host' IDENTIFIED BY 'newpassword';
    • 语法 2：SET PASSWORD FOR 'user'@'host' = 'newpassword';
  • 修改当前登录用户密码：
    • ALTER USER USER() IDENTIFIED BY 'newpassword';
    • SET PASSWORD = 'newpassword';
    • 命令行方式：mysqladmin -u username -p password 'newpassword'（执行时需输入旧密码）

双重密码支持（MySQL 8.0.14+）

  • 作用：密码变更时保留旧密码（次要密码），避免多系统 / 应用同步更新时出现中断

  • 设置方法：

    1

    ALTER USER USER() IDENTIFIED BY 'new_password' RETAIN CURRENT PASSWORD;
    • 新密码成为主密码，原密码变为次要密码

  • 移除次要密码：ALTER USER USER() DISCARD OLD PASSWORD;

密码过期管理

手动设置密码过期

  • 创建账号时直接过期：CREATE USER 'erika'@'localhost' IDENTIFIED BY 'first#Pass' PASSWORD EXPIRE;
  • 手动过期已有账号：ALTER USER 'erika'@'localhost' PASSWORD EXPIRE;
  • 效果：用户可登录，但必须先修改密码才能执行其他操作

自动过期配置

  • 全局默认策略：通过default_password_lifetime变量设置（默认 0，永不过期）

  • 账号级配置（优先级高于全局）：

    • 设置 30 天过期：CREATE USER 'consultant'@'laptop3' IDENTIFIED BY 'change_me' PASSWORD EXPIRE INTERVAL 30 DAY;

    • 沿用全局默认：ALTER USER 'consultant'@'laptop3' PASSWORD EXPIRE DEFAULT;

    • 禁用自动过期：ALTER USER 'consultant'@'laptop3' PASSWORD EXPIRE NEVER;

身份认证插件

核心认证插件（默认支持）

插件名称 特性 适用场景
caching_sha2_password MySQL 8.0 默认，SHA-256 加密，服务器端缓存 绝大多数生产环境
mysql_native_password 传统哈希方式，兼容性好 旧版本客户端连接
sha256_password SHA-256 加密，无缓存 兼容旧版企业级场景（已废弃）

特殊认证插件

明文客户端认证插件（mysql_clear_password）

  • 作用：客户端直接发送明文密码到服务器（需配合加密连接）
  • 启用方式：
    • 环境变量：LIBMYSQL_ENABLE_CLEARTEXT_PLUGIN=1
    • 命令行参数：mysql --enable-cleartext-plugin -u user -p
    • C API 函数：mysql_options()指定MYSQL_ENABLE_CLEARTEXT_PLUGIN

可加载认证插件

  • auth_socket：仅允许与系统用户名相同的 MySQL 用户通过 Unix socket 连接（无需密码）
  • mysql_no_login：禁止任何客户端连接使用该插件的账号（适用于仅执行存储程序的账号）
  • test_plugin_server：用于测试和开发，支持原生和旧密码认证

企业级认证插件（仅企业版）

  • PAM 认证（authentication_pam）：对接系统 PAM 服务（如 Linux 密码、LDAP），支持 Linux/macOS
  • Windows 认证（authentication_windows）：使用 Windows 原生服务认证客户端
  • LDAP 认证（authentication_ldap_sasl/simple）：通过 LDAP 目录服务获取用户信息并认证

PAM 认证配置示例

  1. 创建 PAM 服务配置文件/etc/pam.d/mysql-pam，内容：

    1
2

    auth    include     password-auth
account include     password-auth

  2. 创建 PAM 认证的 MySQL 用户：

    1

    CREATE USER bob@localhost IDENTIFIED WITH authentication_pam AS 'mysql-pam';

  3. 创建 PAM 代理用户（映射系统组到 MySQL 用户）：

    1

    CREATE USER ''@'' IDENTIFIED WITH authentication_pam AS 'mysql-pam,www=webuser,root=root';

  4. 授权代理权限：

    1

    GRANT PROXY ON webuser@localhost TO ''@'';

  5. 登录方式：mysql --enable-cleartext-plugin -u bob -p（输入系统密码）

权限分配与回收

权限范围（从大到小）

  1. 全局权限：作用于所有数据库，语法ON *.*（例：GRANT SELECT ON *.* TO user@host;）
  2. 数据库权限：作用于指定数据库，语法ON db_name.*
  3. 表权限：作用于指定表，语法ON db_name.table_name
  4. 列权限：作用于表中指定列，需在权限后指定列名
  5. 存储例程权限：作用于存储过程 / 函数，语法ON db_name.routine_name

常用权限类型

  • 数据操作：SELECT（查询）、INSERT（插入）、UPDATE（更新）、DELETE（删除）
  • 结构操作：CREATE（创建）、ALTER（修改）、DROP（删除）、CREATE VIEW（创建视图）
  • 管理权限：SHUTDOWN（关闭服务器）、PROCESS（查看进程）、RELOAD（刷新权限 / 日志）、SUPER（超级权限）
  • 特殊权限：ALL PRIVILEGES（所有权限，不含授权他人权限）、USAGE（仅连接权限，默认新账号权限）、GRANT OPTION（授权他人权限的权限）

权限操作语法

授予权限

  • 基础语法：GRANT 权限1,权限2 ON 范围 TO '用户/角色'@'主机' [WITH GRANT OPTION];

  • 列级授权示例：授予查询mysql.user表的user和host列权限

    1

    GRANT SELECT (user, host) ON mysql.user TO kari@localhost;

  • 授予角色示例：将r_viewer角色授予用户GRANT r_viewer TO kari@localhost WITH ADMIN OPTION;

    （WITH ADMIN OPTION允许用户将该角色授予他人）

回收权限

  • 基础语法：REVOKE 权限1,权限2 ON 范围 FROM '用户/角色'@'主机';
  • 回收角色示例：REVOKE r_updater FROM kari@localhost;
  • 回收所有权限：REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'sasha'@'host';

部分回收（MySQL 8.0.16+）

  • 作用：全局授予某权限后，排除特定数据库的权限

  • 启用条件：SET GLOBAL partial_revokes = ON;（默认关闭）

  • 示例：授予全局查询权限，但禁止查询mysql库

    1
2
3

    CREATE USER u1 IDENTIFIED BY 'Pssw0rd';
GRANT SELECT ON *.* TO u1;
REVOKE SELECT ON mysql.* FROM u1;

查看权限

  • 查看当前用户权限：SHOW GRANTS;（等同于SHOW GRANTS FOR CURRENT_USER();）
  • 查看其他用户权限：SHOW GRANTS FOR 'kari'@'localhost';
  • 查看角色权限：SHOW GRANTS FOR 'r_updater'@'%';
  • 查看角色展开后的用户权限：SHOW GRANTS FOR 'kari'@'localhost' USING r_viewer, r_updater;

角色激活与强制角色

角色激活级别（优先级：会话级 > 用户级 > 服务器级）

服务器级激活

  • 配置变量：activate_all_roles_on_login（默认 OFF）

  • 作用：所有用户登录时自动激活所有已授予的角色

    1

    SET PERSIST activate_all_roles_on_login = ON;

    （持久化，重启生效）

用户级激活（默认角色）

  • 语法：SET DEFAULT ROLE 角色1,角色2 TO 'user'@'host';

  • 示例：设置用户登录时默认激活r_viewer和r_updater

    1

    SET DEFAULT ROLE r_viewer, r_updater TO kari@localhost;

  • 简化设置：SET DEFAULT ROLE ALL TO kari@localhost;（激活所有授予的角色）

会话级激活（临时生效）

  • 语法：SET ROLE 角色1,角色2;
  • 常用参数：
    • SET ROLE ALL;（激活所有授予的角色）
    • SET ROLE NONE;（禁用所有角色）
    • SET ROLE DEFAULT;（激活用户级默认角色）
  • 查看当前激活角色：SELECT CURRENT_ROLE();

强制角色（所有用户必激活）

  • 作用：无需手动授予，自动对所有用户激活的角色（不可回收）
  • 配置语法：SET PERSIST mandatory_roles = 'role1, role2@localhost';
  • 特性：不能通过REVOKE或DROP ROLE删除，重启后仍生效

授权表与权限生效机制

核心授权表（存储在 mysql 库中）

表名 存储内容
user 用户账号、角色及全局权限
global_grants 动态全局权限
db 数据库级权限
tables_priv 表级权限
columns_priv 列级权限
role_edges 角色与用户 / 角色的授予关系
default_roles 用户的默认激活角色
password_history 用户密码修改历史

权限生效规则

  • 全局权限 / 密码变更：仅对后续新连接生效

  • 数据库级权限变更：客户端执行USE db_name后生效

  • 角色、表、列、存储例程权限变更：立即生效

  • 直接修改授权表后：需执行FLUSH PRIVILEGES重新加载内存副本

  • 注意：禁止使用INSERT/UPDATE/DELETE直接修改授权表，避免系统异常

最佳实践

  1. 遵循 “最小权限原则”：普通用户仅授予必要权限（如只读用户仅给SELECT），管理员避免滥用SUPER权限
  2. 角色分类管理：按业务场景创建角色（如r_readonly、r_admin），批量分配给用户，简化权限维护
  3. 密码安全策略：设置自动过期（如 90 天）、强制复杂密码，使用双重密码应对系统升级
  4. 限制连接来源：创建账号时指定具体主机或 IP 网段，避免%通配符
  5. 定期审计权限：通过SHOW GRANTS检查冗余权限，及时回收离职用户或过期角色的权限

MySQL 用户管理常用命令速查表

用户账号操作

创建用户

功能 命令 说明
基础创建 CREATE USER 'username'@'hostname' IDENTIFIED BY 'password'; 例：CREATE USER webuser@localhost IDENTIFIED BY 'Abc123';，hostname省略默认%（任意主机）
创建并设置密码过期 CREATE USER 'username'@'hostname' IDENTIFIED BY 'password' PASSWORD EXPIRE; 用户登录后需先改密码才能操作
创建 PAM 认证用户（企业版） CREATE USER 'username'@'hostname' IDENTIFIED WITH authentication_pam AS 'pam-service-name'; 例：CREATE USER bob@localhost IDENTIFIED WITH authentication_pam AS 'mysql-pam';

修改用户

功能 命令 说明
重命名用户 RENAME USER 'old_user'@'old_host' TO 'new_user'@'new_host'; 例：RENAME USER consultant@laptop3 TO james@laptop3;
锁定 / 解锁用户 ALTER USER 'username'@'hostname' ACCOUNT LOCK/UNLOCK; 锁定后用户无法登录
修改用户认证插件 ALTER USER 'username'@'hostname' IDENTIFIED WITH caching_sha2_password BY 'new_password'; 切换插件时需重新设置密码

删除用户

功能 命令 说明
删除用户及所有权限 DROP USER 'username'@'hostname'; 例：DROP USER james@laptop3;，同时清除授权表中该用户记录

密码管理

设置 / 修改密码

功能 命令 说明
修改指定用户密码 ALTER USER 'username'@'hostname' IDENTIFIED BY 'new_password'; 例：ALTER USER webuser1@localhost IDENTIFIED BY 'n3W&P4$$w0rd';
修改当前用户密码 ALTER USER USER() IDENTIFIED BY 'new_password'; 或 SET PASSWORD = 'new_password'; 登录状态下直接执行，无需指定用户名
命令行修改密码 mysqladmin -u username -p password 'new_password'; 执行时需输入旧密码，适用于脚本自动化
设置双重密码 ALTER USER USER() IDENTIFIED BY 'new_password' RETAIN CURRENT PASSWORD; 新密码为主密码，原密码为次要密码（MySQL 8.0.14+）
移除次要密码 ALTER USER USER() DISCARD OLD PASSWORD; 仅保留主密码

密码过期配置

功能 命令 说明
手动设置密码过期 ALTER USER 'username'@'hostname' PASSWORD EXPIRE; 例：ALTER USER erika@localhost PASSWORD EXPIRE;
配置账号自动过期 ALTER USER 'username'@'hostname' PASSWORD EXPIRE INTERVAL N DAY; 例：ALTER USER consultant@laptop3 PASSWORD EXPIRE INTERVAL 30 DAY;（30 天过期）
禁用账号自动过期 ALTER USER 'username'@'hostname' PASSWORD EXPIRE NEVER; 优先级高于全局默认策略

角色操作

角色创建 / 删除

功能 命令 说明
创建角色 CREATE ROLE 'rolename'@'hostname' [, 'rolename2'@'hostname2']; 例：CREATE ROLE r_admin, r_dev@localhost;，省略主机名默认%
删除角色 DROP ROLE 'rolename'@'hostname' [, 'rolename2'@'hostname2']; 例：DROP ROLE r_super, r_dev@localhost;，同时回收所有用户的该角色权限

角色授权与激活

功能 命令 说明
授予角色给用户 GRANT 'rolename'@'hostname' [, ...] TO 'username'@'hostname' [WITH ADMIN OPTION]; 例：GRANT r_viewer, r_updater TO kari@localhost WITH ADMIN OPTION;，WITH ADMIN OPTION允许用户转授角色
设置用户默认角色 SET DEFAULT ROLE 'rolename1'[, 'rolename2'] TO 'username'@'hostname'; 例：SET DEFAULT ROLE r_viewer, r_updater TO kari@localhost;，用户登录自动激活
会话级激活角色 SET ROLE 'rolename1'[, 'rolename2']; 或 SET ROLE ALL; 例：SET ROLE r_viewer;（激活指定角色）、SET ROLE NONE;（禁用所有角色）
查看当前激活角色 SELECT CURRENT_ROLE(); 输出当前会话中生效的角色
配置强制角色（全局） SET PERSIST mandatory_roles = 'rolename1, rolename2@localhost'; 所有用户自动激活，不可回收

权限操作

授予权限

功能 命令 说明
全局权限授予 GRANT 权限1[, 权限2] ON *.* TO 'user/role'@'hostname' [WITH GRANT OPTION]; 例：GRANT SELECT, INSERT ON *.* TO admin@localhost;，*.*表示所有数据库
数据库权限授予 GRANT 权限1[, 权限2] ON db_name.* TO 'user/role'@'hostname'; 例：GRANT CREATE, ALTER ON world.* TO dev@localhost;，仅作用于world库
表权限授予 GRANT 权限1[, 权限2] ON db_name.table_name TO 'user/role'@'hostname'; 例：GRANT SELECT, UPDATE ON world.city TO reader@localhost;
列权限授予 GRANT 权限(列1, 列2) ON db_name.table_name TO 'user/role'@'hostname'; 例：GRANT SELECT (user, host) ON mysql.user TO kari@localhost;，仅允许操作指定列
授予所有权限 GRANT ALL PRIVILEGES ON 范围 TO 'user/role'@'hostname' [WITH GRANT OPTION]; ALL PRIVILEGES不含转授权限，需加WITH GRANT OPTION才允许转授

回收权限

功能 命令 说明
回收指定权限 REVOKE 权限1[, 权限2] ON 范围 FROM 'user/role'@'hostname'; 例：REVOKE DELETE, INSERT ON world.* FROM Amon@localhost;
回收角色 REVOKE 'rolename'@'hostname' FROM 'username'@'hostname'; 例：REVOKE r_updater FROM kari@localhost;
回收所有权限 REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'username'@'hostname'; 清除用户所有权限及转授权限
部分回收（全局排除特定库） REVOKE 权限 ON exclude_db.* FROM 'username'@'hostname'; 需先开启SET GLOBAL partial_revokes = ON;，例：REVOKE SELECT ON mysql.* FROM u1;

查看权限

功能 命令 说明
查看当前用户权限 SHOW GRANTS; 或 SHOW GRANTS FOR CURRENT_USER(); 输出当前登录账号的所有权限与角色
查看指定用户权限 SHOW GRANTS FOR 'username'@'hostname'; 例：SHOW GRANTS FOR kari@localhost;
查看角色权限 SHOW GRANTS FOR 'rolename'@'hostname'; 例：SHOW GRANTS FOR r_updater@'%';
查看角色展开后的用户权限 SHOW GRANTS FOR 'username'@'hostname' USING 'rolename1'[, 'rolename2']; 输出角色包含的具体权限，例：SHOW GRANTS FOR kari@localhost USING r_viewer;

辅助操作

功能 命令 说明
查看所有用户 / 角色 SELECT user, host, authentication_string FROM mysql.user; 从授权表mysql.user中查询账号信息
刷新权限（直接修改授权表后） FLUSH PRIVILEGES; 重新加载授权表到内存，使手动修改生效
查看 PAM 代理用户映射 SELECT USER(), CURRENT_USER(); 例：代理用户 anne 登录后，输出anne@localhost（实际用户）和webuser@localhost（映射用户）
命令行登录 MySQL 本地：mysql -u username -p password；远程：mysql -u username -p password -h server_ip -p与密码间无空格，若加空格需后续手动输入密码
启用明文认证登录（PAM 等场景） mysql --enable-cleartext-plugin -u username -p 客户端发送明文密码，需配合加密连接使用
© 2025 Penna_a
共 0 次浏览