mysql连接权限测试
来源:互联网 发布:镇江学美工设计 编辑:程序博客网 时间:2024/05/02 00:30
本实验完成以下测试需求:
需求一:验证'%'是否包含'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
不加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
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: 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
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: 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协议;
- mysql连接权限测试
- MySQL设置连接权限
- mysql drop权限测试
- mysql连接测试程序
- php 测试连接mysql
- 测试连接MySQL
- 测试PHP连接MYSQL
- mysql 连接测试
- JAVA连接MYSQL测试
- JDBC连接MySQL测试
- mysql 开启远程连接权限
- Mysql远程连接赋予权限
- mysql 开启远程连接权限
- MYSQL远程连接,权限设定
- MySql开启远程连接权限
- Mysql 付外部连接权限
- 设置mysql远程连接权限
- 开启MySql远程连接权限
- 导航栏 $_ajax异步局部刷新加载内容
- DB2的TRUNCATE功能
- kafka集群安装详细说明
- C控制语句:循环
- 大型程序的结构(三)【程序包】重点!!!
- mysql连接权限测试
- 【MVVM】Android Data Binding实战(一)
- 自定义雅虎新闻闪屏加载动画
- ScheduledExecutorService 不能进行任务调度
- c#调用笔记本摄像头并使用tcp传输图像
- Android NDK学习笔记3-入门案例篇
- Android中常见的热门标签的流式布局的实现
- nodejs 本地应用部署
- css3 圆角|盒阴影|线性渐变 ie兼容 | pie解决