印刻数据库

外观

MySQL 用户创建与删除 ​

用户创建方法 ​

使用CREATE USER语句 ​

基本语法 ​

sql
-- 创建基本用户
CREATE USER 'username'@'host' IDENTIFIED BY 'password';

-- 创建用户并指定认证插件
CREATE USER 'username'@'host' IDENTIFIED WITH auth_plugin BY 'password';

-- 创建用户并设置密码过期时间
CREATE USER 'username'@'host' IDENTIFIED BY 'password' PASSWORD EXPIRE INTERVAL 90 DAY;

-- 创建用户并设置资源限制
CREATE USER 'username'@'host' IDENTIFIED BY 'password'
  WITH MAX_QUERIES_PER_HOUR 1000
       MAX_UPDATES_PER_HOUR 500
       MAX_CONNECTIONS_PER_HOUR 100
       MAX_USER_CONNECTIONS 10;

主机名指定 ​

  • localhost：仅允许本地连接
  • %：允许从任何主机连接
  • 192.168.1.%：允许从指定网段连接
  • 192.168.1.100：仅允许从指定IP连接

密码管理 ​

sql
-- 创建用户时生成强密码
CREATE USER 'username'@'host' IDENTIFIED BY RANDOM PASSWORD;

-- 创建用户时使用密码哈希
CREATE USER 'username'@'host' IDENTIFIED BY PASSWORD '*HASHED_PASSWORD';

使用GRANT语句 ​

语法 ​

sql
-- 创建用户并授予权限
GRANT SELECT ON database.* TO 'username'@'host' IDENTIFIED BY 'password';

-- 创建用户并授予多个权限
GRANT SELECT, INSERT, UPDATE ON database.* TO 'username'@'host' IDENTIFIED BY 'password';

-- 创建用户并授予所有权限
GRANT ALL PRIVILEGES ON *.* TO 'username'@'host' IDENTIFIED BY 'password' WITH GRANT OPTION;

注意事项 ​

  • 在MySQL 8.0中，GRANT语句不再自动创建用户
  • 需要先创建用户，再授予权限
  • WITH GRANT OPTION允许用户授予自己的权限给其他用户

使用mysql_native_password插件 ​

语法 ​

sql
-- 使用mysql_native_password插件创建用户
CREATE USER 'username'@'host' IDENTIFIED WITH mysql_native_password BY 'password';

适用场景 ​

  • 需要兼容旧版本MySQL客户端
  • 对性能要求较高的场景
  • 不需要复杂的密码验证策略

使用caching_sha2_password插件 ​

语法 ​

sql
-- 使用caching_sha2_password插件创建用户
CREATE USER 'username'@'host' IDENTIFIED WITH caching_sha2_password BY 'password';

适用场景 ​

  • MySQL 8.0及以上版本推荐使用
  • 提供更强的安全性
  • 支持密码缓存，提高性能

用户删除方法 ​

使用DROP USER语句 ​

基本语法 ​

sql
-- 删除用户
DROP USER 'username'@'host';

-- 删除多个用户
DROP USER 'user1'@'host', 'user2'@'host';

-- 删除用户（如果存在）
DROP USER IF EXISTS 'username'@'host';

注意事项 ​

  • DROP USER语句会同时删除用户的所有权限
  • 需要有CREATE USER权限或超级权限才能执行
  • 删除用户前应确认用户不再需要
  • 建议在删除用户前备份用户权限信息

手动删除用户 ​

修改mysql.user表 ​

sql
-- 删除用户记录
DELETE FROM mysql.user WHERE User='username' AND Host='host';

-- 刷新权限
FLUSH PRIVILEGES;

注意事项 ​

  • 不推荐使用这种方法，容易出错
  • 需要手动删除用户在其他权限表中的记录
  • 可能导致权限表不一致
  • 建议使用DROP USER语句

批量删除用户 ​

使用脚本批量删除 ​

