空用户的危害

MySQL中是允许用户名为 '' 的用户存在，本章节介绍数据库中存在这种空用户时的危害。

MySQL中使用空用户时，它将可以匹配任何用户名。这一特性也会带来多种安全性、功能性危害。所以，在实际使用过程中应避免使用空用户。

• 安全性危害
  • 当存在空用户时，连接时可以使用任意用户名进行登录。
  • 如果空用户有密码，则使用任意用户名和空用户的密码即可登录数据库，并获得空用户所拥有的所有权限。示例：

    #没有空用户时，使用非法用户名‘abcd’，连接失败 
    mysql> select user,host from mysql.user; 
    +------------------+-----------+
    | user             | host      | 
    +------------------+-----------+
    | root             | %         | 
    | mysql.infoschema | localhost | 
    | mysql.session    | localhost | 
    | mysql.sys        | localhost | 
    +------------------+-----------+
    mysql -uabcd -h127.0.0.1 -P3306 -pTest_1234 
    mysql: [Warning] Using a password on the command line interface can be insecure. 
    ERROR 1045 (28000): Access denied for user 'abcd'@'localhost' (using password: YES) 
    
    # 创建空用户后，使用非法用户名‘abcd’，密码用空用户的密码，连接成功  
    mysql> create user ''@'localhost' IDENTIFIED BY 'Test_1234'; 
    mysql> select user,host from mysql.user; 
    +------------------+-----------+
    | user             | host      | 
    +------------------+-----------+
    | root             | %         | 
    |                  | localhost | 
    | mysql.infoschema | localhost | 
    | mysql.session    | localhost | 
    | mysql.sys        | localhost | 
    +------------------+-----------+ 
    mysql -uabcd -h127.0.0.1 -P3306 -pTest_1234 
    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 37Server version: 8.0.22-debug Source distribution 
    Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. 
    Oracle is a registered trademark of Oracle Corporation and/or its affiliates. 
    Other names may be trademarks of their respective owners. 
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    mysql> 

  • 如果空用户没有密码，则使用任意用户名即可免密登录数据库，并获得空用户所拥有的所有权限。示例：

    #存在无密码的空用户时，可以使用任意用户免密登录数据库。  
    mysql> create user ''@'localhost'; 
    Query OK, 0 rows affected (8.87 sec) 
    mysql> select user,host from mysql.user; 
    +------------------+-----------+
    | user             | host      | 
    +------------------+-----------+
    | root             | %         | 
    |                  | localhost | 
    | mysql.infoschema | localhost | 
    | mysql.session    | localhost | 
    | mysql.sys        | localhost | 
    +------------------+-----------+
    mysql -uabcd -h127.0.0.1 -P3306 
    Welcome to the MySQL monitor.  Commands end with ; or \g. 
    Your MySQL connection id is 39Server version: 8.0.22-debug Source distribution 
    Copyright (c) 2000, 2020, Oracle and/or its affiliates. 
    All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. 
    Other names may be trademarks of their respective owners. 
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 
    mysql>  
    #-----------------
    mysql -usdhsjkdshk -h127.0.0.1 -P3306 
    Welcome to the MySQL monitor.  Commands end with ; or \g. 
    Your MySQL connection id is 40Server version: 8.0.22-debug Source distribution 
    Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. 
    Oracle is a registered trademark of Oracle Corporation and/or its affiliates. 
    Other names may be trademarks of their respective owners. 
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 
    mysql> 

• 功能性危害

  当存在空用户时，可能因为匹配出错，导致正常的用户名无法登录。

  示例：存在空用户与root用户的host有重叠时，导致root用户无法使用密码登录，或者使用空用户的密码登录后无法进入root的权限。

  mysql> create user ''@'localhost'; 
  Query OK, 0 rows affected (8.87 sec)  
  mysql> select user,host from mysql.user; 
  +------------------+-----------+
  | user             | host      | 
  +------------------+-----------+
  | root             | %         | 
  |                  | localhost | 
  | mysql.infoschema | localhost | 
  | mysql.session    | localhost | 
  | mysql.sys        | localhost | 
  +------------------+-----------+
  # 用root的密码无法登录 
  mysql -uroot -h127.0.0.1 -P3306 -pTest_root 
  mysql: [Warning] Using a password on the command line interface can be insecure. 
  ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)  
  # 用空用户的密码(免密)登录后实际是空用户登录，没有root权限。 
  mysql -uroot -h127.0.0.1 -P3306  
  Welcome to the MySQL monitor.  Commands end with ; or \g. 
  Your MySQL connection id is 45Server version: 8.0.22-debug Source distribution 
  Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. 
  Oracle is a registered trademark of Oracle Corporation and/or its affiliates. 
  Other names may be trademarks of their respective owners. 
  Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 
  mysql> select user,host from mysql.user; 
  ERROR 1142 (42000): SELECT command denied to user ''@'localhost' for table 'user'