[MDEV-13486] Make mysql_install_db create a real ''@'%' anonymous account for the test database - Jira

Geoff Montee (Inactive) created issue - 2017-08-09 17:33

Geoff Montee (Inactive) made changes - 2017-08-09 17:34

Field	Original Value	New Value
Description	It seems that MySQL and MariaDB reject 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://mariadb.com/kb/en/mariadb/create-user/#account-names https://mariadb.com/kb/en/mariadb/grant/ Example: MariaDB [(none)]> SELECT VERSION(); +-----------------+ \| VERSION() \| +-----------------+ \| 10.1.25-MariaDB \| +-----------------+ 1 row in set (0.00 sec) MariaDB [(none)]> SELECT User, Host FROM mysql.user WHERE User=''; Empty set (0.00 sec) MariaDB [(none)]> CREATE USER ''@'%'; ERROR 1396 (HY000): Operation CREATE USER failed for ''@'%' MariaDB [(none)]> CREATE USER ''@'192.168.1.%'; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> CREATE USER ''@'%.mysql.com'; Query OK, 0 rows affected (0.02 sec) If this is a feature, then we should probably document it. If it is a bug, then we should probably fix it. Relevant upstream bug: https://bugs.mysql.com/bug.php?id=87363	It seems that MySQL and MariaDB reject 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://mariadb.com/kb/en/mariadb/create-user/#account-names https://mariadb.com/kb/en/mariadb/grant/ Example: {noformat} MariaDB [(none)]> SELECT VERSION(); +-----------------+ \| VERSION() \| +-----------------+ \| 10.1.25-MariaDB \| +-----------------+ 1 row in set (0.00 sec) MariaDB [(none)]> SELECT User, Host FROM mysql.user WHERE User=''; Empty set (0.00 sec) MariaDB [(none)]> CREATE USER ''@'%'; ERROR 1396 (HY000): Operation CREATE USER failed for ''@'%' MariaDB [(none)]> CREATE USER ''@'192.168.1.%'; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> CREATE USER ''@'%.mysql.com'; Query OK, 0 rows affected (0.02 sec) {noformat} If this is a feature, then we should probably document it. If it is a bug, then we should probably fix it. Relevant upstream bug: https://bugs.mysql.com/bug.php?id=87363

Sergei Golubchik made changes - 2017-08-10 09:57

Labels

documentation privileges

privileges

Sergei Golubchik made changes - 2017-08-10 09:57

Component/s

Documentation [ 10903 ]

Sergei Golubchik made changes - 2017-08-10 09:58

Affects Version/s		5.5 [ 15800 ]
Affects Version/s		10.0 [ 16000 ]
Affects Version/s		10.1 [ 16100 ]
Affects Version/s		10.2 [ 14601 ]
Affects Version/s	10.0.31 [ 22501 ]
Affects Version/s	10.1.25 [ 22542 ]
Affects Version/s	10.2.7 [ 22543 ]

Sergei Golubchik made changes - 2017-08-10 09:58

Assignee

Ian Gilfillan [ greenman ]

Sergei Golubchik made changes - 2017-08-10 09:59

Priority

Major [ 3 ]

Minor [ 4 ]

Sergei Golubchik made changes - 2017-08-10 09:59

Fix Version/s

10.2 [ 14601 ]

Sergei Golubchik made changes - 2017-08-10 09:59

Status

Open [ 1 ]

Confirmed [ 10101 ]

Geoff Montee (Inactive) made changes - 2019-02-21 23:27

Summary

Document limitations regarding anonymous accounts that use wildcards

Make mysql_install_db create a real anonymous accounts for the test database

Geoff Montee (Inactive) made changes - 2019-02-21 23:35

Description

It seems that MySQL and MariaDB reject 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://mariadb.com/kb/en/mariadb/create-user/#account-names

https://mariadb.com/kb/en/mariadb/grant/

Example:

{noformat}
MariaDB [(none)]> SELECT VERSION();
+-----------------+
| VERSION() |
+-----------------+
| 10.1.25-MariaDB |
+-----------------+
1 row in set (0.00 sec)

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

MariaDB [(none)]> CREATE USER ''@'%';
ERROR 1396 (HY000): Operation CREATE USER failed for ''@'%'
MariaDB [(none)]> CREATE USER ''@'192.168.1.%';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> CREATE USER ''@'%.mysql.com';
Query OK, 0 rows affected (0.02 sec)
{noformat}

