MySQL创建子账户及权限

一、为何需要创建独立账号？

• 最小权限原则：每个用户只拥有完成工作所必需的权限，避免因账号泄露导致整个数据库沦陷
• 操作审计需求：不同账号的操作日志便于追踪和排查问题
• 资源控制能力：限制单个账号的系统资源消耗（如查询频率、连接数）
• 职责分离：区分开发、运维、分析等不同角色的数据访问权限

二、创建MySQL用户账号

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

• username：自定义的用户名
• host：允许连接的主机地址
  • localhost：仅允许本地连接
  • %：允许从任何IP地址连接
  • 192.168.1.%：允许特定网段连接
• password：用户密码，强烈建议使用包含大小写字母、数字和特殊字符的复杂密码

三、分配权限

GRANT 权限类型 ON 数据库.表 TO '用户名'@'主机';

权限类型详解

常用授权示例

授予单个数据库所有权限

GRANT ALL PRIVILEGES ON sales_db.* TO 'dev_user'@'192.168.1.%';

授予只读权限

GRANT SELECT ON report_db.* TO 'read_user'@'%';

授予特定表权限

GRANT SELECT, INSERT ON order_db.customers TO 'ops_user'@'localhost';

授予跨数据库权限（需谨慎）

GRANT SELECT ON *.* TO 'dba_user'@'localhost' WITH GRANT OPTION;

权限生效与查看

授权后必须刷新权限使更改生效：

FLUSH PRIVILEGES;

查看用户权限：

SHOW GRANTS FOR 'username'@'host';

四、高级账号管理技巧

1. 资源使用限制

创建用户时可限制其资源使用：

CREATE USER 'limited_user'@'%' 
IDENTIFIED BY 'Password123'
WITH MAX_QUERIES_PER_HOUR 100 
 MAX_UPDATES_PER_HOUR 10
 MAX_CONNECTIONS_PER_HOUR 30;

此用户每小时最多执行100次查询、10次更新和30个连接

2. 密码策略管理

ALTER USER 'dev_user'@'%' 
PASSWORD EXPIRE INTERVAL 90 DAY
PASSWORD HISTORY 5;

强制该用户每90天更改密码，且新密码不能与最近5次密码重复

3. 账号禁用与启用

– 禁用账号

ALTER USER 'temp_user'@'%' ACCOUNT LOCK;

– 启用账号

ALTER USER 'temp_user'@'%' ACCOUNT UNLOCK;

4. 重命名用户

RENAME USER 'old_user'@'%' TO 'new_user'@'%';

五、安全最佳实践

• 避免使用通配符主机名：尽可能限制访问IP范围，减少暴露面
• 遵循最小权限原则：只授予完成工作所需的最低权限
• 定期审计权限：使用SHOW GRANTS检查用户权限是否合理
• 删除无用账号：及时清理离职人员或不再使用的账号

DROP USER 'departed_user'@'%';

• 敏感操作双重认证：对DROP、DELETE等高危操作设置额外审批流程
• 密码策略强制执行：启用密码复杂度检查和定期过期策略

六、权限修改与撤销

当需要收回权限时：

– 撤销所有权限

REVOKE ALL PRIVILEGES ON sales_db.* FROM 'dev_user'@'%';

– 撤销特定权限

REVOKE DELETE ON order_db.* FROM 'ops_user'@'localhost';

撤销权限后同样需要执行FLUSH PRIVILEGES;使更改生效