MySQL连接权限测试

本实验完成以下测试需求：
需求一：验证"%"是否包含"localhost"
需求二：验证localhost和127.0.0.1分别使用的连接协议
版本：mariadb 10.1.12一.验证"%"是否包含"localhost"
先建立帐号和授权:
MariaDB [（none）]> grant all privileges on *.* to "lmsapps"@"%" identified by "Lms166apps";
Query OK, 0 rows affected （0.00 sec）MariaDB [（none）]> flush privileges;
Query OK, 0 rows affected （0.00 sec）MariaDB [（none）]> exit
Bye
[apps@mvxl2972 ~]$ MySQL -ulmsapps -pLms166apps --socket=/tmp/mysql3306.sock
ERROR 1045 （28000）: Access denied for user "lmsapps"@"localhost" （using password: YES）
当只有"lmsapps"@"%"有权限时，无法登入。
MariaDB [（none）]> select user,host from mysql.user;
+---------+-----------+
| user | host |
+---------+-----------+
| lmsapps | % |
| root | 127.0.0.1 |
| root | ::1 |
| | localhost |
| root | localhost |
| | mvxl2972 |
| root | mvxl2972 |
+---------+-----------+
7 rows in set （0.00 sec）再增加lmsapps@"localhost"用户：
MariaDB [（none）]> grant all privileges on *.* to lmsapps@"localhost" identified by "Lms166apps";
Query OK, 0 rows affected （0.00 sec）
MariaDB [（none）]> select user,host from mysql.user;
+---------+-----------+
| user | host |
+---------+-----------+
| lmsapps | % |
| root | 127.0.0.1 |
| root | ::1 |
| | localhost |
| lmsapps | localhost |
| root | localhost |
| | mvxl2972 |
| root | mvxl2972 |
+---------+-----------+
[apps@mvxl2972 ~]$ mysql -ulmsapps -pLms166apps --socket=/tmp/mysql3306.sock
Welcome to the MariaDB monitor. Commands end with ; or g.
Your MariaDB connection id is 28
Server version: 10.1.12-MariaDB MariaDB ServerCopyright （c） 2000, 2016, Oracle, MariaDB Corporation Ab and others.Type "help;" or "h" for help. Type "c" to clear the current input statement.MariaDB [（none）]>现在能正常登入。
说明"%"是不包含"localhost"二.验证localhost和127.0.0.1分别使用的连接协议1.在skip_name_resolve为开启状态下：
MariaDB [（none）]> show variables like "skip%";
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| skip_external_locking | ON |
| skip_name_resolve | ON |
| skip_networking | OFF |
| skip_parallel_replication | OFF |
| skip_replication | OFF |
| skip_show_database | OFF |先drop用户"lmsapps"@"%"
MariaDB [（none）]> drop user "lmsapps"@"%";
Query OK, 0 rows affected （0.00 sec）MariaDB [（none）]> exit
Bye
[apps@mvxl2972 ~]$ mysql -ulmsapps -pLms166apps -h 127.0.0.1
ERROR 1045 （28000）: Access denied for user "lmsapps"@"127.0.0.1" （using password: YES） mysql -ulmsapps -pLms166apps -h 127.0.0.1通过127.0.0.1连接时使用TCP/IP协议：
[apps@mvxl2972 ~]$ mysql -ulmsapps -pLms166apps -h 127.0.0.1
Welcome to the MariaDB monitor. Commands end with ; or g.
Your MariaDB connection id is 34
Server version: 10.1.12-MariaDB MariaDB ServerCopyright （c） 2000, 2016, Oracle, MariaDB Corporation Ab and others.Type "help;" or "h" for help. Type "c" to clear the current input statement.MariaDB [（none）]> s
--------------
mysql Ver 15.1 Distrib 10.1.12-MariaDB, for Linux （x86_64） using readline 5.1Connection id: 34
Current database:
Current user: lmsapps@127.0.0.1
SSL: Not in use
Current pager: stdout
Using outfile: ""
Using delimiter: ;
Server: MariaDB
Server version: 10.1.12-MariaDB MariaDB Server
Protocol version: 10
Connection: 127.0.0.1 via TCP/IP
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
TCP port: 3306
Uptime: 1 day 22 hours 14 min 17 secThreads: 2 Questions: 68 Slow queries: 0 Opens: 0 Flush tables: 1 Open tables: 11 Queries per second avg: 0.000不加127.0.0.1时，默认连接使用socket协议:
[apps@mvxl2972 ~]$ mysql -ulmsapps -pLms166apps
Welcome to the MariaDB monitor. Commands end with ; or g.
Your MariaDB connection id is 35
Server version: 10.1.12-MariaDB MariaDB ServerCopyright （c） 2000, 2016, Oracle, MariaDB Corporation Ab and others.Type "help;" or "h" for help. Type "c" to clear the current input statement.MariaDB [（none）]> s
--------------
mysql Ver 15.1 Distrib 10.1.12-MariaDB, for Linux （x86_64） using readline 5.1Connection id: 35
Current database:
Current user: lmsapps@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ""
Using delimiter: ;
Server: MariaDB
Server version: 10.1.12-MariaDB MariaDB Server
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /tmp/mysql3306.sock
Uptime: 1 day 22 hours 14 min 54 secThreads: 2 Questions: 72 Slow queries: 0 Opens: 0 Flush tables: 1 Open tables: 11 Queries per second avg: 0.000加localhost连接使用socket协议:
[apps@mvxl2972 ~]$ mysql -ulmsapps -pLms166apps -h localhost
Welcome to the MariaDB monitor. Commands end with ; or g.
Your MariaDB connection id is 59
Server version: 10.1.12-MariaDB MariaDB ServerCopyright （c） 2000, 2016, Oracle, MariaDB Corporation Ab and others.Type "help;" or "h" for help. Type "c" to clear the current input statement.MariaDB [（none）]> s
--------------
mysql Ver 15.1 Distrib 10.1.12-MariaDB, for Linux （x86_64） using readline 5.1Connection id: 59
Current database:
Current user: lmsapps@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ""
Using delimiter: ;
Server: MariaDB
Server version: 10.1.12-MariaDB MariaDB Server
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /tmp/mysql3306.sock
Uptime: 1 day 22 hours 26 min 16 secThreads: 4 Questions: 371 Slow queries: 0 Opens: 0 Flush tables: 1 Open tables: 11 Queries per second avg: 0.002
2.在skip_name_resolve为停用状态下：
更改配置文件中skip_name_resolve为off，并重启mysql.
MariaDB [（none）]> show variables like "skip%";
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| skip_external_locking | ON |
| skip_name_resolve | OFF |
| skip_networking | OFF |
| skip_parallel_replication | OFF |
| skip_replication | OFF |
| skip_show_database | OFF |停掉skip_name_resolve后，用127.0.0.1登入转变成了localhost，用lmsapps@"127.0.0.1"的密码无法登入:
-bash-4.1$ mysql -ulmsapps -p -h 127.0.0.1 -P 3306
Enter password:
ERROR 1045 （28000）: Access denied for user "lmsapps"@"localhost" （using password: YES）-bash-4.1$ mysql -ulmsapps -p -h 127.0.0.1
Enter password:
ERROR 1045 （28000）: Access denied for user "lmsapps"@"localhost" （using password: YES）而用lmsapps@"localhost"登入才成功，并且使用TCP/IPt协议：
-bash-4.1$ mysql -ulmsapps -p -h localhost
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
MariaDB [（none）]>实验总结：
1.授权中的"%"不包含"localhost";
2.skip_name_resolve开启情况下，127.0.0.1连接时用"lmsapps"@"%"帐号使用TCP/IP协义，而默认或用localhost连接时使用socket协议;
3.skip_name_resolve关闭情况下，127.0.0.1连接会转换成用"lmsapps"@"localhost"帐号使用TCP/IP协义，而默认或用localhost连接时使用socket协议;本文永久更新链接地址