DevNull 知识库

Appearance

11｜账号与权限 ​

前面所有操作都用 root。但生产环境不能让应用用 root 连数据库——root 能删库、能看所有数据、能改权限，一旦应用被攻破或 SQL 注入，攻击者直接拿到最高权限。

正确做法是给应用建专门的账号，只给它必要的权限。本篇讲 MySQL 的账号怎么建、权限怎么授、怎么排查权限问题。

一、账号是 user@host ​

MySQL 的账号由 user@host 两部分组成——用户名 + 来源。同一个用户名可以有多个来源：

sql
SELECT user, host FROM mysql.user;
text
user    | host
app     | 10.0.%
app     | localhost
app     | %

这意味着 app 这个用户名可以从三个来源连——10.0.% 网段、本机、任意地址。匹配规则是找最精确的那一条：从 10.0.1.15 连进来，优先匹配 10.0.%，不匹配 localhost 也不匹配 %。

这个设计很关键——同一个用户名不同来源可以有不同权限。app@10.0.% 给内网完整权限，app@%（外网）只给只读。

USER() 和 CURRENT_USER() 的区别 ​

排查权限问题时这两个函数能救命：

sql
SELECT USER(), CURRENT_USER();
  • USER() — 客户端声称的身份（连的时候传的 user@host）
  • CURRENT_USER() — MySQL 实际匹配到的授权账号

权限问题大多数出在后者与预期不一致——你以为连的是 app@10.0.%，实际 MySQL 给你匹配成了权限更宽的 app@%。两个不一样就是问题所在。

二、建账号 ​

sql
-- 应用账号，限制来源网段
CREATE USER 'app_user'@'10.0.%'
IDENTIFIED BY 'StrongPassword_123!';

-- 只读账号
CREATE USER 'readonly'@'10.0.%'
IDENTIFIED BY 'ReadonlyPassword_123!';

@'10.0.%' 限制只能从 10.0 网段连。% 是通配符。别用 '%'（任意地址）——除非真要外网连，否则收紧来源。

密码要符合策略——8.4 默认要求大小写+数字+特殊字符+长度。设太简单会报错。

锁定和解锁 ​

sql
ALTER USER 'app_user'@'10.0.%' ACCOUNT LOCK;    -- 锁定，不能连
ALTER USER 'app_user'@'10.0.%' ACCOUNT UNLOCK;  -- 解锁

离职或临时禁用账号用 LOCK，不用直接删（删了想恢复得重建）。

删账号前确认连接 ​

sql
SELECT user, host, db, command, time, state
FROM information_schema.processlist
WHERE user = 'app_user';

直接删正在被大量使用的账号，应用立刻报错。 删前先确认没有活跃连接，或先 LOCK 让新连接进不来，等旧连接断完再删。

三、授权和回收 ​

建了账号还没权限——默认啥都干不了。用 GRANT 给权限：

sql
-- 应用账号：单库的增删改查
GRANT SELECT, INSERT, UPDATE, DELETE
ON app_db.* TO 'app_user'@'10.0.%';

-- 只读账号：只能查
GRANT SELECT ON app_db.* TO 'readonly'@'10.0.%';

ON app_db.* 限定在 app_db 这个库。.* 是库下所有表。也可以精确到表 ON app_db.servers。

查看某账号的权限：

sql
SHOW GRANTS FOR 'app_user'@'10.0.%'\G

回收权限：

sql
REVOKE DELETE ON app_db.* FROM 'app_user'@'10.0.%';

权限范围 ​

权限能干什么
SELECT查
INSERT/UPDATE/DELETE增改删
CREATE/DROP/ALTER建删改表结构
ALL所有权限（≈root 对这个库）
RELOAD/REPLICATION CLIENT看状态、复制（给监控用）

原则——最小权限：只给业务需要的，别图省事给 ALL。需要看连接状态的监控账号给 REPLICATION CLIENT 就行，不用 ALL。

四、密码策略和改密 ​

sql
-- 改密码
ALTER USER 'app_user'@'10.0.%' IDENTIFIED BY 'NewStrongPassword_456!';

-- 看密码策略
SHOW VARIABLES LIKE 'validate_password%';

8.4 密码策略默认 MEDIUM——长度 8+、含大小写数字特殊字符。生产可以调到 STRICT（更严）。

改应用密码的流程——别直接改，会让应用断连：

text
1. 新建账号 + 新密码
2. 授权（和旧账号一样）
3. 应用切到新账号
4. 观察连接正常后，删旧账号

直接改密码的瞬间，应用用旧密码连会失败。新建账号切换的方式零中断。

五、角色（8.0+） ​

权限多了不好管——十个应用账号都要同样的权限，一个个 GRANT 繁琐。角色（role）把一组权限打包：

sql
CREATE ROLE 'app_role';
GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO 'app_role';

-- 给账号授角色
GRANT 'app_role' TO 'app_user1'@'10.0.%';
GRANT 'app_role' TO 'app_user2'@'10.0.%';

改权限只改角色，所有用了这个角色的账号自动生效。账号多、权限一致的场景用角色省事。

六、SSL 连接 ​

跨网络连 MySQL，密码可能被截获。SSL 加密连接防止窃听。强制某账号必须 SSL 连：

sql
ALTER USER 'app_user'@'10.0.%' REQUIRE SSL;

REQUIRE SSL 要求加密连接。更严格可以 REQUIRE X509——不仅加密还要校验客户端证书。跨机房或公网连数据库必须开 SSL，内网同机可以不开。

七、账号巡检 ​

定期检查账号有没有问题：

sql
-- 看所有账号
SELECT user, host, account_locked FROM mysql.user;

-- 找权限过大的（ALL 权限）
SELECT user, host FROM mysql.user WHERE Super_priv = 'Y';

-- 找没有密码或弱密码的
SELECT user, host FROM mysql.user WHERE authentication_string = '';

巡检能发现：离职没删的账号、权限给太宽的、没设密码的匿名账号。生产定期跑一遍。

账号权限之后 ​

谁能连、能干什么——账号权限搞定了。但数据要防丢——硬盘可能坏、误删可能发生。下一篇讲备份与恢复：怎么备份、怎么从备份恢复、怎么恢复到某个时间点。