2)如果你同时创建了'liuwenhe'@'192.168.0.%'和'liuwenhe'@'%'这两个用户,那么当你从192.168.0网段去登陆数据库的时候,'liuwenhe'@'%'用户是不能登陆数据库的,只能通过'liuwenhe'@'192.168.0.%'登陆,但是当你删除'liuwenhe'@'192.168.0.%'用户的时候,'liuwenhe'@'%'用户就可以登陆了,可以理解为mysql优先并且只会验证匹配度高的用户,
具体验证过程如下:
mysql> select host,user from mysql.user;
+--------------+----------+
| host | user |
+--------------+----------+
| % | liuwenhe |
| % | ogg |
| % | root |
| 127.0.0.1 | root |
| 192.168.0.% | liuwenhe |
| 192.168.0.% | ncms |
| 192.168.0.13 | rep |
| localhost | ncms |
| localhost | ogg |
| localhost | root |
| server01 | root |
+--------------+----------+
11 rows in set (0.00 sec)
在另一台机器S244(192.168.0.244)尝试登陆mysql:
使用'liuwenhe'@'%'用户登录失败:如下
[root@S244 ~]# mysql -uliuwenhe -pliuwenheall -h192.168.0.12
Warning: Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'liuwenhe'@'192.168.0.244' (using password: YES)
使用'liuwenhe'@'192.168.0.%'用户登录成功,如下:
[root@S244 ~]# mysql -uliuwenhe -pliuwenhe0 -h192.168.0.12
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3679
Server version: 5.6.26-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
当你删除'liuwenhe'@'192.168.0.%'用户的时候,'liuwenhe'@'%'用户就可以登陆了,如下:
mysql> delete from mysql.user where user='liuwenhe' and host='192.168.0.%';
Query OK, 1 row affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
依旧在另一台机器S244(192.168.0.244)尝试使用'liuwenhe'@'%'用户登陆mysql,成功了:
[root@S244 ~]# mysql -uliuwenhe -pliuwenheall -h192.168.0.12
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3681
Server version: 5.6.26-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
3)我的这台mysql所在的服务器上面有两个ip,如下:
现在我创建了一个 'liuwenhe'@'192.168.8.%' ,
那么只能通过
mysql -uliuwenhe -pliuwenhe8 -h192.168.8.238登陆,不能通过mysql -uliuwenhe -pliuwenhe8 -h192.168.0.12登陆,同理创建了一个 'liuwenhe'@'192.168.0.%' ,只能通过
mysql -uliuwenhe -pliuwenhe0 -h192.168.0.12登陆,不能通过mysql -uliuwenhe -pliuwenhe0 -h192.168.8.238登陆
验证如下:
mysql> CREATE USER 'liuwenhe'@'192.168.0.%' IDENTIFIED BY 'liuwenhe0';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
[root@server02 ~]# mysql -uliuwenhe -pliuwenhe0 -h192.168.0.12
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3704
Server version: 5.6.26-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> exit
Bye
[root@server02 ~]# mysql -uliuwenhe -pliuwenhe0 -h192.168.8.238
Warning: Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'liuwenhe'@'192.168.8.238' (using password: YES)