If this is a feature, then we should probably document it. If it is a bug, then we should probably fix it.

Relevant upstream bug:

https://bugs.mysql.com/bug.php?id=87363

mysql_install_db only partially creates a ''@'%' user when setting up the test database. This causes errors that are difficult to interpret when someone tries to actually create a ''@'%' user. For example:

{noformat}
MariaDB [(none)]> SELECT VERSION();
+-----------------+
| VERSION() |
+-----------------+
| 10.1.25-MariaDB |
+-----------------+
1 row in set (0.00 sec)

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

MariaDB [(none)]> CREATE USER ''@'%';
ERROR 1396 (HY000): Operation CREATE USER failed for ''@'%'
{noformat}

The root cause of the problem is that scripts/mysql_test_db.sql inserts some rows into mysql.db for the ''@'%' user, but it does not also insert a row into mysql.user for the user:

https://github.com/MariaDB/server/blob/4abb8216a054e14afbeb81e8529e02bab6fa14ac/scripts/mysql_test_db.sql

We should probably fix scripts/mysql_test_db.sql, so that it creates a row in ''@'%' mysql.user for the user.

For now, this can be worked around by deleting the row in the mysql.db table and then executing FLUSH PRIVILEGES. For example:

{noformat}
DELETE FROM mysql.db WHERE User='' AND Host='%';
FLUSH PRIVILEGES;
{noformat}

And then the account can be created:

{noformat}
MariaDB [(none)]> CREATE USER ''@'%';
Query OK, 0 rows affected (0.01 sec)
{noformat}

This is documented here:

https://mariadb.com/kb/en/library/create-user/#anonymous-accounts

Relevant upstream bug:

https://bugs.mysql.com/bug.php?id=87363

Geoff Montee (Inactive) made changes - 2019-02-21 23:36

Summary

Make mysql_install_db create a real anonymous accounts for the test database

Make mysql_install_db create a real ''@'%' anonymous account for the test database

Geoff Montee (Inactive) made changes - 2019-02-21 23:36

Affects Version/s		10.3 [ 22126 ]
Affects Version/s		10.4 [ 22408 ]

Geoff Montee (Inactive) made changes - 2019-02-21 23:36

Fix Version/s		10.4 [ 22408 ]
Fix Version/s	10.2 [ 14601 ]

Geoff Montee (Inactive) made changes - 2019-02-21 23:36

Assignee

Sergei Golubchik [ serg ]

Geoff Montee (Inactive) made changes - 2019-02-21 23:36

Component/s

Scripts & Clients [ 11002 ]

Geoff Montee (Inactive) made changes - 2019-02-21 23:39

Remote Link

This issue links to "MySQL bug #87363 - Document limitations regarding anonymous accounts that use wildcards (Web Link)" [ 28801 ]

Geoff Montee (Inactive) made changes - 2019-02-21 23:39

Description

mysql_install_db only partially creates a ''@'%' user when setting up the test database. This causes errors that are difficult to interpret when someone tries to actually create a ''@'%' user. For example:

{noformat}
MariaDB [(none)]> SELECT VERSION();
+-----------------+
| VERSION() |
+-----------------+
| 10.1.25-MariaDB |
+-----------------+
1 row in set (0.00 sec)

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

MariaDB [(none)]> CREATE USER ''@'%';
ERROR 1396 (HY000): Operation CREATE USER failed for ''@'%'
{noformat}

The root cause of the problem is that scripts/mysql_test_db.sql inserts some rows into mysql.db for the ''@'%' user, but it does not also insert a row into mysql.user for the user:

https://github.com/MariaDB/server/blob/4abb8216a054e14afbeb81e8529e02bab6fa14ac/scripts/mysql_test_db.sql

We should probably fix scripts/mysql_test_db.sql, so that it creates a row in ''@'%' mysql.user for the user.

For now, this can be worked around by deleting the row in the mysql.db table and then executing FLUSH PRIVILEGES. For example:

{noformat}
DELETE FROM mysql.db WHERE User='' AND Host='%';
FLUSH PRIVILEGES;
{noformat}

And then the account can be created:

{noformat}
MariaDB [(none)]> CREATE USER ''@'%';
Query OK, 0 rows affected (0.01 sec)
{noformat}

