印刻数据库

外观

MySQL 用户管理 ​

用户管理概述 ​

用户的重要性 ​

MySQL 用户管理是数据库安全的基础，合理的用户管理可以：

  • 防止未授权访问
  • 限制用户权限范围
  • 便于审计和追踪操作
  • 提高数据库安全性
  • 满足合规要求

MySQL 用户结构 ​

MySQL 用户由两部分组成：

  • 用户名：用户的名称
  • 主机名/IP：允许用户连接的来源地址，可以是：
    • 具体 IP 地址（如 192.168.1.100）
    • 网段（如 192.168.1.%）
    • 本地（localhost 或 127.0.0.1）
    • 所有主机（%，不推荐）

示例用户：'root'@'localhost'、'app'@'192.168.1.%'

用户创建 ​

CREATE USER 语法 ​

sql
CREATE USER [IF NOT EXISTS] 'username'@'host' [IDENTIFIED BY 'password']
    [IDENTIFIED WITH auth_plugin [BY 'password']]
    [DEFAULT ROLE role [, role] ...]
    [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
    [WITH resource_option [resource_option] ...]
    [PASSWORD EXPIRE [DEFAULT | NEVER | INTERVAL N DAY]]
    [ACCOUNT {LOCK | UNLOCK}];

基本用户创建 ​

创建本地用户 ​

sql
-- 创建本地 root 用户
CREATE USER 'root'@'localhost' IDENTIFIED BY 'StrongRootPassword1!';

-- 创建普通本地用户
CREATE USER 'admin'@'localhost' IDENTIFIED BY 'AdminPassword1!';

创建远程用户 ​

sql
-- 创建允许从特定 IP 连接的用户
CREATE USER 'app'@'192.168.1.100' IDENTIFIED BY 'AppPassword1!';

-- 创建允许从网段连接的用户
CREATE USER 'app'@'192.168.1.%' IDENTIFIED BY 'AppPassword1!';

-- 创建允许从任何主机连接的用户（不推荐）
CREATE USER 'app'@'%' IDENTIFIED BY 'AppPassword1!';

使用不同认证插件 ​

mysql_native_password（传统方式） ​

sql
CREATE USER 'user1'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Password1!';

caching_sha2_password（MySQL 8.0 默认） ​

sql
CREATE USER 'user2'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'Password2!';

无密码用户（不推荐） ​

sql
CREATE USER 'user3'@'localhost' IDENTIFIED WITH auth_socket; -- 基于操作系统认证

设置资源限制 ​

sql
CREATE USER 'app'@'localhost' IDENTIFIED BY 'AppPassword1!'
    WITH MAX_QUERIES_PER_HOUR 1000
         MAX_UPDATES_PER_HOUR 500
         MAX_CONNECTIONS_PER_HOUR 100
         MAX_USER_CONNECTIONS 10;

设置密码过期策略 ​

sql
-- 密码永不过期
CREATE USER 'user'@'localhost' IDENTIFIED BY 'Password1!' PASSWORD EXPIRE NEVER;

-- 密码 90 天后过期
CREATE USER 'user'@'localhost' IDENTIFIED BY 'Password1!' PASSWORD EXPIRE INTERVAL 90 DAY;

-- 密码立即过期，首次登录需修改
CREATE USER 'user'@'localhost' IDENTIFIED BY 'Password1!' PASSWORD EXPIRE;

创建锁定用户 ​

sql
-- 创建锁定用户，需要手动解锁才能使用
CREATE USER 'user'@'localhost' IDENTIFIED BY 'Password1!' ACCOUNT LOCK;

用户信息查询 ​

查看所有用户 ​

sql
-- 查看所有用户（MySQL 5.7+）
SELECT user, host, plugin FROM mysql.user;

-- 查看特定用户
SELECT * FROM mysql.user WHERE user = 'app' AND host = 'localhost';

查看用户权限 ​

sql
-- 查看用户的全局权限
SHOW GRANTS FOR 'app'@'localhost';

-- 查看用户的数据库级别权限
SHOW GRANTS FOR 'app'@'localhost' USING 'mysql.db';

-- 查看用户的表级别权限
SHOW GRANTS FOR 'app'@'localhost' USING 'mysql.tables_priv';

查看用户资源限制 ​

sql
SELECT user, host, max_questions, max_updates, max_connections, max_user_connections FROM mysql.user;

查看用户密码策略 ​

sql
SELECT user, host, password_expired, password_last_changed, password_lifetime FROM mysql.user;

用户修改 ​

修改用户名和主机 ​

使用 RENAME USER ​

sql
-- 重命名用户
RENAME USER 'old_user'@'localhost' TO 'new_user'@'localhost';

-- 修改用户主机
RENAME USER 'user'@'192.168.1.100' TO 'user'@'192.168.1.%';

使用 UPDATE（不推荐） ​

sql
-- 不推荐直接修改系统表，可能导致权限问题
UPDATE mysql.user SET user = 'new_user' WHERE user = 'old_user' AND host = 'localhost';
FLUSH PRIVILEGES;

修改用户密码 ​

ALTER USER 方式（推荐） ​

sql
-- 修改用户密码
ALTER USER 'user'@'localhost' IDENTIFIED BY 'NewPassword1!';

-- 修改认证插件和密码
ALTER USER 'user'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'NewPassword1!';

SET PASSWORD 方式 ​

sql
SET PASSWORD FOR 'user'@'localhost' = 'NewPassword1!';

使用 mysqladmin 工具 ​

bash
mysqladmin -u user -p'oldpassword' password 'newpassword'

修改密码过期策略 ​

sql
-- 设置密码永不过期
ALTER USER 'user'@'localhost' PASSWORD EXPIRE NEVER;

-- 设置密码 90 天过期
ALTER USER 'user'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;

-- 立即过期密码
ALTER USER 'user'@'localhost' PASSWORD EXPIRE;

修改资源限制 ​

sql
-- 修改资源限制
ALTER USER 'app'@'localhost' WITH MAX_QUERIES_PER_HOUR 2000 MAX_UPDATES_PER_HOUR 1000;

-- 移除资源限制
ALTER USER 'app'@'localhost' WITH MAX_QUERIES_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0;

锁定和解锁用户 ​

sql
-- 锁定用户
ALTER USER 'user'@'localhost' ACCOUNT LOCK;

-- 解锁用户
ALTER USER 'user'@'localhost' ACCOUNT UNLOCK;

修改用户默认角色 ​

sql
-- 设置用户默认角色
ALTER USER 'user'@'localhost' DEFAULT ROLE 'developer', 'readonly';

-- 移除默认角色
ALTER USER 'user'@'localhost' DEFAULT ROLE NONE;

用户删除 ​

DROP USER 语法 ​

sql
DROP USER [IF EXISTS] 'username'@'host' [, 'username'@'host'] ...;

删除单个用户 ​

sql
-- 删除指定用户
DROP USER 'app'@'192.168.1.%';

-- 删除多个用户
DROP USER 'app1'@'localhost', 'app2'@'localhost';

-- 删除不存在的用户（使用 IF EXISTS 避免错误）
DROP USER IF EXISTS 'nonexistent'@'localhost';

注意事项 ​

  • 删除用户时会自动移除该用户的所有权限
  • 删除用户前应先确认该用户没有正在执行的会话
  • 建议使用 IF EXISTS 避免因用户不存在导致的错误

MySQL版本差异与用户管理 ​

特性MySQL 5.6MySQL 5.7MySQL 8.0
默认认证插件mysql_native_passwordmysql_native_passwordcaching_sha2_password
CREATE USER 语法基本语法增强语法，支持更多选项完整语法，支持角色和资源限制
密码策略插件需手动安装内置 validate_password内置 validate_password，增强功能
密码过期功能不支持支持支持，增强配置选项
账户锁定功能不支持支持支持
资源限制支持支持支持
角色管理不支持支持增强支持
密码复杂度验证需手动安装插件内置支持增强支持
临时表权限有限制增强增强
撤销权限语法REVOKE 基本语法REVOKE 增强语法REVOKE 完整语法
权限表结构传统结构新增 role_edges 表增强 role_edges 表

用户认证管理 ​

认证插件 ​

MySQL 支持多种认证插件，常用的有：

插件名称描述适用场景
caching_sha2_passwordMySQL 8.0 默认，安全性高推荐使用
mysql_native_password传统认证方式兼容性要求高的场景
auth_socket基于操作系统用户认证本地管理用户
sha256_password基于 SHA-256 认证安全性要求高的场景
pam基于 PAM 认证集成外部认证系统

切换认证插件 ​

sql
-- 将用户切换到 caching_sha2_password
ALTER USER 'user'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'Password1!';

-- 将用户切换到 mysql_native_password
ALTER USER 'user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Password1!';

密码复杂度验证 ​

安装 validate_password 插件 ​

sql
-- 安装密码验证插件
INSTALL PLUGIN validate_password SONAME 'validate_password.so';

-- 查看插件状态
SHOW PLUGINS LIKE 'validate_password';

配置密码复杂度 ​

sql
-- 查看密码验证插件配置
SHOW VARIABLES LIKE 'validate_password%';

-- 配置密码复杂度
SET GLOBAL validate_password_policy = 'MEDIUM'; -- 中等复杂度
SET GLOBAL validate_password_length = 8; -- 最小长度 8
SET GLOBAL validate_password_number_count = 1; -- 至少一个数字
SET GLOBAL validate_password_special_char_count = 1; -- 至少一个特殊字符
SET GLOBAL validate_password_mixed_case_count = 1; -- 至少一个大小写

用户会话管理 ​

查看用户会话 ​

sql
-- 查看所有活动会话
SHOW PROCESSLIST;

-- 查看特定用户的会话
SELECT * FROM information_schema.processlist WHERE user = 'app';

终止用户会话 ​

sql
-- 终止特定会话
KILL [CONNECTION | QUERY] process_id;

-- 示例：终止 ID 为 123 的会话
KILL 123;

-- 终止用户的所有会话
SELECT CONCAT('KILL ', id, ';') FROM information_schema.processlist WHERE user = 'app';
-- 复制结果并执行

限制用户会话数 ​

sql
-- 在创建用户时限制会话数
CREATE USER 'app'@'localhost' IDENTIFIED BY 'Password1!' WITH MAX_USER_CONNECTIONS 10;

-- 修改现有用户的会话数限制
ALTER USER 'app'@'localhost' WITH MAX_USER_CONNECTIONS 20;

最佳实践 ​

用户命名规范 ​

场景命名示例规范说明
管理员admin@localhost只允许本地连接
应用用户app_name@192.168.1.%按应用命名，限制网段
只读用户app_name_read@192.168.1.%明确标识只读权限
监控用户monitor@192.168.1.%只授予监控所需权限
备份用户backup@192.168.1.100只允许备份服务器连接

密码策略 ​

  • 使用强密码：包含大小写字母、数字和特殊字符
  • 密码长度至少 8 位
  • 定期更换密码（90-180 天）
  • 不同环境使用不同密码
  • 使用密码管理工具

最小权限原则 ​

  • 只授予用户所需的最小权限
  • 避免使用 GRANT ALL PRIVILEGES ON *.*
  • 按功能划分用户（读写、只读、管理等）
  • 定期审查用户权限

连接限制 ​

  • 避免使用 'user'@'%' 允许所有主机连接
  • 尽量限制为特定 IP 或网段
  • 限制用户的并发连接数
  • 配置合理的连接超时时间

定期审计 ​

  • 定期审查用户列表，删除不必要的用户
  • 审计用户权限变更
  • 监控用户登录和操作
  • 记录和分析异常登录尝试

安全配置 ​

  • 禁用匿名用户：删除所有 ''@'localhost' 或 ''@'%' 匿名用户
    sql
    DELETE FROM mysql.user WHERE user = '';
FLUSH PRIVILEGES;
  • 移除默认的测试数据库：删除 test 数据库
    sql
    DROP DATABASE IF EXISTS test;
DELETE FROM mysql.db WHERE Db = 'test' OR Db LIKE 'test\_%';
FLUSH PRIVILEGES;
  • 为 root 用户设置强密码：使用复杂密码
    sql
    ALTER USER 'root'@'localhost' IDENTIFIED BY 'StrongRootPassword2024!';
  • 限制 root 用户只能本地连接：确保 root 用户只允许本地访问
    sql
    DELETE FROM mysql.user WHERE user = 'root' AND host != 'localhost';
FLUSH PRIVILEGES;
  • 启用密码过期策略：设置合理的密码过期时间
    sql
    SET GLOBAL default_password_lifetime = 90; -- 90天过期
  • 启用连接控制插件：防止暴力破解
    sql
    INSTALL PLUGIN CONNECTION_CONTROL SONAME 'connection_control.so';
INSTALL PLUGIN CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS SONAME 'connection_control.so';
SET GLOBAL connection_control_failed_connections_threshold = 3;
SET GLOBAL connection_control_min_connection_delay = 1000;
SET GLOBAL connection_control_max_connection_delay = 30000;
  • 禁用不必要的权限表访问：限制普通用户对权限表的访问
    sql
    REVOKE SELECT ON mysql.* FROM 'app_user'@'localhost';
  • 启用审计日志：记录用户活动（MySQL 8.0+）
    sql
    INSTALL PLUGIN audit_log SONAME 'audit_log.so';
SET GLOBAL audit_log_policy = 'ALL';

用户管理自动化脚本 ​

创建应用用户脚本 ​

bash
#!/bin/bash
# 创建 MySQL 应用用户脚本

# 配置参数
MYSQL_USER="root"
MYSQL_PASSWORD="RootPassword1!"
APP_NAME="myapp"
APP_PASSWORD="AppPassword1!"
APP_HOST="192.168.1.%"
DB_NAME="myapp_db"

# 日志函数
log() {
    echo "[$(date +'%Y-%m-%d %H:%M:%S')] $1"
}

# 执行 SQL 命令
mysql_exec() {
    mysql -u $MYSQL_USER -p$MYSQL_PASSWORD -e "$1"
}

# 主函数
main() {
    log "开始创建应用用户: $APP_NAME@$APP_HOST"
    
    # 创建应用用户
    mysql_exec "CREATE USER IF NOT EXISTS '$APP_NAME'@'$APP_HOST' IDENTIFIED BY '$APP_PASSWORD' WITH MAX_USER_CONNECTIONS 50;"
    if [ $? -eq 0 ]; then
        log "用户创建成功"
    else
        log "用户创建失败"
        exit 1
    fi
    
    # 授予数据库权限
    mysql_exec "GRANT SELECT, INSERT, UPDATE, DELETE ON $DB_NAME.* TO '$APP_NAME'@'$APP_HOST';"
    if [ $? -eq 0 ]; then
        log "权限授予成功"
    else
        log "权限授予失败"
        exit 1
    fi
    
    # 刷新权限
    mysql_exec "FLUSH PRIVILEGES;"
    
    log "应用用户创建完成"
}

# 执行脚本
main

清理过期用户脚本 ​

bash
#!/bin/bash
# 清理过期 MySQL 用户脚本

# 配置参数
MYSQL_USER="root"
MYSQL_PASSWORD="RootPassword1!"
LOG_FILE="/var/log/mysql/cleanup_users.log"

# 日志函数
log() {
    echo "[$(date +'%Y-%m-%d %H:%M:%S')] $1" >> $LOG_FILE
}

# 执行 SQL 命令
mysql_exec() {
    mysql -u $MYSQL_USER -p$MYSQL_PASSWORD -e "$1"
}

# 主函数
main() {
    log "开始清理过期用户"
    
    # 获取超过 90 天未使用的用户（需要 performance_schema 启用）
    EXPIRED_USERS=$(mysql_exec "SELECT user, host FROM mysql.user WHERE user NOT IN ('root', 'mysql.sys', 'mysql.session') AND password_last_changed < DATE_SUB(NOW(), INTERVAL 90 DAY);")
    
    # 处理结果
    echo "$EXPIRED_USERS" | grep -v "user" | while read -r user host; do
        if [ -n "$user" ]; then
            log "删除过期用户: $user@$host"
            mysql_exec "DROP USER '$user'@'$host';"
            if [ $? -eq 0 ]; then
                log "用户 $user@$host 删除成功"
            else
                log "用户 $user@$host 删除失败"
            fi
        fi
    done
    
    log "过期用户清理完成"
}

# 执行脚本
main

常见问题及解决方案 ​

忘记 root 密码 ​

Linux 系统 ​

bash
# 1. 停止 MySQL 服务
systemctl stop mysqld

# 2. 以跳过授权表方式启动
mysqld --skip-grant-tables --skip-networking &

# 3. 无密码登录
mysql -u root

# 4. 修改 root 密码
FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewRootPassword1!';

# 5. 重启 MySQL 服务
pkill mysqld
systemctl start mysqld

Windows 系统 ​

  1. 停止 MySQL 服务（服务管理器或命令行）
  2. 在命令行中启动 MySQL：
    cmd
    mysqld --skip-grant-tables
  3. 另开命令行，无密码登录：
    cmd
    mysql -u root
  4. 修改密码：
    sql
    FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewRootPassword1!';
  5. 重启 MySQL 服务

用户无法登录 ​

问题解决方案
密码错误重置密码
主机限制检查用户的 host 配置
账户锁定解锁账户
密码过期修改密码
认证插件不匹配切换认证插件
权限不足检查防火墙或网络配置

查看登录失败日志 ​

sql
-- 查看登录失败次数
SHOW GLOBAL STATUS LIKE 'Connection_errors_denied';

-- 查看错误日志
SHOW VARIABLES LIKE 'log_error';
-- 然后查看该日志文件

总结 ​

MySQL 用户管理是数据库安全的基础，通过合理创建、配置和管理用户，可以提高数据库的安全性和可靠性。

建议：

  • 定期审查用户列表和权限
  • 遵循最小权限原则
  • 使用强密码策略
  • 限制用户连接来源
  • 监控用户活动
  • 定期备份用户权限

通过本文的介绍，您应该掌握了 MySQL 用户的创建、修改、删除和管理的方法，以及相关的最佳实践。