适用版本：MySQL 8.0+。5.7 已于 2023-10-31 EOL，本文不再覆盖其语法（如 IDENTIFIED BY 嵌在 GRANT 里、PASSWORD() 函数等均已移除）。

速查

-- 建用户 + 授权（8.0 必须分两步，GRANT 不再支持隐式建用户/带密码）
CREATE USER 'tom'@'%' IDENTIFIED BY 'pwd123';
GRANT ALL PRIVILEGES ON *.* TO 'tom'@'%' WITH GRANT OPTION;

-- 查 / 改密 / 删
SHOW GRANTS FOR 'tom'@'%';
ALTER USER 'tom'@'%' IDENTIFIED BY 'new_pwd';
DROP USER 'tom'@'%';

用户

创建

CREATE USER 'username'@'host' IDENTIFIED BY 'password';
字段含义
username账号名
host允许从哪个主机登陆。localhost 仅本机；% 任意远端；192.168.1.% 限定网段
password登陆密码，可省略（不建议）

8.0 的默认认证插件是 caching_sha2_password（5.7 是 mysql_native_password）。绝大多数现代客户端已支持；但 PHP mysqlnd < 7.2.8、部分老 ORM/客户端连不上时，单独为该账号回退插件：

CREATE USER 'tom'@'%' IDENTIFIED WITH mysql_native_password BY 'pwd';

例子：

CREATE USER 'dog'@'localhost' IDENTIFIED BY '123456';
CREATE USER 'pig'@'192.168.1.%' IDENTIFIED BY '123456';
CREATE USER 'pig'@'%' IDENTIFIED BY '';   -- 空密码，等价于无密码
CREATE USER 'pig'@'%';                    -- 无密码

改密 / 锁定 / 过期

ALTER USER 是 8.0 修改账号属性的统一入口（SET PASSWORD 与 PASSWORD() 已废）。

-- 改他人密码
ALTER USER 'pig'@'%' IDENTIFIED BY 'new_pwd';

-- 改自己当前密码
ALTER USER USER() IDENTIFIED BY 'new_pwd';

-- 强制下次登陆改密
ALTER USER 'pig'@'%' PASSWORD EXPIRE;

-- 密码过期策略：每 90 天强制改一次
ALTER USER 'pig'@'%' PASSWORD EXPIRE INTERVAL 90 DAY;

-- 账号上锁 / 解锁
ALTER USER 'pig'@'%' ACCOUNT LOCK;
ALTER USER 'pig'@'%' ACCOUNT UNLOCK;

-- 连续失败登录自动锁定（8.0.19+）：5 次失败锁 1 天
ALTER USER 'pig'@'%' FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 1;

删除

DROP USER 'username'@'host';

权限

授予

GRANT priv_type [(col_list)] [, priv_type ...]
  ON [object_type] level
  TO user [, user ...]
  [WITH GRANT OPTION];

按层级（level）从大到小：

层级写法例子
全局*.*GRANT ALL PRIVILEGES ON *.* TO 'pig'@'%'
库db.*GRANT ALL ON my_db.* TO 'pig'@'%'
表db.tblGRANT SELECT, INSERT ON test.user TO 'pig'@'%'
列db.tbl + 列名GRANT SELECT (name, email) ON test.user TO 'pig'@'%'
存储例程PROCEDURE db.x / FUNCTION db.xGRANT EXECUTE ON PROCEDURE app.calc TO 'pig'@'%'

WITH GRANT OPTION 允许被授权者把自己的权限再传给别人。

查看

SHOW GRANTS FOR 'pig'@'%';
SHOW GRANTS FOR CURRENT_USER();
SHOW GRANTS FOR 'pig'@'%' USING 'app_read';  -- 含指定 role 展开后的权限

-- 从系统视图反查（适合脚本）
SELECT * FROM information_schema.user_privileges WHERE GRANTEE = "'pig'@'%'";

撤销

REVOKE priv_type ON level FROM user;
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'pig'@'%';  -- 一把全收