This is documented here:

https://mariadb.com/kb/en/library/create-user/#anonymous-accounts

Relevant upstream bug:

https://bugs.mysql.com/bug.php?id=87363

mysql_install_db only partially creates a ''@'%' user when setting up the test database. This causes errors that are difficult to interpret when someone tries to actually create a ''@'%' user. For example:

{noformat}
MariaDB [(none)]> SELECT VERSION();
+-----------------+
| VERSION() |
+-----------------+
| 10.1.25-MariaDB |
+-----------------+
1 row in set (0.00 sec)

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

MariaDB [(none)]> CREATE USER ''@'%';
ERROR 1396 (HY000): Operation CREATE USER failed for ''@'%'
{noformat}

The root cause of the problem is that scripts/mysql_test_db.sql inserts some rows into mysql.db for the ''@'%' user, but it does not also insert a row into mysql.user for the user:

https://github.com/MariaDB/server/blob/4abb8216a054e14afbeb81e8529e02bab6fa14ac/scripts/mysql_test_db.sql

We should probably fix scripts/mysql_test_db.sql, so that it creates a row in ''@'%' mysql.user for the user.

For now, this can be worked around by deleting the row in the mysql.db table and then executing FLUSH PRIVILEGES. For example:

{noformat}
DELETE FROM mysql.db WHERE User='' AND Host='%';
FLUSH PRIVILEGES;
{noformat}

And then the account can be created:

{noformat}
MariaDB [(none)]> CREATE USER ''@'%';
Query OK, 0 rows affected (0.01 sec)
{noformat}

This is documented here:

https://mariadb.com/kb/en/library/create-user/#anonymous-accounts

Geoff Montee (Inactive) made changes - 2019-02-21 23:41

Description

mysql_install_db only partially creates a ''@'%' user when setting up the test database. This causes errors that are difficult to interpret when someone tries to actually create a ''@'%' user. For example:

{noformat}
MariaDB [(none)]> SELECT VERSION();
+-----------------+
| VERSION() |
+-----------------+
| 10.1.25-MariaDB |
+-----------------+
1 row in set (0.00 sec)

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

MariaDB [(none)]> CREATE USER ''@'%';
ERROR 1396 (HY000): Operation CREATE USER failed for ''@'%'
{noformat}

The root cause of the problem is that scripts/mysql_test_db.sql inserts some rows into mysql.db for the ''@'%' user, but it does not also insert a row into mysql.user for the user:

https://github.com/MariaDB/server/blob/4abb8216a054e14afbeb81e8529e02bab6fa14ac/scripts/mysql_test_db.sql

We should probably fix scripts/mysql_test_db.sql, so that it creates a row in ''@'%' mysql.user for the user.

For now, this can be worked around by deleting the row in the mysql.db table and then executing FLUSH PRIVILEGES. For example:

{noformat}
DELETE FROM mysql.db WHERE User='' AND Host='%';
FLUSH PRIVILEGES;
{noformat}

And then the account can be created:

{noformat}
MariaDB [(none)]> CREATE USER ''@'%';
Query OK, 0 rows affected (0.01 sec)
{noformat}

This is documented here:

https://mariadb.com/kb/en/library/create-user/#anonymous-accounts

mysql_install_db only partially creates a ''@'%' user when setting up the test database. This causes errors that are difficult to interpret when someone tries to actually create a ''@'%' user. For example:

{noformat}
MariaDB [(none)]> SELECT VERSION();
+-----------------+
| VERSION() |
+-----------------+
| 10.1.25-MariaDB |
+-----------------+
1 row in set (0.00 sec)

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

MariaDB [(none)]> CREATE USER ''@'%';
ERROR 1396 (HY000): Operation CREATE USER failed for ''@'%'
{noformat}

The root cause of the problem is that scripts/mysql_test_db.sql inserts some rows into mysql.db for the ''@'%' user, but it does not also insert a row into mysql.user for the user:

https://github.com/MariaDB/server/blob/4abb8216a054e14afbeb81e8529e02bab6fa14ac/scripts/mysql_test_db.sql

We should probably fix scripts/mysql_test_db.sql, so that it creates a row in ''@'%' mysql.user for the user.

For now, this can be worked around by deleting the row in the mysql.db table and then executing FLUSH PRIVILEGES. For example:

