MySQL 账户

一、账户名称

1. 账户名组成

MySQL 账户名由用户名和主机名组成，格式为 'user_name'@'host_name'，可针对不同主机连接的同用户名用户创建不同账户。

2. 与操作系统用户名/密码的区别

MySQL 用户名/密码用于自身身份验证，与操作系统的无关。
MySQL 用户名最长 32 个字符，操作系统用户名最大长度可能不同。
存储在 mysql.user 表中的密码经特定插件算法加密。
若用户名/密码仅含 ASCII 字符，不受字符集影响；含非 ASCII 字符可能需指定字符集验证。

3. 账户名格式

（1）用户名（user_name）

为空时表示匿名用户，如 ''@'localhost'。

（2）主机名（host_name）

'user_name'@'%' 中主机名部分可选，与 'user_name' 等效，% 可省略。
可为主机名或 IP 地址（IPv4/IPv6），如 127.0.0.1（IPv4 环回接口）、::1（IPv6 环回接口）。
允许使用 ％ 和 _ 通配符，但 MySQL 不会对以数字和点开头的主机名执行匹配（可能被利用）。
IPv4 地址可使用网络掩码（host_ip/netmask），支持 CIDR 表示法，网络掩码符号不支持 IPv6 地址。示例：
```
'david'@'198.51.100.0/255.255.255.0'
'david'@'198.51.100.44/24'  # CIDR 表示法
```
多个匹配项时，按规则排序后选择第一行。

（3）引号使用

用户名和主机名合法时无需引号，可用反引号（`）、单引号（'）或双引号（"）分别引用。

4. 预留账户

'root'@'localhost'：管理员账户。
'mysql.sys'@'localhost'：root 备用账户，root 被删除时可用，已锁定，不能客户端连接。
'mysql.session'@'localhost'：插件内部访问服务器用，已锁定，不能客户端连接。
'mysql.infoschema'@'localhost'：作为 INFORMATION_SCHEMA 视图的 DEFINER，root 备用账户，已锁定，不能客户端连接。

5. 查询账户名的函数

user()
current_user()
current_user

二、账户类别

1. 系统账户和普通账户

（1）系统账户

具有 SYSTEM_USER 权限。
可修改系统/普通账户。
操作影响：账户操作、杀死当前会话及语句、为存储对象设置 DEFINER 属性、指定强制角色（具有 SYSTEM_USER 权限的角色不能列在 mandatory_roles 系统变量中）。

（2）普通账户

无 SYSTEM_USER 权限。
只能修改普通账户。
可为其赋予 SYSTEM_USER 权限，但可能需其他权限。

2. 系统会话和普通会话

系统会话：具有 SYSTEM_USER 权限。
普通会话：无 SYSTEM_USER 权限。

3. 保护系统账户不被常规账户操纵

普通账户/角色不应授予 SYSTEM_USER 权限和 mysql 库的修改权限，可通过以下方式处理：

（1）回收 `SYSTEM_USER` 权限和对 `mysql` 库的权限

CREATE USER u1 IDENTIFIED BY 'password';
GRANT ALL ON *.* TO u1 WITH GRANT OPTION;
REVOKE SYSTEM_USER ON *.* FROM u1;
REVOKE ALL ON mysql.* FROM u1;

（2）使对 `mysql` 库只有只读权限

CREATE USER u2 IDENTIFIED BY 'password';
GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO u2;
REVOKE INSERT, UPDATE, DELETE ON mysql.* FROM u2;

（3）使对 `mysql` 库的指定表和列具有只读权限

CREATE USER u3 IDENTIFIED BY 'password';
GRANT ALL ON *.* TO u3;
REVOKE ALL ON mysql.* FROM u3;
GRANT SELECT ON mysql.db TO u3;
GRANT SELECT(Host,User) ON mysql.user TO u3;

三、账户和权限

1. 为账户分配权限

（1）创建账户并授予权限

CREATE USER 'finley'@'localhost' IDENTIFIED BY 'password';
GRANT ALL ON *.* TO 'finley'@'localhost' WITH GRANT OPTION;

CREATE USER 'finley'@'%.example.com' IDENTIFIED BY 'password';
GRANT ALL ON *.* TO 'finley'@'%.example.com' WITH GRANT OPTION;

CREATE USER 'admin'@'localhost' IDENTIFIED BY 'password';
GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost';

CREATE USER 'dummy'@'localhost';

CREATE USER 'custom'@'localhost' IDENTIFIED BY 'password';
GRANT ALL ON bankaccount.* TO 'custom'@'localhost';

CREATE USER 'custom'@'host47.example.com' IDENTIFIED BY 'password';
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON expenses.* TO 'custom'@'host47.example.com';

CREATE USER 'custom'@'%.example.com' IDENTIFIED BY 'password';
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON customer.addresses TO 'custom'@'%.example.com';

（2）检查账户权限和属性

mysql> SHOW GRANTS FOR 'admin'@'localhost';
+-----------------------------------------------------+
| Grants for admin@localhost                          |
+-----------------------------------------------------+
| GRANT RELOAD, PROCESS ON *.* TO 'admin'@'localhost' |
+-----------------------------------------------------+

mysql> SET print_identified_with_as_hex = ON;
mysql> SHOW CREATE USER 'admin'@'localhost'\G
*************************** 1. row ***************************
CREATE USER for admin@localhost: CREATE USER 'admin'@'localhost'
IDENTIFIED WITH 'caching_sha2_password'
AS 0x24412430303524301D0E17054E2241362B1419313C3E44326F294133734B30792F436E77764270373039612E32445250786D43594F45354532324B6169794F47457852796E32
REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK
PASSWORD HISTORY DEFAULT
PASSWORD REUSE INTERVAL DEFAULT
PASSWORD REQUIRE CURRENT DEFAULT

（3）回收账户权限

REVOKE ALL ON *.* FROM 'finley'@'%.example.com';
REVOKE RELOAD ON *.* FROM 'admin'@'localhost';
REVOKE INSERT,UPDATE,DELETE ON customer.addresses FROM 'custom'@'%.example.com';

mysql> SHOW GRANTS FOR 'admin'@'localhost';  -- 验证
+---------------------------------------------+
| Grants for admin@localhost                  |
+---------------------------------------------+
| GRANT PROCESS ON *.* TO 'admin'@'localhost' |
+---------------------------------------------+

（4）删除账户

DROP USER 'finley'@'localhost';
DROP USER 'finley'@'%.example.com';
DROP USER 'admin'@'localhost';
DROP USER 'dummy'@'localhost';

2. 回收部分权限

（1）系统变量 `partial_revokes`

控制是否可对账户设置权限限制，默认禁用，禁用时回收部分权限会报错。

-- 未开启
mysql> CREATE USER u1;
mysql> GRANT SELECT, INSERT ON *.* TO u1;
mysql> REVOKE INSERT ON world.* FROM u1;
ERROR 1141 (42000): There is no such grant defined for user 'u1' on host '%'

-- 开启
SET PERSIST partial_revokes = ON;

-- 验证
mysql> REVOKE INSERT ON world.* FROM u1;
mysql> SHOW GRANTS FOR u1;
+------------------------------------------+
| Grants for u1@%                          |
+------------------------------------------+
| GRANT SELECT, INSERT ON *.* TO `u1`@`%`  |
| REVOKE INSERT ON `world`.* FROM `u1`@`%` |
+------------------------------------------+

启用后，若有账户使用权限限制，则禁用失败。

（2）权限限制的记录位置

存储在 mysql.user 系统表的 User_attributes 列，示例：

mysql> SELECT User, Host, User_attributes->>'$.Restrictions'
       FROM mysql.user WHERE User_attributes->>'$.Restrictions' <'';
+------+------+------------------------------------------------------+
| User | Host | User_attributes->>'$.Restrictions'                   |
+------+------+------------------------------------------------------+
| u1   | %    | [{"Database": "world", "Privileges": ["INSERT"]}] |
+------+------+------------------------------------------------------+

（3）限制

schema 部分支持 SQL 通配符 % 和 _。
可对不存在的 schema 部分回收，但前提是已拥有全局权限（无权限则回收无意义）。
权限限制可继承（账户给其他账户授权时，其他账户可能继承）。
权限限制可能被移除：通过无限制账户全局授权、schema 级别授权、回收全局权限。
若账户在全局和 schema 级别都有权限，需回收两次才能完成限制。

（4）部分回收和显式的 schema 授权

可直接授予 schema 级别权限，而非全局权限。
也可授予全局权限后，通过部分回收做限制。

（5）禁用部分回收

先移除权限限制：

查看有权限限制的账户：

SELECT User, Host, User_attributes->>'$.Restrictions' FROM mysql.user WHERE User_attributes->>'$.Restrictions' <'';

移除限制的方式：

-- 全局授权
GRANT INSERT, DELETE ON *.* TO u1;
-- schema 级别授权
GRANT INSERT, DELETE ON world.* TO u1;
-- 回收全局权限
REVOKE INSERT, DELETE ON *.* FROM u1;
-- 移除账户
DROP USER u1;

再禁用系统变量：
```
SET PERSIST partial_revokes = OFF;
```

（6）部分回收和复制

复制结构中，若存在部分回收的权限限制，所有主机的权限设置应一致。

3. 权限变更的影响

使用账户变更语句修改账户，服务器自动重新加载授权表。
使用 INSERT、UPDATE、DELETE 语句修改授权表，需手动重新加载（flush privileges、mysqladmin flush-privilegs、mysqladmin reload），否则不生效。
授权表重新加载影响所有会话。
全局权限和密码对已连接客户端无影响，仅对后续连接生效。

四、账户和密码

1. 锁定账户

客户端无法登录锁定账户，不影响代理账户或存储程序/视图的使用。

（1）语法

通过 CREATE USER/ALTER USER 加 ACCOUNT LOCK/ACCOUNT UNLOCK 实现：

CREATE USER test2@localhost IDENTIFIED BY 'test2' ACCOUNT LOCK;
ALTER USER test2@localhost ACCOUNT UNLOCK;

（2）查看锁定状态

mysql.user 表的 account_locked 列。
状态变量 Locked_connects：记录尝试登录锁定账户的次数。

2. 分配密码

（1）创建用户同时分配

CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'password';

（2）为已有账户分配

ALTER USER 'jeffrey'@'localhost' IDENTIFIED BY 'password';

（3）非匿名用户为自身分配

ALTER USER USER() IDENTIFIED BY 'password';

（4）命令行分配（不安全）

mysqladmin -u user_name -h host_name password "password"

五、账户资源限制

1. 系统变量 `max_user_connections`

限制最大客户端连接数，仅限制连接数量，不限制连接后的操作。

2. 对单个账户进行限制

（1）语法

通过 CREATE USER/ALTER USER 加 WITH 子句设置，限定项包括：

MAX_QUERIES_PER_HOUR：每小时查询数量。
MAX_UPDATES_PER_HOUR：每小时更新数量。
MAX_CONNECTIONS_PER_HOUR：每小时连接次数。
MAX_USER_CONNECTIONS：并发连接数（与全局 max_user_connections 冲突时，以账户限制为准；账户限制为 0 则以全局为准）。

（2）设置限制

创建用户时：

CREATE USER 'francis'@'localhost' IDENTIFIED BY 'frank'
 WITH MAX_QUERIES_PER_HOUR 20
      MAX_UPDATES_PER_HOUR 10
      MAX_CONNECTIONS_PER_HOUR 5
      MAX_USER_CONNECTIONS 2;

修改用户时：

mysql> ALTER USER 'francis'@'localhost' WITH MAX_QUERIES_PER_HOUR 100;

（3）解除限制

设置为 0 表示无限制：

mysql> ALTER USER 'francis'@'localhost' WITH MAX_CONNECTIONS_PER_HOUR 0;

（4）存储位置

限制存储在 mysql.user 表的 max_questions、max_updates、max_connections、max_user_connections 列。

（5）资源计数

按账户计数，多连接不增加限制数。
重置方式：
- 重置所有账户：flush user_resources、重新加载授权表（flush privileges;、mysqladmin reload）。
- 重置单个账户：ALTER USER 重新设置。
- 服务器启动后从 0 开始计数。

红炉点雪

MySQL 账户

MySQL 账户

一、账户名称

1. 账户名组成

2. 与操作系统用户名/密码的区别

3. 账户名格式

（1）用户名（user_name）

（2）主机名（host_name）

（3）引号使用

4. 预留账户

5. 查询账户名的函数

二、账户类别

1. 系统账户和普通账户

（1）系统账户

（2）普通账户

2. 系统会话和普通会话

3. 保护系统账户不被常规账户操纵

（1）回收 SYSTEM_USER 权限和对 mysql 库的权限

（2）使对 mysql 库只有只读权限

（3）使对 mysql 库的指定表和列具有只读权限

三、账户和权限

1. 为账户分配权限

（1）创建账户并授予权限

（2）检查账户权限和属性

（3）回收账户权限

（4）删除账户

2. 回收部分权限

（1）系统变量 partial_revokes

（2）权限限制的记录位置

（3）限制

（4）部分回收和显式的 schema 授权

（5）禁用部分回收

（6）部分回收和复制

3. 权限变更的影响

四、账户和密码

1. 锁定账户

（1）语法

（2）查看锁定状态

2. 分配密码

（1）创建用户同时分配

（2）为已有账户分配

（3）非匿名用户为自身分配

（4）命令行分配（不安全）

五、账户资源限制

1. 系统变量 max_user_connections

2. 对单个账户进行限制

（1）语法

（2）设置限制

（3）解除限制

（4）存储位置

（5）资源计数

（1）回收 `SYSTEM_USER` 权限和对 `mysql` 库的权限

（2）使对 `mysql` 库只有只读权限

（3）使对 `mysql` 库的指定表和列具有只读权限

（1）系统变量 `partial_revokes`

1. 系统变量 `max_user_connections`