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 Server

Copyright (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 Server

Copyright (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.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

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/98a07c0ba4f7d15d0cda0b18c9b6528d.html