MySQL 5.6不删空用户的影响
MySQL 5.6不删空用户的影响
问题
MySQL5.6 新建本地可以登录的用户，但在本地始终登录不上，不存在的用户却能登录成功
mysql -uroot -p1234 -S /data/mysql/33562/mysql.sock -e "select version()"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------+
| version() |
+-----------+
| 5.6.39 |
+-----------+
创建了admin@'%' 用户，但在本地无法登陆，但可以远程登陆
sselect user,host,password from mysql.user;
+--------+-----------+-------------------------------------------+
| user | host | password |
+--------+-----------+-------------------------------------------+
| root | localhost | *A4B6157319038724E3560894F7F932C8886EBFCF |
| root | slave58 | |
| root | 127.0.0.1 | |
| root | ::1 | |
| | localhost | |
| | slave58 | |
| admin | % | *4ACFE3202A5FF5CF467898FC58AAB1D615029441 |
| zabbix | 127.0.0.1 | *DEEF4D7D88CD046ECA02A80393B7780A63E7E789 |
| super | 127.0.0.1 | *F85A86E6F55A370C1A115F696A9AD71A7869DB81 |
| super | slave58 | *F85A86E6F55A370C1A115F696A9AD71A7869DB81 |
+--------+-----------+-------------------------------------------+
show grants for 'admin'@'%';
+---------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' IDENTIFIED BY PASSWORD '*4ACFE3202A5FF5CF467898FC58AAB1D615029441' |
+---------------------------------------------------------------------------------------------------------------+
select password('admin');
+-------------------------------------------+
| password('admin') |
+-------------------------------------------+
| *4ACFE3202A5FF5CF467898FC58AAB1D615029441 |
+-------------------------------------------+
以ip地址无法登陆
mysql -uadmin -padmin -P33562 -h10.186.30.58
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'admin'@'slave58' (using password: YES)
以socket也无法登陆
mysql -uadmin -padmin -P33562 -S /data/mysql/33562/mysql.sock
或者
mysql -uadmin -padmin -P33562 -h127.0.0.1
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'admin'@'localhost' (using password: YES)
不存在的用户却可以登录成功
mysql -utest -S /data/mysql/33562/mysql.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.6.39 MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
分析
1、本是以ip地址进行登录的，错误信息报的是Access denied for user 'admin'@'slave58'，自动进行了DNS解析，解析成了主机名，skip-name-resolve 可以禁用DNS解析
2、mysql在验证权限的时候，先验证host，在验证user，最后验证password，即
host->user->password
3、以host或者socket登陆，进行了DNS解析，host；socket被解析成了slave58;localhost， 而mysql.user 中host 为localhost和slave58 对应空用户，就报了访问拒绝错误
测试
启动mysqld时没有加上--skip-name-resolve
以mysql不存在的用户进行登录
mysql -utest -P33562 -h`hostname`
或者
mysql -utest -S /data/mysql/33562/mysql.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.39 MySQL Community Server (GPL)
.....
show grants;
+------------------------------------+
| Grants for @slave58 |
+------------------------------------+
| GRANT USAGE ON *.* TO ''@'slave58' |
+------------------------------------+
show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| test |
+--------------------+
create table test.test_t1(id int);
Query OK, 0 rows affected (0.05 sec)
drop table test.test_t1;
Query OK, 0 rows affected (0.05 sec)
drop database test;
Query OK, 8 rows affected (0.49 sec)
具有USAGE 的权限的用户 有test 库的所有权限和information_schema 中表的select权限。
不存在的用户还可以 drop datase test 这个“神操作”是因为 空用户具有test和test_*库的所有权限，而任何用户都能匹配空用户，mysql5.6初始化后默认就有这个，而mysql5.7默认没有，也没有空用户，空密码用户
select * from mysql.db;
以mysql.user中存在的用户登录如 “问题” 中所示，无法登录
在mysql.user 中存在的用户还不能登录，任意一个不存在的用户还可以登录成功 ，还可以drop database test，这有很大的安全隐患！开始怀疑，这mysql5.6是不是假的o_o
启动mysqld时加上--skip-name-resolve
ps -ef | grep -w 33562 | grep -v grep | awk '{print $2}' | xargs -l kill
su - mysql -c "/usr/local/mysql/5.6.39/bin/mysqld --no-defaults --basedir=/usr/local/mysql/5.6.39 --datadir=/data/mysql/33562 --pid-file=/data/mysql/33562/clone.pid --port=33562 --server-id=33562 --socket=/data/mysql/33562/mysql.sock --tmpdir=/data/mysql/33562 --skip-name-resolve &"
以mysql.user中不存在的用户进行登录
mysql -utest -P33562 -h`hostname`
ERROR 1045 (28000): Access denied for user 'test'@'10.186.30.58' (using password: NO)
mysql -utest -S /data/mysql/33562/mysql.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.6.39 MySQL Community Server (GPL)
以`hostname`登录，host 转化成ip地址，在mysql.user中没有匹配到；
以socket登录 host 被转化成localhost,在mysql.user中匹配到了''@'localost'空用户，登录成功。可见，任意用户都能匹配mysql.user的user为空' '的用户
以mysql.user中存在的用户可以登录成功
mysql -uadmin -padmin -P33562 -h10.186.30.58
mysql: [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 1
Server version: 5.6.39 MySQL Community Server (GPL)
结论
1、在没有以 skip-name-resolve 启动启动的情况下，以mysql -uuser -ppassword登录会进行DNS解析，在mysql.user中匹配到错误的 host，即时没有限制主机的用户 ‘user’@'%'也无法登录
2、即时一个并不存在的用户在以`hostname` 或者socket进行登录，若匹配了mysql.user中的host，该host对应的user和password为空，并不存在的用户也能登录成功
3、为了避免进行DNS解析，在my.cnf配置文件中添加skip-name-resolve
4、在刚初始化mysql5.6后，就应该删除mysql.user; mysql.db中空用户，空密码账户(包括root空秘密账户)，减少安全隐患
DELETE FROM mysql.user WHERE PASSWORD=' ';
DELETE FROM mysql.db WHERE USER=' '; FLUSH PRIVILEGES;
5、可用mysql5.6 自带mysql_secure_installation 脚本删除空用户，空密码账户，删除test库
