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.

更多技术信息请查看云掣官网https://yunche.pro/?t=yrgw