{noformat}
DELETE FROM mysql.db WHERE User='' AND Host='%';
FLUSH PRIVILEGES;
{noformat}

And then the account can be created:

{noformat}
MariaDB [(none)]> CREATE USER ''@'%';
Query OK, 0 rows affected (0.01 sec)
{noformat}

This is documented here:

https://mariadb.com/kb/en/library/create-user/#fixing-a-legacy-default-anonymous-account

Geoff Montee (Inactive) made changes - 2019-02-21 23:43

Description

mysql_install_db only partially creates a ''@'%' user when setting up the test database. This causes errors that are difficult to interpret when someone tries to actually create a ''@'%' user. For example:

{noformat}
MariaDB [(none)]> SELECT VERSION();
+-----------------+
| VERSION() |
+-----------------+
| 10.1.25-MariaDB |
+-----------------+
1 row in set (0.00 sec)

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

MariaDB [(none)]> CREATE USER ''@'%';
ERROR 1396 (HY000): Operation CREATE USER failed for ''@'%'
{noformat}

The root cause of the problem is that scripts/mysql_test_db.sql inserts some rows into mysql.db for the ''@'%' user, but it does not also insert a row into mysql.user for the user:

https://github.com/MariaDB/server/blob/4abb8216a054e14afbeb81e8529e02bab6fa14ac/scripts/mysql_test_db.sql

We should probably fix scripts/mysql_test_db.sql, so that it creates a row in ''@'%' mysql.user for the user.

For now, this can be worked around by deleting the row in the mysql.db table and then executing FLUSH PRIVILEGES. For example:

{noformat}
DELETE FROM mysql.db WHERE User='' AND Host='%';
FLUSH PRIVILEGES;
{noformat}

And then the account can be created:

{noformat}
MariaDB [(none)]> CREATE USER ''@'%';
Query OK, 0 rows affected (0.01 sec)
{noformat}

This is documented here:

https://mariadb.com/kb/en/library/create-user/#fixing-a-legacy-default-anonymous-account

mysql_install_db only partially creates a ''@'%' user when setting up the test database. This causes errors that are difficult to interpret when someone tries to actually create a ''@'%' user. For example:

{noformat}
MariaDB [(none)]> SELECT VERSION();
+-----------------+
| VERSION() |
+-----------------+
| 10.1.25-MariaDB |
+-----------------+
1 row in set (0.00 sec)

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

MariaDB [(none)]> CREATE USER ''@'%';
ERROR 1396 (HY000): Operation CREATE USER failed for ''@'%'
{noformat}

The root cause of the problem is that scripts/mysql_test_db.sql inserts some rows into mysql.db for the ''@'%' user, but it does not also insert a row into mysql.user for the user:

https://github.com/MariaDB/server/blob/4abb8216a054e14afbeb81e8529e02bab6fa14ac/scripts/mysql_test_db.sql

We should probably fix scripts/mysql_test_db.sql, so that it creates a row in mysql.user for the ''@'%' user.

For now, this can be worked around by deleting the row in the mysql.db table and then executing FLUSH PRIVILEGES. For example:

{noformat}
DELETE FROM mysql.db WHERE User='' AND Host='%';
FLUSH PRIVILEGES;
{noformat}

And then the account can be created:

{noformat}
MariaDB [(none)]> CREATE USER ''@'%';
Query OK, 0 rows affected (0.01 sec)
{noformat}

This is documented here:

https://mariadb.com/kb/en/library/create-user/#fixing-a-legacy-default-anonymous-account

Geoff Montee (Inactive) made changes - 2019-08-05 21:45

Link

This issue relates to MDEV-20259 [ MDEV-20259 ]

Sergei Golubchik made changes - 2019-08-07 13:07

Link

This issue blocks MDEV-20259 [ MDEV-20259 ]

Sergei Golubchik made changes - 2019-08-07 13:07

Link

This issue relates to MDEV-20259 [ MDEV-20259 ]

Geoff Montee (Inactive) made changes - 2019-10-31 18:42

Fix Version/s		10.5 [ 23123 ]
Fix Version/s	10.4 [ 22408 ]

Geoff Montee (Inactive) made changes - 2019-11-01 18:51

Description

