MySQL运维实战（2）MySQL用户和权限管理
MySQL用户管理
基本命令
创建用户
使用create user命令创建用户
create user 'username'@'host' identified by '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可以将权限赋予其他用户。
用户管理
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.
