MySQL连接权限测试

190次阅读

共计 6214 个字符，预计需要花费 16 分钟才能阅读完成。

本实验完成以下测试需求：
需求一：验证 ’%’ 是否包含 ’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)

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 Server

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.1

Connection 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 sec

Threads: 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 Server

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.1

Connection 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 sec

Threads: 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 Server

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.1

Connection 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 sec

Threads: 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 协议;

本文永久更新链接地址：http://www.linuxidc.com/Linux/2016-12/138200.htm

正文完

星哥玩云-微信公众号