mysql_install_db only partially creates a ''@'%' user when setting up the test database. This causes errors that are difficult to interpret when someone tries to actually create a ''@'%' user. For example:

{noformat}
MariaDB [(none)]> SELECT VERSION();
+-----------------+
| VERSION() |
+-----------------+
| 10.1.25-MariaDB |
+-----------------+
1 row in set (0.00 sec)

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

MariaDB [(none)]> CREATE USER ''@'%';
ERROR 1396 (HY000): Operation CREATE USER failed for ''@'%'
{noformat}

The root cause of the problem is that scripts/mysql_test_db.sql inserts some rows into mysql.db for the ''@'%' user, but it does not also insert a row into mysql.user for the user:

https://github.com/MariaDB/server/blob/4abb8216a054e14afbeb81e8529e02bab6fa14ac/scripts/mysql_test_db.sql

We should probably fix scripts/mysql_test_db.sql, so that it creates a row in mysql.user for the ''@'%' user.

For now, this can be worked around by deleting the row in the mysql.db table and then executing FLUSH PRIVILEGES. For example:

{noformat}
DELETE FROM mysql.db WHERE User='' AND Host='%';
FLUSH PRIVILEGES;
{noformat}

And then the account can be created:

{noformat}
MariaDB [(none)]> CREATE USER ''@'%';
Query OK, 0 rows affected (0.01 sec)
{noformat}

This is documented here:

https://mariadb.com/kb/en/library/create-user/#fixing-a-legacy-default-anonymous-account

Currently, {{mysql_install_db}} inserts some rows into the {{mysql.db}} table for the {{''@'%'}} user account, but it does not insert any rows into the {{mysql.user}} table for that user account. For example:

{noformat}
MariaDB [(none)]> SELECT * FROM mysql.user WHERE User='' AND Host='%'\G
Empty set (0.00 sec)

MariaDB [(none)]> 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)
{noformat}

These rows are currently inserted by the {{scripts/mysql_test_db.sql}} script:

https://github.com/MariaDB/server/blob/mariadb-10.4.8/scripts/mysql_test_db.sql#L18

This behavior is apparently an artifact of MySQL 3.22, which implemented privileges prior to the implementation of the {{GRANT}} statement.

The effect of this is that {{mysql_install_db}} creates privileges for the {{''@'%'}} user account, but the user account doesn't really exist from the perspective of other DCL statements like {{GRANT}}, {{CREATE USER}}, {{ALTER USER}}, and {{DROP USER}}.

If someone tries to actually create a {{''@'%'}} user account, then they will see errors that are difficult to interpret. For example:

{noformat}
MariaDB [(none)]> CREATE USER ''@'%';
ERROR 1396 (HY000): Operation CREATE USER failed for ''@'%'
{noformat}

We should probably fix {{scripts/mysql_test_db.sql}}, so that it creates a row in the {{mysql.user}} table for the {{''@'%'}} user account.

For now, this can be worked around by deleting the row in the mysql.db table and then executing FLUSH PRIVILEGES. For example:

{noformat}
DELETE FROM mysql.db WHERE User='' AND Host='%';
FLUSH PRIVILEGES;
{noformat}

And then the account can be created:

{noformat}
MariaDB [(none)]> CREATE USER ''@'%';
Query OK, 0 rows affected (0.01 sec)
{noformat}

This is documented here:

https://mariadb.com/kb/en/library/create-user/#fixing-a-legacy-default-anonymous-account

Geoff Montee (Inactive) made changes - 2019-11-01 18:55

Description

Currently, {{mysql_install_db}} inserts some rows into the {{mysql.db}} table for the {{''@'%'}} user account, but it does not insert any rows into the {{mysql.user}} table for that user account. For example:

{noformat}
MariaDB [(none)]> SELECT * FROM mysql.user WHERE User='' AND Host='%'\G
Empty set (0.00 sec)

MariaDB [(none)]> 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)
{noformat}

These rows are currently inserted by the {{scripts/mysql_test_db.sql}} script:

https://github.com/MariaDB/server/blob/mariadb-10.4.8/scripts/mysql_test_db.sql#L18

This behavior is apparently an artifact of MySQL 3.22, which implemented privileges prior to the implementation of the {{GRANT}} statement.