核心规则：REVOKE 的层级必须与 GRANT 时完全一致。 表级授权只能用表级回收，库级只能用库级回收——彼此不能跨级抵消。

当初 GRANT用这条 REVOKE结果
GRANT SELECT ON test.user TO 'pig'@'%'REVOKE SELECT ON *.* FROM 'pig'@'%'❌ 不生效
GRANT SELECT ON *.* TO 'pig'@'%'REVOKE SELECT ON test.user FROM 'pig'@'%'❌ 不生效
GRANT SELECT ON test.user TO 'pig'@'%'REVOKE SELECT ON test.user FROM 'pig'@'%'✅ 生效

FLUSH PRIVILEGES？

CREATE USER / GRANT / REVOKE / ALTER USER / DROP USER 都会自动重载授权表，不需要 FLUSH PRIVILEGES。只有当你绕过 DCL、直接 INSERT/UPDATE/DELETE mysql.user 等系统表时（强烈不推荐）才必须手动刷新。

角色

角色（ROLE）是命名的一组权限。给一批用户授同样权限时，比一条条 GRANT 简洁得多——一改全改。

-- 1. 建角色 + 给角色授权
CREATE ROLE 'app_read', 'app_write';
GRANT SELECT                    ON app_db.* TO 'app_read';
GRANT INSERT, UPDATE, DELETE    ON app_db.* TO 'app_write';

-- 2. 把角色授给用户
CREATE USER 'reporter'@'%' IDENTIFIED BY 'pwd';
GRANT 'app_read'              TO 'reporter'@'%';
GRANT 'app_read', 'app_write' TO 'tom'@'%';

-- 3. 登陆即激活（否则用户每次连进来要 SET ROLE 一下，应用层一般不会做）
SET DEFAULT ROLE ALL TO 'reporter'@'%';

用户侧查看/切换：

SELECT CURRENT_ROLE();
SET ROLE 'app_read';   -- 只激活某个
SET ROLE ALL;          -- 激活全部已授角色
SET ROLE NONE;         -- 全部停用

几条容易踩的坑：

  • 角色与用户共享 mysql.user 命名空间——CREATE ROLE 'x' 之后 CREATE USER 'x'@'%' 会冲突。
  • 角色默认 ACCOUNT LOCK，不能直接登陆，符合「角色不是账号」的语义。
  • 想全实例自动激活，可设服务器变量：SET PERSIST activate_all_roles_on_login = ON;

远程访问

开启 MySQL 远程访问 = 用户 host 字段允许远端 + 服务器 bind-address 不锁本机。

CREATE USER 'root'@'192.168.1.100' IDENTIFIED BY 'pwd';      -- 仅特定 IP
CREATE USER 'root'@'%' IDENTIFIED BY 'pwd';                  -- 任意 IP
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;

Ubuntu/Debian 上还要改服务端配置：

sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

# 默认 127.0.0.1（只听本机），改为 0.0.0.0 监听全部网卡后重启 mysql
bind-address = 0.0.0.0

生产环境千万别配 'root'@'%' —— 给应用账号用最小权限，给运维账号绑定 VPN/堡垒机 IP。

host 匹配规则

查看当前会话的账号：

SELECT CURRENT_USER();

user / host 字段的匹配模式：

HOSTUser匹配的连接
'thomas.loc.gov''fred'fred，从 thomas.loc.gov
'thomas.loc.gov'''任何用户，从 thomas.loc.gov
'%''fred'fred，任意主机
'%'''任何用户，任意主机
'%.loc.gov''fred'fred，loc.gov 任意子域
'x.y.%''fred'fred，x.y.* 主机
'144.155.166.177''fred'fred，指定 IP
'144.155.166.%''fred'fred，整个 C 类子网

⚠️ IP 通配符仅对纯 IP 生效。MySQL 会拒绝匹配「以数字 + 点」起始的主机名——防止有人把主机命名成 144.155.166.evil.com 来骗过 144.155.166.% 规则。1.2.foo.com 这种主机名永远不会命中 host 列。

References

– EOF –