MySQL运维实战（2）MySQL用户和权限管理
作者：俊达
引言
MySQL数据库系统，拥有强大的控制系统功能，可以为不同用户分配特定的权限，这对于运维来说至关重要，因为它可以帮助管理员控制用户对数据库的访问权限。用户管理涉及创建、修改和删除数据库用户，权限管理则控制用户对数据库的访问和操作。MySQL提供了灵活的权限控制机制，允许管理员根据需要为每个用户分配特定的权限，确保数据安全和合规性。正确的用户和权限管理策略有助于防止未经授权的访问和恶意操作，提高数据库的安全性和稳定性。
1 基本命令
创建用户
使用create user命令创建用户
create user 'username'@'host' identified by 'passwd';
删除用户
drop user 'username'@'host';
修改用户密码
alter user 'username'@'host' identified by 'new_passwd';
查看用户权限
mysql> show grants for 'username'@'host';
+-----------------------------------------+
| Grants for username@host |
+-----------------------------------------+
| GRANT USAGE ON *.* TO 'username'@'host' |
+-----------------------------------------+
mysql> show grants for 'root'@'localhost';
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
mysql> show grants for 'mysql.session'@'localhost';
+-----------------------------------------------------------------------+
| Grants for mysql.session@localhost |
+-----------------------------------------------------------------------+
| GRANT SUPER ON *.* TO 'mysql.session'@'localhost' |
| GRANT SELECT ON `performance_schema`.* TO 'mysql.session'@'localhost' |
| GRANT SELECT ON `mysql`.`user` TO 'mysql.session'@'localhost' |
+-----------------------------------------------------------------------+
新创建的用户只有usage权限
授予权限
使用grant授权，grant的基本语法：
grant [privilege] on [db].[obj] to 'user'@'host' [with grant option];
- privilege: 授权给用户的具体权限，多个权限可以使用逗号分割。具体权限见下面表格。
- db: 授权的数据库名称，可以使用''代表所有数据库，使用'_'匹配一个字符。不能加引号，加了引号就不是通配符了。
- obj: 授权的对象名称，可以使用''表示所有对象。不能加引号，加了引号就不是通配符了。
- 'user'@'host': 授权的用户名。授权时host不支持通配符。必须匹配某个具体的用户。
- with grant option:
将授权的权限也给予用户。比如给user赋予select权限，user可以查询数据，但是user不能把查询数据的权限赋予其他用户。如果给user授权时加上with - grant option，则user可以将权限赋予其他用户。
撤销权限
revoke [privilege] on [db].[obj] from 'user'@'host';
2 用户管理
mysql用户由两部分组成，用户名和登陆主机。登陆主机限制了用户登陆数据库的服务器地址信息。登陆主机可以使用%, _通配符
%: 匹配任何字符串。
_: 匹配任意一个字符。
使用create user语句创建用户。
早期版本，执行grant语句时，如果被授权的用户不存在，会自动创建用户。不过不推荐这种做法。
下面是一个例子:
mysql> show variables like '%sql_mode%';
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
### 5.7 SQL_MODE默认有 NO_AUTO_CREATE_USER, 表示执行grant语句时不自动创建用户。
mysql> grant select on *.* to 'userx'@'%';
ERROR 1133 (42000): Cant find any matching row in the user table
### 修改sql_mode
mysql> set sql_mode='';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------------------------------------------------------+
| Warning | 3090 | Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release. |
+---------+------+------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
### 再次执行grant语句，授权成功，但是提示有warning
mysql> grant select on *.* to 'userx'@'%';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1287 | Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement. |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
### 默认创建的用户无密码，有安全风险。
select user,host, authentication_string from mysql.user where user = 'userx';
+-------+------+-----------------------+
| user | host | authentication_string |
+-------+------+-----------------------+
| userx | % | |
+-------+------+-----------------------+
用户登陆验证的过程
mysql的所有用户，都存储在mysql.user表。登陆时，会基于mysql.user表的信息验证用户身份
- 获取用户登陆时的客户端IP信息。
- 如果mysql没有设置skip-name-resove参数，会将客户端IP信息转换成主机名。若转换失败，服务端错误日志中会记录warning日志
- 获取客户端指定的用户名
- 基于客户端IP或主机信息和客户端指定的用户名，到mysql.user表查找对应的记录。
- 如果能找到对应的记录，验证客户端密码。
- 如果找不到对应的记录，则登陆失败。
匿名用户
mysql支持匿名用户（用户名为空的用户）。在5.6之前的版本，使用mysql_install_db初始化数据库时，会默认创建匿名用户，允许从本地服务器登陆免密登陆数据库。
如果系统中有匿名用户，用户登陆验证时会先验证匿名用户。
### 创建用户auser
mysql> create user 'auser'@'%' identified by 'auser';
Query OK, 0 rows affected (0.14 sec)
mysql> create user ''@'localhost';
Query OK, 0 rows affected (0.00 sec)
### 本地使用auser登陆数据库
[root@box1 ~]# mysql -uauser -h127.0.0.1 -pauser
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'auser'@'localhost' (using password: YES)
## 登陆报错，1045是密码错误
### 使用auser, 不输入密码，登陆数据库
[root@box1 ~]# mysql -uauser -h127.0.0.1
Welcome to the MySQL monitor. Commands end with ; or \g.
mysql> show grants;
+--------------------------------------+
| Grants for @localhost |
+--------------------------------------+
| GRANT USAGE ON *.* TO ''@'localhost' |
+--------------------------------------+
# 可以登陆，但是登陆的用户是 ''@localhost'，而不是auser
### 使用IP auser登陆数据库
[root@box1 ~]# mysql -uauser -h172.16.20.51 -pauser
Welcome to the MySQL monitor. Commands end with ; or \g.
mysql> show grants;
+-----------------------------------+
| Grants for auser@% |
+-----------------------------------+
| GRANT USAGE ON *.* TO 'auser'@'%' |
+-----------------------------------+
1 row in set (0.00 sec)
引起上面问题的原因是系统中存在一个匿名用户。
把匿名用户删除后，使用auser可以正常登陆127.0.0.1
mysql> show grants for 'auser'@'%';
+-----------------------------------+
| Grants for auser@% |
+-----------------------------------+
| GRANT USAGE ON *.* TO 'auser'@'%' |
+-----------------------------------+
1 row in set (0.00 sec)
mysql> show grants for ''@'localhost';
+--------------------------------------+
| Grants for @localhost |
+--------------------------------------+
| GRANT USAGE ON *.* TO ''@'localhost' |
+--------------------------------------+
1 row in set (0.00 sec)
mysql> drop user ''@'localhost';
Query OK, 0 rows affected (0.00 sec)
[root@box3 ~]# mysql -uauser -h172.16.20.51 -pauser
Welcome to the MySQL monitor. Commands end with ; or \g.
mysql> show grants;
+-----------------------------------+
| Grants for auser@% |
+-----------------------------------+
| GRANT USAGE ON *.* TO 'auser'@'%' |
+-----------------------------------+
1 row in set (0.01 sec)
主机名称解析
默认配置下mysql服务端会将client ip转换成主机名，若转换失败，会在日志中记录warning信息。服务端会根据解析后的主机名来进行权限验证。
2021-04-06T10:13:43.500717Z 3 [Warning] IP address '172.16.20.53' could not be resolved: Name or service not known
一般会在mysql参数文件中增加skip-name-resolve选项。加上这个选项后，以主机名创建的用户信息不会被加载。
2021-04-06T10:07:49.322405Z 0 [Warning] 'user' entry 'user@box1' ignored in --skip-name-resolve mode.