MySQL运维实战（2）MySQL用户和权限管理

用户万里河山一局棋

作者：俊达
引言

MySQL数据库系统，拥有强大的控制系统功能，可以为不同用户分配特定的权限，这对于运维来说至关重要，因为它可以帮助管理员控制用户对数据库的访问权限。用户管理涉及创建、修改和删除数据库用户，权限管理则控制用户对数据库的访问和操作。MySQL提供了灵活的权限控制机制，允许管理员根据需要为每个用户分配特定的权限，确保数据安全和合规性。正确的用户和权限管理策略有助于防止未经授权的访问和恶意操作，提高数据库的安全性和稳定性。
1 基本命令

创建用户

使用create user命令创建用户

1. create user 'username'@'host' identified by 'passwd';

复制代码

删除用户

1. drop user 'username'@'host';

复制代码

修改用户密码

1. alter user 'username'@'host' identified by 'new_passwd';

复制代码

查看用户权限

1. mysql> show grants for 'username'@'host';
2. +-----------------------------------------+
3. | Grants for username@host                |
4. +-----------------------------------------+
5. | GRANT USAGE ON *.* TO 'username'@'host' |
6. +-----------------------------------------+
8. mysql> show grants for 'root'@'localhost';
9. +---------------------------------------------------------------------+
10. | Grants for root@localhost                                           |
11. +---------------------------------------------------------------------+
12. | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
13. | GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
14. +---------------------------------------------------------------------+
16. mysql> show grants for 'mysql.session'@'localhost';
17. +-----------------------------------------------------------------------+
18. | Grants for mysql.session@localhost                                    |
19. +-----------------------------------------------------------------------+
20. | GRANT SUPER ON *.* TO 'mysql.session'@'localhost'                     |
21. | GRANT SELECT ON `performance_schema`.* TO 'mysql.session'@'localhost' |
22. | GRANT SELECT ON `mysql`.`user` TO 'mysql.session'@'localhost'         |
23. +-----------------------------------------------------------------------+

复制代码

新创建的用户只有usage权限
授予权限

使用grant授权，grant的基本语法：

1. 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可以将权限赋予其他用户。
  

撤销权限

1. revoke [privilege] on [db].[obj] from 'user'@'host';

复制代码

2 用户管理

mysql用户由两部分组成，用户名和登陆主机。登陆主机限制了用户登陆数据库的服务器地址信息。登陆主机可以使用%, _通配符
%: 匹配任何字符串。
_: 匹配任意一个字符。
使用create user语句创建用户。
早期版本，执行grant语句时，如果被授权的用户不存在，会自动创建用户。不过不推荐这种做法。
下面是一个例子:

1. mysql> show variables like '%sql_mode%';
2. +---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
3. | Variable_name | Value                                                                                                                                     |
4. +---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
5. | 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 |
6. +---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
7. 1 row in set (0.00 sec)
9. ### 5.7 SQL_MODE默认有 NO_AUTO_CREATE_USER, 表示执行grant语句时不自动创建用户。
11. mysql> grant select on *.* to 'userx'@'%';
12. ERROR 1133 (42000): Cant find any matching row in the user table
15. ### 修改sql_mode
16. mysql> set sql_mode='';
17. Query OK, 0 rows affected, 1 warning (0.00 sec)
19. mysql> show warnings;
20. +---------+------+------------------------------------------------------------------------------------------------+
21. | Level   | Code | Message                                                                                        |
22. +---------+------+------------------------------------------------------------------------------------------------+
23. | Warning | 3090 | Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release. |
24. +---------+------+------------------------------------------------------------------------------------------------+
25. 1 row in set (0.00 sec)
27. ### 再次执行grant语句，授权成功，但是提示有warning
28. mysql> grant select on *.* to 'userx'@'%';
29. Query OK, 0 rows affected, 1 warning (0.00 sec)
31. mysql> show warnings;
32. +---------+------+------------------------------------------------------------------------------------------------------------------------------------+
33. | Level   | Code | Message                                                                                                                            |
34. +---------+------+------------------------------------------------------------------------------------------------------------------------------------+
35. | Warning | 1287 | Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement. |
36. +---------+------+------------------------------------------------------------------------------------------------------------------------------------+
37. 1 row in set (0.00 sec)
40. ### 默认创建的用户无密码，有安全风险。
41. select user,host, authentication_string from mysql.user where user = 'userx';
42. +-------+------+-----------------------+
43. | user  | host | authentication_string |
44. +-------+------+-----------------------+
45. | userx | %    |                       |
46. +-------+------+-----------------------+