The effect of this is that {{mysql_install_db}} creates privileges for the {{''@'%'}} user account, but the user account doesn't really exist from the perspective of other DCL statements like {{GRANT}}, {{CREATE USER}}, {{ALTER USER}}, and {{DROP USER}}.

If someone tries to actually create a {{''@'%'}} user account, then they will see errors that are difficult to interpret. For example:

{noformat}
MariaDB [(none)]> CREATE USER ''@'%';
ERROR 1396 (HY000): Operation CREATE USER failed for ''@'%'
{noformat}

We should probably fix {{scripts/mysql_test_db.sql}}, so that it creates a row in the {{mysql.user}} table for the {{''@'%'}} user account.

For now, this can be worked around by deleting the row in the mysql.db table and then executing FLUSH PRIVILEGES. For example:

{noformat}
DELETE FROM mysql.db WHERE User='' AND Host='%';
FLUSH PRIVILEGES;
{noformat}

And then the account can be created:

{noformat}
MariaDB [(none)]> CREATE USER ''@'%';
Query OK, 0 rows affected (0.01 sec)
{noformat}

This is documented here:

https://mariadb.com/kb/en/library/create-user/#fixing-a-legacy-default-anonymous-account

Currently, {{mysql_install_db}} provides default access to the test database by inserting some rows into the {{mysql.db}} table for the {{''@'%'}} user account, but it does not insert any rows into the {{mysql.user}} table for that user account. For example:

{noformat}
MariaDB [(none)]> SELECT * FROM mysql.user WHERE User='' AND Host='%'\G
Empty set (0.00 sec)

MariaDB [(none)]> 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)
{noformat}

These rows are currently inserted by the {{scripts/mysql_test_db.sql}} script:

https://github.com/MariaDB/server/blob/mariadb-10.4.8/scripts/mysql_test_db.sql#L18

This behavior is apparently an artifact of MySQL 3.22, which implemented privileges prior to the implementation of the {{GRANT}} statement.

The effect of this is that {{mysql_install_db}} creates privileges for the {{''@'%'}} user account, but the user account doesn't really exist from the perspective of other DCL statements like {{GRANT}}, {{CREATE USER}}, {{ALTER USER}}, and {{DROP USER}}.

If someone tries to actually create a {{''@'%'}} user account, then they will see errors that are difficult to interpret. For example:

{noformat}
MariaDB [(none)]> CREATE USER ''@'%';
ERROR 1396 (HY000): Operation CREATE USER failed for ''@'%'
{noformat}

We should probably fix {{scripts/mysql_test_db.sql}}, so that it creates a row in the {{mysql.user}} table for the {{''@'%'}} user account.

For now, this can be worked around by deleting the row in the mysql.db table and then executing FLUSH PRIVILEGES. For example:

{noformat}
DELETE FROM mysql.db WHERE User='' AND Host='%';
FLUSH PRIVILEGES;
{noformat}

And then the account can be created:

{noformat}
MariaDB [(none)]> CREATE USER ''@'%';
Query OK, 0 rows affected (0.01 sec)
{noformat}

This is documented here:

https://mariadb.com/kb/en/library/create-user/#fixing-a-legacy-default-anonymous-account

Geoff Montee (Inactive) made changes - 2019-11-01 18:55

Description

Currently, {{mysql_install_db}} provides default access to the test database by inserting some rows into the {{mysql.db}} table for the {{''@'%'}} user account, but it does not insert any rows into the {{mysql.user}} table for that user account. For example:

{noformat}
MariaDB [(none)]> SELECT * FROM mysql.user WHERE User='' AND Host='%'\G
Empty set (0.00 sec)

MariaDB [(none)]> 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)
{noformat}

These rows are currently inserted by the {{scripts/mysql_test_db.sql}} script:

https://github.com/MariaDB/server/blob/mariadb-10.4.8/scripts/mysql_test_db.sql#L18

This behavior is apparently an artifact of MySQL 3.22, which implemented privileges prior to the implementation of the {{GRANT}} statement.

The effect of this is that {{mysql_install_db}} creates privileges for the {{''@'%'}} user account, but the user account doesn't really exist from the perspective of other DCL statements like {{GRANT}}, {{CREATE USER}}, {{ALTER USER}}, and {{DROP USER}}.

If someone tries to actually create a {{''@'%'}} user account, then they will see errors that are difficult to interpret. For example:

{noformat}
MariaDB [(none)]> CREATE USER ''@'%';
ERROR 1396 (HY000): Operation CREATE USER failed for ''@'%'
{noformat}

We should probably fix {{scripts/mysql_test_db.sql}}, so that it creates a row in the {{mysql.user}} table for the {{''@'%'}} user account.

For now, this can be worked around by deleting the row in the mysql.db table and then executing FLUSH PRIVILEGES. For example:

{noformat}
DELETE FROM mysql.db WHERE User='' AND Host='%';
FLUSH PRIVILEGES;
{noformat}

And then the account can be created:

{noformat}
MariaDB [(none)]> CREATE USER ''@'%';
Query OK, 0 rows affected (0.01 sec)
{noformat}

This is documented here:

https://mariadb.com/kb/en/library/create-user/#fixing-a-legacy-default-anonymous-account

Currently, {{mysql_install_db}} provides default access to the {{test}} database by inserting some rows into the {{mysql.db}} table for the {{''@'%'}} user account, but it does not insert any rows into the {{mysql.user}} table for that user account. For example:

{noformat}
MariaDB [(none)]> SELECT * FROM mysql.user WHERE User='' AND Host='%'\G
Empty set (0.00 sec)

MariaDB [(none)]> 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)
{noformat}

These rows are currently inserted by the {{scripts/mysql_test_db.sql}} script:

https://github.com/MariaDB/server/blob/mariadb-10.4.8/scripts/mysql_test_db.sql#L18

This behavior is apparently an artifact of MySQL 3.22, which implemented privileges prior to the implementation of the {{GRANT}} statement.

The effect of this is that {{mysql_install_db}} creates privileges for the {{''@'%'}} user account, but the user account doesn't really exist from the perspective of other DCL statements like {{GRANT}}, {{CREATE USER}}, {{ALTER USER}}, and {{DROP USER}}.

If someone tries to actually create a {{''@'%'}} user account, then they will see errors that are difficult to interpret. For example:

{noformat}
MariaDB [(none)]> CREATE USER ''@'%';
ERROR 1396 (HY000): Operation CREATE USER failed for ''@'%'
{noformat}

We should probably fix {{scripts/mysql_test_db.sql}}, so that it creates a row in the {{mysql.user}} table for the {{''@'%'}} user account.

For now, this can be worked around by deleting the row in the mysql.db table and then executing FLUSH PRIVILEGES. For example:

{noformat}
DELETE FROM mysql.db WHERE User='' AND Host='%';
FLUSH PRIVILEGES;
{noformat}

And then the account can be created:

{noformat}
MariaDB [(none)]> CREATE USER ''@'%';
Query OK, 0 rows affected (0.01 sec)
{noformat}

This is documented here:

https://mariadb.com/kb/en/library/create-user/#fixing-a-legacy-default-anonymous-account

Geoff Montee (Inactive) made changes - 2019-11-01 19:09

Link

This issue relates to ~~MDEV-20947~~ [ ~~MDEV-20947~~ ]

Geoff Montee (Inactive) made changes - 2019-11-15 20:32

Link

This issue relates to MENT-506 [ MENT-506 ]

Sergei Golubchik made changes - 2020-02-17 10:33

Affects Version/s	10.2 [ 14601 ]
Affects Version/s	5.5 [ 15800 ]
Affects Version/s	10.0 [ 16000 ]
Affects Version/s	10.1 [ 16100 ]
Affects Version/s	10.3 [ 22126 ]
Affects Version/s	10.4 [ 22408 ]
Issue Type	Bug [ 1 ]	Task [ 3 ]

Ralf Gebhardt made changes - 2020-08-05 08:40

Fix Version/s

10.5 [ 23123 ]

Sergei Golubchik made changes - 2021-12-06 21:22

Workflow

MariaDB v3 [ 81979 ]

MariaDB v4 [ 131796 ]

Julien Fritsch made changes - 2022-04-21 09:36

Priority

Minor [ 4 ]

Major [ 3 ]

Sergei Golubchik made changes - 2022-10-21 14:24

Fix Version/s		N/A [ 14700 ]
Resolution		Won't Fix [ 2 ]
Status	Confirmed [ 10101 ]	Closed [ 6 ]

MariaDB Server

Make mysql_install_db create a real ''@'%' anonymous account for the test database

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration