MySQL 账号和权限管理笔记整理

99%的焦虑都来自于虚度时间和没有好好做事，所以唯一的解决办法就是行动起来，认真做完事情，战胜焦虑，战胜那些心里空荡荡的时刻，而不是选择逃避。不要站在原地想象困难，行动永远是改变现状的最佳方式

写在前面

---

• 博文内容极客时间老师的专栏学习笔记整理:
• https://time.geekbang.org/column/article/802420
• 理解不足小伙伴帮忙指正 :),生活加油

99%的焦虑都来自于虚度时间和没有好好做事，所以唯一的解决办法就是行动起来，认真做完事情，战胜焦虑，战胜那些心里空荡荡的时刻，而不是选择逃避。不要站在原地想象困难，行动永远是改变现状的最佳方式

---

账号和权限管理

在MySQL数据库中，账号和权限管理是非常重要的部分，它确保了数据库的安全性和数据的完整性。

在 mysql 中一个用户由两部分组成，username 是用户名，host 是允许登录数据库的客户端的主机名或 IP。host 中可以使用通配符，使用百分号 “%” 匹配任意字符串，使用下划线 “_” 匹配一个字符

create user 'username'@'host' identified by 'complex_password';

同名用户条目的优先级

MySQL 允许创建 username 相同，但 host 不同的用户。

create user 'u03'@'%' identified by 'somepassword';
create user 'u03'@'172.16.0.0/16' identified by 'somepassword';
create user 'u03'@'mysql02' identified by 'somepassword';
create user 'u03'@'172.16.121.%' identified by 'somepassword';
create user 'u03'@'172.16.121.237' identified by 'somepassword';

可以通过 mysql.user 来查看创建的用户数据

mysql> select user,host from mysql.user where user='u03';
+------+----------------+
| user | host           |
+------+----------------+
| u03  | %              |
| u03  | 172.16.0.0/16  |
| u03  | 172.16.121.%   |
| u03  | 172.16.121.237 |
| u03  | mysql02        |
+------+----------------+
5 rows in set (0.00 sec)

登录数据库之后，可以用函数 current_user 来获取当前的登录用户

# mysql -u u03 -psomepassword -h172.16.121.234

mysql> select current_user();
+-------------------+
| current_user()    |
+-------------------+
| u03@172.16.0.0/16 |
+-------------------+
1 row in set (0.01 sec)

或者用 show grants 命令也能看到当前的登录用户

mysql> show grants;
+---------------------------------------------+
| Grants for u03@172.16.0.0/16                |
+---------------------------------------------+
| GRANT USAGE ON *.* TO `u03`@`172.16.0.0/16` |
+---------------------------------------------+
1 row in set (0.00 sec)

在登陆的时候，MySQL 会把所有的用户记录按优先级从高到低的顺序排列，缓存到内存里。服务端接收到客户端发起的连接请求后，从请求包中解析出用户名和密码信息

mysql.user 表中用户条目的优先级如何确定呢？基本的规则是这样的：

• IP 条目的优先级最高。IP 条目中没有通配符，精确的 IP 和 IP 地址段都是 IP 条目。
• 精确 IP 的优先级比 IP 地址段的优先级高。
• 对于 2 个 IP 地址段，前缀长的优先级更高。比如 172.16.121.0/24 优先级比 172.16.0.0/16 高。
• 不使用通配符的条目比使用通配符的条目优先级高。
• 对于都使用了通配符的条目，则根据第一个通配符在 host 字段中出现的位置来判断优先级。通配符出现的位置越靠前，优先级越低。比如 ‘%’ 的优先级最低，’abc%’ 的优先级比 ‘abcd%’ 低。

'u03'@'172.16.121.237'
'u03'@'172.16.0.0/16'
'u03'@'mysql02'
'u03'@'172.16.121.%'
'u03'@'%'

在真实环境中，我们经常会设置 skip_name_resolve，这样 MySQL 就只会根据 IP 来验证用户，不需要再将 IP 反解析成主机名。

## my.cnf
skip_name_resolve

密码验证组件

不要给 MySQL 用户设置过于简单的密码，可以通过密码验证组件来强制密码的复杂度。

使用 RPM 安装的 MySQL 默认就已经开启了密码验证组件。

如果你使用了二进制安装，可以用命令 INSTALL COMPONENT 来启用密码验证。

mysql> INSTALL COMPONENT 'file://component_validate_password';
Query OK, 0 rows affected (1.03 sec)

无法创建密码过于简单的用户，报错提示

mysql> create user 'ux'@'%' identified by 'simplepassword';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

mysql> create user 'ux'@'%' identified by 'Complex-Password-2024';
Query OK, 0 rows affected (1.04 sec)

密码验证组件有几个参数可以配置，你可以根据自己的需求适当调整这些参数。

mysql> show variables like 'validate_password%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password.check_user_name    | ON     |
| validate_password.dictionary_file    |        |
| validate_password.length             | 8      |
| validate_password.mixed_case_count   | 1      |
| validate_password.number_count       | 1      |
| validate_password.policy             | MEDIUM |
| validate_password.special_char_count | 1      |
+--------------------------------------+--------+

权限管理

早期版本中，如果被授权的用户不存在，那么在执行 grant 语句时，会自动创建这个用户。

在 MySQL 5.7中，SQL_MODE 中加入了 NO_AUTO_CREATE_USER 选项，用来避免这种 grant 语句自动创建用户的行为。

到了 MySQL 8.0，已经不再支持 NO_AUTO_CREATE_USER 选项了。

grant privileges
on something
to 'user'@'host';

执行 grant 时，被授权的用户必须已经存在，否则会报错。

mysql> grant select on *.* to 'readonly'@'%';
ERROR 1410 (42000): You are not allowed to create a user with GRANT

在 MySQL 中，有的权限是全局的，这些权限跟某个具体的数据库没有关系，授权时需要使用 on *.*

grant process on *.* to 'u03'@'%';

比如这个例子给用户 u03 授权了查看 process 列表的权限。

有的权限跟数据库或数据库中的对象相关，授权时可以指定具体的数据库或数据库对象。下面这个 grant 语句给用户 u03 授权了数据库 db01 的 DDL 权限。

grant create,index,alter,drop on db01.* to 'u03'@'%';

授权后，u03 可以创建库名为 db01 的数据库，并在这个库中创建表、修改表结构、DROP 表，但是不能读写表中的数据。

mysql> show grants;
+----------------------------------------------------+
| Grants for u03@%                                   |
+----------------------------------------------------+
| GRANT USAGE ON *.* TO `u03`@`%`                    |
| GRANT CREATE, INDEX, DROP, ALTER ON `db01`.* TO `u03`@`%` |
+----------------------------------------------------+

## 可以创建库名为db01的数据库
mysql> create database db01;
Query OK, 1 row affected (2.91 sec)

## 但是不能创建其他数据库
mysql> create database db02;
ERROR 1044 (42000): Access denied for user 'u03'@'%' to database 'db02'

可以创建表，修改表结构，DROP表，但是不能读写表中的数据

## 可以创建表，修改表结构，DROP表，但是不能读写表中的数据
mysql> create table t1(a int, b int);
Query OK, 0 rows affected (11.54 sec)

mysql> alter table t1 add c int;
Query OK, 0 rows affected (4.98 sec)
Records: 0  Duplicates: 0  Warnings: 0

不能读写表数据

mysql> insert into t1 values(1,2,3);
ERROR 1142 (42000): INSERT command denied to user 'u03'@'192.168.113.13' for table 't1'

mysql> select * from t1;
ERROR 1142 (42000): SELECT command denied to user 'u03'@'192.168.113.13' for table 't1'

需要给账号添加相应的权限后，才能访问表中的数据。

mysql> grant select,insert,update,delete on db01.* to 'u03'@'%';
Query OK, 0 rows affected (2.31 sec) 

grant 语句中，授权对象（库名和对象名）也可以使用通配符，使用 "%" 匹配任意字符，使用 "_" 匹配一个字符。

grant create,drop,alter,index on `db__`.* to 'u03'@'%';

授权后，u03 用户可以创建库名以 db 开头，并且库名长度是 4 个字节的数据库。

mysql> create database db_1;
Query OK, 1 row affected (1.89 sec)

mysql> create database db_2;
Query OK, 1 row affected (0.12 sec)

mysql> create database db_10;
ERROR 1044 (42000): Access denied for user 'u03'@'%' to database 'db_10'

如果库名中有下划线 "*"，可以在 grant 时使用转义符对通配符 "*" 进行转义。下面的 SQL 把 db_1 库的读写权限赋给了 u03 用户

mysql> grant select,insert,update,delete on `db\_1`.* to 'u03'@'%';
Query OK, 0 rows affected (0.91 sec)

使用 show grants 命令查看 u03 用户当前的权限

mysql> show grants;
+----------------------------------------------------------------+
| Grants for u03@%                                               |
+----------------------------------------------------------------+
| GRANT USAGE ON *.* TO `u03`@`%`                                |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `db01`.* TO `u03`@`%`  |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `db\_1`.* TO `u03`@`%` |
| GRANT CREATE, DROP, INDEX, ALTER ON `db__`.* TO `u03`@`%`      |
+----------------------------------------------------------------+

最小权限原则

最小权限原则管理数据库用户的权限时，要遵循最小权限原则。

root 用户要交给少数管理人员管理，其他任何人不能使用 root 用户。有的公司出于安全考虑，还会删除系统自带的 root 用户，并单独创建一个超级管理员用户。给不同的业务方分配不同的用户，并按业务的实际需求授予最小的权限。

举例来说，如果业务方的需求是同步数据库中的数据，只需要对库表授予 SELECT 权限。对于一般的应用程序，需要读写数据，授予 SELECT、INSERT、UPDATE、DELETE、EXECUTE 这些权限就可以了。

对于 DBA 或运维人员，需要执行数据库变更，可以授予CREATE、ALTER、DROP、INDEX 等 DDL 权限，以及 PROCESS、SUPER等管理权限

开发环境和生产环境的用户，不要使用相同的密码。不同的业务方共用同一个用户、授予用户超出需要的权限、开发环境和生产环境使用相同的用户名和密码，都会给数据库安全带来极大的风险。

有时候为了方便，你可能会把all privileges 授予一个用户，然后所有业务方都使用这个用户来访问数据库。这是非常危险的，千万不要这么做。

博文部分内容参考

© 文中涉及参考链接内容版权归原作者所有，如有侵权请告知 :)

---

https://time.geekbang.org/column/article/802420

---

© 2018-至今 liruilonger@gmail.com, 保持署名-非商用-相同方式共享(CC BY-NC-SA 4.0)