复制代码

用户登陆验证的过程

mysql的所有用户，都存储在mysql.user表。登陆时，会基于mysql.user表的信息验证用户身份

• 获取用户登陆时的客户端IP信息。
  
• 如果mysql没有设置skip-name-resove参数，会将客户端IP信息转换成主机名。若转换失败，服务端错误日志中会记录warning日志
  
• 获取客户端指定的用户名
  
• 基于客户端IP或主机信息和客户端指定的用户名，到mysql.user表查找对应的记录。
  
• 如果能找到对应的记录，验证客户端密码。
  
• 如果找不到对应的记录，则登陆失败。
  匿名用户
  mysql支持匿名用户（用户名为空的用户）。在5.6之前的版本，使用mysql_install_db初始化数据库时，会默认创建匿名用户，允许从本地服务器登陆免密登陆数据库。
  如果系统中有匿名用户，用户登陆验证时会先验证匿名用户。
  

1. ### 创建用户auser
2. mysql> create user 'auser'@'%' identified by 'auser';
3. Query OK, 0 rows affected (0.14 sec)
5. mysql> create user ''@'localhost';
6. Query OK, 0 rows affected (0.00 sec)
9. ### 本地使用auser登陆数据库
10. [root@box1 ~]# mysql -uauser -h127.0.0.1 -pauser
11. mysql: [Warning] Using a password on the command line interface can be insecure.
12. ERROR 1045 (28000): Access denied for user 'auser'@'localhost' (using password: YES)
13. ## 登陆报错，1045是密码错误
15. ### 使用auser, 不输入密码，登陆数据库
16. [root@box1 ~]# mysql -uauser -h127.0.0.1
17. Welcome to the MySQL monitor.  Commands end with ; or \g.
18. mysql> show grants;
19. +--------------------------------------+
20. | Grants for @localhost                |
21. +--------------------------------------+
22. | GRANT USAGE ON *.* TO ''@'localhost' |
23. +--------------------------------------+
24. # 可以登陆，但是登陆的用户是 ''@localhost'，而不是auser
27. ### 使用IP auser登陆数据库
28. [root@box1 ~]# mysql -uauser -h172.16.20.51 -pauser
29. Welcome to the MySQL monitor.  Commands end with ; or \g.
30. mysql> show grants;
31. +-----------------------------------+
32. | Grants for auser@%                |
33. +-----------------------------------+
34. | GRANT USAGE ON *.* TO 'auser'@'%' |
35. +-----------------------------------+
36. 1 row in set (0.00 sec)

复制代码

引起上面问题的原因是系统中存在一个匿名用户。
把匿名用户删除后，使用auser可以正常登陆127.0.0.1

1. mysql> show grants for 'auser'@'%';
2. +-----------------------------------+
3. | Grants for auser@%                |
4. +-----------------------------------+
5. | GRANT USAGE ON *.* TO 'auser'@'%' |
6. +-----------------------------------+
7. 1 row in set (0.00 sec)
9. mysql> show grants for ''@'localhost';
10. +--------------------------------------+
11. | Grants for @localhost                |
12. +--------------------------------------+
13. | GRANT USAGE ON *.* TO ''@'localhost' |
14. +--------------------------------------+
15. 1 row in set (0.00 sec)
17. mysql> drop user ''@'localhost';
18. Query OK, 0 rows affected (0.00 sec)
22. [root@box3 ~]# mysql -uauser -h172.16.20.51 -pauser
23. Welcome to the MySQL monitor.  Commands end with ; or \g.
25. mysql> show grants;
26. +-----------------------------------+
27. | Grants for auser@%                |
28. +-----------------------------------+
29. | GRANT USAGE ON *.* TO 'auser'@'%' |
30. +-----------------------------------+
31. 1 row in set (0.01 sec)

复制代码

主机名称解析

默认配置下mysql服务端会将client ip转换成主机名，若转换失败，会在日志中记录warning信息。服务端会根据解析后的主机名来进行权限验证。

1. 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选项。加上这个选项后，以主机名创建的用户信息不会被加载。

1. 2021-04-06T10:07:49.322405Z 0 [Warning] 'user' entry 'user@box1' ignored in --skip-name-resolve mode.

复制代码

来源:https://www.cnblogs.com/yunchekeji/p/17946861
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！