mysql> drop user whb;
ERROR 1396 (HY000): Operation DROP USER failed for 'whb'@'%'

账号明明叫 whb,报错里却多了个 @'%'。MySQL 的账号标识从来不是用户名,而是“用户名 + 允许登录的主机”。

账号是用户名加主机

use mysql;
select host, user, authentication_string from user;
 
create user 'whb'@'localhost' identified by '12345678';
select user, host, authentication_string from user;

host 为 localhost 表示只能从本机登录;% 表示任意主机;192.168.1.% 是一个网段。'whb'@'localhost' 和 'whb'@'%' 是两条独立记录,可以设不同密码、给不同权限,删掉一条另一条还在。

省略 host 时,MySQL 补 %,所以 drop user whb 找的是 'whb'@'%',找不到就报 1396。删除、改密、授权都要写全 '用户名'@'主机'。

host 匹配有优先级:字面量优先于通配符。同一个用户名下同时有 'app'@'localhost' 和 'app'@'%',本机连接会落到 localhost 那条。排查当前连接落到哪个账号:

select user(), current_user();

user() 是客户端声明的身份,current_user() 是服务器实际匹配到的账号。两者不一致,说明 host 落到了另一条记录,权限自然可能对不上。

不要随手建 'user'@'%'。host 越宽,密码泄露后的攻击面越大;生产环境通常只留 'app'@'10.0.0.%' 这种网段账号,再给运维单独留一个 'dba'@'localhost'。

authentication_string 里不是明文密码

authentication_string 是认证插件算出来的摘要,不是 PASSWORD() 的结果。5.7 默认 mysql_native_password,8.0 默认 caching_sha2_password,同一串明文算出来的摘要不同。

课件给的改密语句是 set password = password('新的密码')。PASSWORD() 在 5.7 已标记废弃,8.0 移除,照抄会报函数不存在。现在用:

alter user 'whb'@'localhost' identified by 'N3w#Pass';
 
-- 或者
set password for 'whb'@'localhost' = 'N3w#Pass';

密码太简单会撞上 1819:

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
show variables like 'validate_password%';

validate_password.policy 从 LOW 到 STRONG:LOW 只查长度,MEDIUM 查数字、大小写和特殊字符,STRONG 再加字典检查。练习环境可以临时调低,生产别关。8.0 里它是组件,5.7 里是插件;没装时 show variables 查不到这些变量。

grant 的三段:权限、范围、账号

grant select on test.* to 'whb'@'localhost';

语法是 grant 权限列表 on 库.对象名 to '用户名'@'登录位置'。权限多了用逗号分隔,all privileges 表示该范围上的全部权限。范围有三种写法:

写法范围
*.*所有库的所有对象
test.*test 库的所有表、视图、存储过程
test.account单张表

课件把权限、它在 mysql.user 里对应的列、以及作用范围列成了一张表,对应关系是:

权限mysql.user 里的列管什么
SELECTSelect_priv表或列
INSERTInsert_priv表或列
UPDATEUpdate_priv表或列
DELETEDelete_priv表或列
CREATECreate_priv库、表、索引
DROPDrop_priv库、表、视图
GRANT OPTIONGrant_priv把权限再授出去
REFERENCESReferences_priv库或表
ALTERAlter_priv表
INDEXIndex_priv表
CREATE VIEWCreate_view_priv视图
SHOW VIEWShow_view_priv视图
ALTER ROUTINEAlter_routine_priv存储程序和函数
CREATE ROUTINECreate_routine_priv存储程序和函数
EXECUTEExecute_priv存储程序和函数
FILEFile_priv服务器主机上的文件
CREATE TEMPORARY TABLESCreate_tmp_table_priv服务器管理
LOCK TABLESLock_tables_priv服务器管理
CREATE USERCreate_user_priv服务器管理
PROCESSProcess_priv服务器管理
RELOADReload_priv服务器管理
REPLICATION CLIENTRepl_client_priv服务器管理
REPLICATION SLAVERepl_slave_priv服务器管理
SHOW DATABASESShow_db_priv服务器管理
SHUTDOWNShutdown_priv服务器管理
SUPERSuper_priv服务器管理

课件这张表把每个权限、它在 mysql.user 里对应的列名、以及作用范围列在一起;范围一栏决定了这条权限能落在全局、库、表还是存储程序上

新建账号默认只有 USAGE:能连上,但没有任何对象权限。给 whb 授了 test 库的 select 之后,show databases 才出现 test,select 能用,delete 报 1142:

show grants for 'whb'@'localhost';
 
revoke all on test.* from 'whb'@'localhost';

回收时范围要和授权时对齐。revoke all on test.* 收的是 test 库这一层,不会动到 *.* 上的全局权限。

课件在授权后写了 flush privileges;。今天的 MySQL 不需要:grant、revoke、create user、alter user 会立即更新授权表。flush privileges 只在直接改 mysql 库的权限表之后才需要,比如手写 update mysql.user。

课件那条 grant select on test.* to 'whb'@'localhost' identified by '密码' 在 8.0 也走不通:grant 不能再顺手建用户,会报 ERROR 1410 (42000): You are not allowed to create a user with GRANT。拆成两步:

create user 'app'@'10.0.0.%' identified by 'Str0ng#Pass';
grant select, insert, update, delete on order_sys.* to 'app'@'10.0.0.%';

权限表、角色与最小权限

mysql 库里不止 user 一张权限表:

表管哪一层
mysql.user全局权限和账号认证信息
mysql.db库级权限
mysql.tables_priv表级权限
mysql.columns_priv列级权限
mysql.procs_priv存储过程和函数
mysql.role_edges角色授予关系,8.0
mysql.default_roles默认角色,8.0

服务器判断一次操作,会从全局、库、表、列逐层找权限:有一层能覆盖这次操作就放行,一层都没有就拒绝。

角色是 8.0 才有的对象。账号一多,逐个授权很难维护,可以先把权限打包:

create role 'read_only';
grant select on order_sys.* to 'read_only';
grant 'read_only' to 'report'@'%';
set default role 'read_only' to 'report'@'%';

5.7 没有角色,只能把授权语句写成脚本,别靠手工点。

最小权限落到一个业务账号上,是这样:

create user 'app'@'10.0.0.%' identified by 'Str0ng#Pass';
grant select, insert, update, delete on order_sys.* to 'app'@'10.0.0.%';

这个账号能读写 order_sys,不能建表、改表、删库,也碰不到别的库;host 收到网段,换网段就连不上。备份账号单独建,只给 select, lock tables, show view, event, trigger。with grant option 不要给业务账号,它等于把继续授权的权力也交出去,配上 *.* 就是造了第二个 root。

张三只该操作 mytest,李四只该操作 msg;如果都给 root,两个人都能操作所有库,一次误操作的爆炸半径就是整台实例

delete 权限尤其危险。有些团队干脆不给业务账号 delete,改成逻辑删除、只给 update;代价是表里多一个 is_deleted 字段,收益是误删可以恢复。

权限不对时先看错误码,方向差得很远:

  • 1045:认证失败,账号、host、密码、插件,四选一。
  • 1044:连上了,但没有这个库的权限。
  • 1142:库能进,但这张表不让干这件事。
  • 1396:账号不存在,或者 host 没写对。