加强MySQL用户安全

电玩女神 2022-08-09 00:56 173阅读 0赞

很多亲们在安装好了MySQL数据库之后，对于mysql用户表并没有做任何特殊的处理，因此缺省情况下，存在密码为空的用户，也有很多用户名和密码都为空的情形，我们称之为双空用户。这种情形下的登录，在此统称为异常登陆。对于生产环境的数据库来说，这会带来一些不确定的安全隐患。下面是关于这个问题的描述以及清理掉无关用户的方法。  
    有关mysql用户相关参考：  
[MySQL 用户与权限管理][MySQL]  
[MySQL 修改用户密码及重置root密码][MySQL _root]  
  
1、演示异常登录

a、演示双空用户登陆
    [root@xlkoracel ~]# mysql -uroot -p
    Enter password: 
    (root@localhost) [(none)]> show variables like 'version';
    +---------------+--------+
    | Variable_name | Value  |
    +---------------+--------+
    | version       | 5.6.26 |
    +---------------+--------+
    
    (root@localhost) [(none)]> select user,host,password from mysql.user;
    +-------+-------------+-------------------------------------------+
    | user  | host        | password                                  |
    +-------+-------------+-------------------------------------------+
    | root  | localhost   | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA |
    | root  | xlkoracel   |                                           |
    | root  | 127.0.0.1   |                                           |
    | root  | ::1         |                                           |
    |       | localhost   |                                           |
    |       | xlkoracel   |                                           |
    | mycat | localhost   | *975B2CD4FF9AE554FE8AD33168FBFC326D2021DD |
    | mycat | 192.168.1.% | *975B2CD4FF9AE554FE8AD33168FBFC326D2021DD |
    | mycat | 192.168.%.% | *975B2CD4FF9AE554FE8AD33168FBFC326D2021DD |
    | root  | 192.168.%.% | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA |
    +-------+-------------+-------------------------------------------+
    
    (root@localhost) [(none)]> -- 可以看到存在用户名和密码同时为空的情形
    (root@localhost) [(none)]> -- 退出后尝试使用任意用户名登录
    (root@localhost) [(none)]> exit
    Bye
    [root@xlkoracel ~]# mysql -uxx ###无需指定密码参数-p
    (xx@localhost) [(none)]> -- 可以成功登陆
    (xx@localhost) [(none)]> -- 下面查看一下自身的权限
    (xx@localhost) [(none)]> show grants;  --当前只有usage权限
    +--------------------------------------+
    | Grants for @localhost                |
    +--------------------------------------+
    | GRANT USAGE ON *.* TO ''@'localhost' |
    +--------------------------------------+
    
    (xx@localhost) [(none)]> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | test               |
    +--------------------+
    
    (xx@localhost) [(none)]> use test;
    Database changed
    (xx@localhost) [test]> show tables;
    Empty set (0.00 sec)
    
    (xx@localhost) [test]> create table t(id int);
    Query OK, 0 rows affected (0.14 sec)
    
    (xx@localhost) [test]> insert into t values(1);
    Query OK, 1 row affected (0.01 sec)
    
    (xx@localhost) [test]> select * from t;
    +------+
    | id   |
    +------+
    |    1 |
    +------+
    1 row in set (0.00 sec)
    
    (xx@localhost) [test]> --从上可以看出，usage权限已经可以完成很多任务
    (xx@localhost) [test]> use infromation_schema;
    ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'infromation_schema'
    (xx@localhost) [test]> exit;
    
    
    b、演示密码为空的用户登陆
    [root@xlkoracel ~]# mysql -uroot -hxlkoracel  ###注，此时也无需指定参数-p  
    (root@xlkoracel) [(none)]> --可以成功登陆
    (root@xlkoracel) [(none)]> show grants;   --查看自身权限，为ALL PRIVILEGES，权限更大
    +---------------------------------------------------------------------+
    | Grants for root@xlkoracel                                           |
    +---------------------------------------------------------------------+
    | GRANT ALL PRIVILEGES ON *.* TO 'root'@'xlkoracel' WITH GRANT OPTION |
    | GRANT PROXY ON ''@'' TO 'root'@'xlkoracel' WITH GRANT OPTION        |
    +---------------------------------------------------------------------+

2、清理异常用户

[root@xlkoracel ~]# mysql -uroot -p
    Enter password: 
    (root@localhost) [(none)]> select user,host,password from mysql.user
        -> where (user is null or user='') and (password is null or password='');
    +------+-----------+----------+
    | user | host      | password |
    +------+-----------+----------+
    |      | localhost |          |
    |      | xlkoracel |          |
    +------+-----------+----------+
    2 rows in set (0.01 sec)
    
    (root@xlkoracel) [(none)]> -- Author : Leshami
    (root@xlkoracel) [(none)]> -- Blog   : http://blog.csdn.net/leshami
    (root@localhost) [(none)]> -- 使用drop 方式清理用户
    (root@localhost) [(none)]> drop user ''@'localhost';
    Query OK, 0 rows affected (0.24 sec)
    
    (root@localhost) [(none)]> select user,host,password from mysql.user
        -> where (user is null or user='') and (password is null or password='');
    +------+-----------+----------+
    | user | host      | password |
    +------+-----------+----------+
    |      | xlkoracel |          |
    +------+-----------+----------+
    1 row in set (0.00 sec)
    
    (root@localhost) [(none)]> -- 直接用delete从mysql.user表清理用户
    (root@localhost) [(none)]> delete from mysql.user
        -> where (user is null or user='') and (password is null or password='');
    Query OK, 1 row affected (0.06 sec)
    
    (root@localhost) [(none)]> -- 直接用delete从mysql.user表清理所有密码为空的用户
    (root@xlkoracel) [(none)]> delete from mysql.user where password is null or password='';
    Query OK, 3 rows affected (0.00 sec)

3、小结  
a、对于部署到生产环境的mysql服务器建议清理所有密码为空的用户以及双空用户  
b、建议清理前先备份，使用drop user方式来清理用户更稳妥

[MySQL]: http://blog.csdn.net/leshami/article/details/39805285
[MySQL _root]: http://blog.csdn.net/leshami/article/details/39805839