sql
-- 批量删除特定前缀的用户
SELECT CONCAT('DROP USER ''', User, '''@''', Host, ''';') 
FROM mysql.user 
WHERE User LIKE 'test_%';

-- 执行生成的DROP USER语句

注意事项 ​

  • 批量删除前应仔细确认用户列表
  • 建议先生成删除语句，再手动执行
  • 保留必要的用户账户
  • 记录删除操作，便于审计

用户创建的最佳实践 ​

预创建准备 ​

需求分析 ​

  • 明确用户的角色和职责
  • 确定用户需要的最小权限
  • 选择合适的认证方式
  • 制定用户命名规范

安全评估 ​

  • 评估用户权限的安全风险
  • 确认用户创建的必要性
  • 验证用户认证方式的安全性
  • 制定用户管理策略

创建流程 ​

标准流程 ​

  1. 申请：用户创建申请，包含用户信息和权限需求
  2. 审批：权限管理团队审批
  3. 创建：执行用户创建操作
  4. 授权：授予必要的最小权限
  5. 验证：验证用户连接和权限
  6. 记录：记录用户创建信息和审批记录

自动化流程 ​

  • 使用配置管理工具（如Ansible）自动化用户创建
  • 集成到CI/CD流程中
  • 建立用户创建的API服务
  • 实现用户创建的自助服务

权限管理 ​

基于角色的权限管理 ​

  • 创建角色并分配权限
  • 将角色授予用户
  • 简化权限管理和维护
  • 确保权限一致性

权限验证 ​

  • 验证用户权限是否符合最小权限原则
  • 测试用户权限是否满足业务需求
  • 定期审查用户权限
  • 及时调整不必要的权限

用户删除的最佳实践 ​

删除前准备 ​

需求确认 ​

  • 确认用户删除的必要性
  • 验证用户删除的审批流程
  • 检查用户是否有未完成的任务
  • 确认用户数据的处理方式

权限备份 ​

  • 备份用户权限信息，便于后续参考
  • 记录用户的权限历史
  • 保存删除操作的审批记录
  • 建立用户删除的审计 trail

删除流程 ​

标准流程 ​

  1. 申请：用户删除申请，包含删除原因
  2. 审批：权限管理团队审批
  3. 通知：通知相关人员用户将被删除
  4. 验证：验证用户不再需要
  5. 删除：执行用户删除操作
  6. 记录：记录用户删除信息和审批记录

特殊情况处理 ​

  • 离职员工：及时删除或禁用账户
  • 调岗员工：调整权限而非删除账户
  • 临时用户：设置过期时间，自动失效
  • 服务账户：谨慎删除，确认无依赖服务

后续处理 ​

权限清理 ​

  • 确认用户相关的权限已完全删除
  • 检查是否有与用户相关的对象
  • 清理用户创建的临时对象
  • 验证权限表的一致性

审计记录 ​

  • 记录用户删除的详细信息
  • 保存删除操作的审批记录
  • 建立用户删除的时间线
  • 为合规审计提供证据

安全考虑 ​

密码安全 ​

密码策略 ​

  • 实施强密码策略
  • 定期强制密码更改
  • 限制密码重用
  • 监控密码尝试失败

密码存储 ​

  • 使用安全的密码哈希算法
  • 避免明文存储密码
  • 加密存储密码相关配置
  • 定期检查密码哈希安全性

认证安全 ​

认证插件选择 ​

  • 使用caching_sha2_password（MySQL 8.0默认）
  • 避免使用不安全的认证插件
  • 定期更新认证插件
  • 验证认证插件的安全性

网络安全 ​

  • 限制用户连接的主机范围
  • 使用SSL/TLS加密连接
  • 避免使用%作为主机名
  • 实施网络访问控制

权限安全 ​

最小权限原则 ​

  • 只授予必要的最小权限
  • 定期审查和回收不必要的权限
  • 基于角色分配权限
  • 避免使用ALL PRIVILEGES

权限监控 ​

  • 监控权限变更
  • 审计权限使用情况
  • 检测异常的权限操作
  • 及时发现权限滥用

常见问题处理 ​

用户创建问题 ​

连接失败 ​

  • 原因：用户不存在、密码错误、主机名不匹配
  • 解决方案：验证用户名、密码和主机名，检查用户创建语句

权限不足 ​

  • 原因：创建用户需要CREATE USER权限
  • 解决方案：使用具有CREATE USER权限的账户

认证插件错误 ​

  • 原因：认证插件不兼容或不存在
  • 解决方案：选择合适的认证插件，确保插件已安装

用户删除问题 ​

删除失败 ​

  • 原因：用户不存在、权限不足、用户有活动连接
  • 解决方案：验证用户存在性，确保有足够权限，终止用户活动连接

权限残留 ​

  • 原因：用户删除后权限表未完全清理
  • 解决方案：使用FLUSH PRIVILEGES，检查权限表

依赖问题 ​

  • 原因：其他对象依赖于该用户
  • 解决方案：检查并处理依赖关系，如存储过程、视图等

性能问题 ​

用户创建缓慢 ​

  • 原因：权限表过大、磁盘IO瓶颈
  • 解决方案：优化权限表，增加磁盘IO性能

用户删除缓慢 ​

  • 原因：用户权限过多、权限表碎片化
  • 解决方案：定期优化权限表，分批删除用户

案例分析 ​

案例1：生产环境用户创建 ​

背景 ​

  • 某企业需要为新应用创建数据库用户
  • 应用需要读写操作权限
  • 要求遵循最小权限原则

实施步骤 ​

  1. 需求分析：应用需要对app_db数据库的读写权限
  2. 审批：权限管理团队审批通过
  3. 创建用户：
    sql
    CREATE USER 'app_user'@'192.168.1.%' IDENTIFIED BY 'StrongPassword123!';
GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO 'app_user'@'192.168.1.%';
  4. 验证：测试用户连接和权限
  5. 记录：记录用户创建信息和审批记录

结果 ​

  • 应用成功连接数据库
  • 用户权限符合最小权限原则
  • 安全风险可控

案例2：离职员工用户删除 ​

背景 ​

  • 某员工离职，需要删除其数据库用户
  • 该用户具有管理权限
  • 需要确保无依赖服务

实施步骤 ​

  1. 需求确认：确认员工已离职，无未完成任务
  2. 审批：权限管理团队审批通过
  3. 检查依赖：确认无服务依赖该用户
  4. 删除用户：
    sql
    DROP USER 'employee'@'localhost';
  5. 验证：确认用户已删除，权限已清理
  6. 记录：记录用户删除信息和审批记录

结果 ​

  • 用户成功删除
  • 无服务中断
  • 权限表保持清洁

案例3：临时测试用户管理 ​

背景 ​

  • 开发团队需要临时测试用户
  • 测试完成后需要删除
  • 避免测试用户残留

实施步骤 ​

  1. 创建临时用户：
    sql
    CREATE USER 'test_user'@'localhost' IDENTIFIED BY 'TestPassword123!' PASSWORD EXPIRE INTERVAL 7 DAY;
GRANT ALL PRIVILEGES ON test_db.* TO 'test_user'@'localhost';
  2. 测试使用：开发团队使用测试用户
  3. 自动过期：7天后密码过期
  4. 清理删除：测试完成后删除用户
    sql
    DROP USER 'test_user'@'localhost';

结果 ​

  • 测试用户正常使用
  • 自动过期提高安全性
  • 无测试用户残留

常见问题（FAQ） ​

Q1: 如何创建具有超级权限的用户？ ​

A1: 创建具有超级权限的用户应谨慎：

sql
CREATE USER 'admin'@'localhost' IDENTIFIED BY 'StrongAdminPassword!';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;

注意：超级权限应只授予必要的管理员，避免滥用。

Q2: 如何创建只读用户？ ​

A2: 创建只读用户：

sql
CREATE USER 'read_only'@'192.168.1.%' IDENTIFIED BY 'ReadOnlyPassword123!';
GRANT SELECT ON database.* TO 'read_only'@'192.168.1.%';

Q3: 如何删除所有测试用户？ ​

A3: 批量删除测试用户：

sql
-- 生成删除语句
SELECT CONCAT('DROP USER ''', User, '''@''', Host, ''';') 
FROM mysql.user 
WHERE User LIKE 'test_%';

-- 执行生成的DROP USER语句

Q4: 用户删除后如何恢复？ ​

A4: 用户删除后无法直接恢复，需要：

  • 从备份中恢复用户信息
  • 重新创建用户并授予相应权限
  • 验证用户权限是否正确

建议：删除用户前备份用户权限信息。

Q5: 如何防止用户暴力破解？ ​

A5: 防止用户暴力破解：

  • 实施强密码策略
  • 限制登录尝试次数
  • 使用防火墙限制连接IP
  • 启用登录失败审计
  • 考虑使用双因素认证