Bug #87363 Document limitations regarding anonymous accounts that use wildcards

• Submitted: 9 Aug 2017 17:24
• Modified: 10 Aug 2017 18:14
• Reporter: Geoff Montee
• Status: Verified
• Category: MySQL Server: Security: Privileges
• Severity: S3 (Non-critical)
• Version: 5.6, 5.5.56, 5.6.37
• OS: Any
• CPU Architecture: Any
• Tags: account, documentation, grant, privileges

[9 Aug 2017 17:24] Geoff Montee

Description:
It seems that MySQL rejects some anonymous accounts that use wildcards in the host name portion of the account name, but this limitation does not seem to be documented on any of the following pages:

https://dev.mysql.com/doc/refman/5.6/en/account-names.html

https://dev.mysql.com/doc/refman/5.6/en/user-names.html

https://dev.mysql.com/doc/refman/5.6/en/create-user.html

https://dev.mysql.com/doc/refman/5.6/en/grant.html#grant-accounts-passwords

How to repeat:
mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.6.35    |
+-----------+
1 row in set (0.00 sec)

mysql> SELECT User, Host FROM mysql.user WHERE User='';
Empty set (0.00 sec)

This fails:

mysql> CREATE USER ''@'%';
ERROR 1396 (HY000): Operation CREATE USER failed for ''@'%'

But this succeeds:

mysql> CREATE USER ''@'192.168.1.%';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER ''@'%.mysql.com';
Query OK, 0 rows affected (0.00 sec)

Suggested fix:
If this is a documentation issue, then fix the documentation. If this is a bug, then fix the bug.

[10 Aug 2017 5:24] MySQL Verification Team

Hello Geoff,

Thank you for the report.
My tests shows that 5.7.19 is not affected where as 5.1.77 to 5.6.37 are affected.

Thanks,
Umesh

[10 Aug 2017 18:14] Geoff Montee

The problem seems to be that the mysql.db table has default entries for the ''@'%' user, but there is no corresponding default entry in the mysql.user table. I can create this user if I delete those entries from mysql.db.

mysql> SELECT * FROM mysql.db WHERE User='' AND Host='%'\G
*************************** 1. row ***************************
                 Host: %
                   Db: test
                 User:
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: Y
          Create_priv: Y
            Drop_priv: Y
           Grant_priv: N
      References_priv: Y
           Index_priv: Y
           Alter_priv: Y
Create_tmp_table_priv: Y
     Lock_tables_priv: Y
     Create_view_priv: Y
       Show_view_priv: Y
  Create_routine_priv: Y
   Alter_routine_priv: N
         Execute_priv: N
           Event_priv: Y
         Trigger_priv: Y
*************************** 2. row ***************************
                 Host: %
                   Db: test\_%
                 User:
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: Y
          Create_priv: Y
            Drop_priv: Y
           Grant_priv: N
      References_priv: Y
           Index_priv: Y
           Alter_priv: Y
Create_tmp_table_priv: Y
     Lock_tables_priv: Y
     Create_view_priv: Y
       Show_view_priv: Y
  Create_routine_priv: Y
   Alter_routine_priv: N
         Execute_priv: N
           Event_priv: Y
         Trigger_priv: Y
2 rows in set (0.00 sec)

mysql> DELETE FROM mysql.db WHERE User='' AND Host='%';
Query OK, 2 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER ''@'%';
Query OK, 0 rows affected (0.00 sec)