[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 added a comment - 2017-08-10 09:57

This is neither.

You cannot create a user if there are already some privileges granted to this user.

And in the default setup, ''@'%' has all privileges on test.*.

This is how it always was, even in 3.22, before GRANT statement was implemented. So it's something that is not fully compatible with GRANT and cannot be created with GRANT — there is no row for ''@'%' in mysql.user table, but there is such a row in mysql.db table.

Perhaps we should make it consistent and create a matching row in mysql.user too.

Sergei Golubchik added a comment - 2017-08-10 09:57 This is neither. You cannot create a user if there are already some privileges granted to this user. And in the default setup, ''@'%' has all privileges on test.* . This is how it always was, even in 3.22, before GRANT statement was implemented. So it's something that is not fully compatible with GRANT and cannot be created with GRANT — there is no row for ''@'%' in mysql.user table, but there is such a row in mysql.db table. Perhaps we should make it consistent and create a matching row in mysql.user too.

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) added a comment - 2017-08-10 18:08

You cannot create a user if there are already some privileges granted to this user.
And in the default setup, ''@'%' has all privileges on test.*.

Oh, interesting. You're right, this works if I delete those privileges:

MariaDB [(none)]> CREATE USER ''@'%';

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

MariaDB [(none)]> SELECT User, Host FROM mysql.db;

+------+------+

| User | Host |

+------+------+

|      | %    |

|      | %    |

+------+------+

2 rows in set (0.01 sec)

MariaDB [(none)]> DELETE FROM mysql.db WHERE User='' AND Host='%';

Query OK, 2 rows affected (0.00 sec)

MariaDB [(none)]> FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> CREATE USER ''@'%';

Query OK, 0 rows affected (0.01 sec)

Perhaps we should make it consistent and create a matching row in mysql.user too.

That sounds like a reasonable fix to me.

Geoff Montee (Inactive) added a comment - 2017-08-10 18:08 You cannot create a user if there are already some privileges granted to this user. And in the default setup, ''@'%' has all privileges on test.*. Oh, interesting. You're right, this works if I delete those privileges: MariaDB [(none)]> CREATE USER ''@'%'; ERROR 1396 (HY000): Operation CREATE USER failed for ''@'%' MariaDB [(none)]> SELECT User, Host FROM mysql.db; +------+------+ | User | Host | +------+------+ | | % | | | % | +------+------+ 2 rows in set (0.01 sec) MariaDB [(none)]> DELETE FROM mysql.db WHERE User='' AND Host='%'; Query OK, 2 rows affected (0.00 sec) MariaDB [(none)]> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> CREATE USER ''@'%'; Query OK, 0 rows affected (0.01 sec) Perhaps we should make it consistent and create a matching row in mysql.user too. That sounds like a reasonable fix to me.

Ian Gilfillan added a comment - 2018-10-24 10:53

I have clarified the docs to explain this. I agree a matching row in mysql.user makes sense. This bug can either be renamed, or closed and a new task created.

Ian Gilfillan added a comment - 2018-10-24 10:53 I have clarified the docs to explain this. I agree a matching row in mysql.user makes sense. This bug can either be renamed, or closed and a new task created.

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

Sergei Golubchik added a comment - 2019-04-10 13:48

Hmm. If mysql_install_db will create such a user, it'll have to create it locked, otherwise it'll allow anonymous passwordless remote connections to the server.

But even if locked, it'll be a catch-all account, so instead for an unexistent user name the error will be not "wrong password" anymore, but "account locked". I think that might be quite unexpected.

Sergei Golubchik added a comment - 2019-04-10 13:48 Hmm. If mysql_install_db will create such a user, it'll have to create it locked , otherwise it'll allow anonymous passwordless remote connections to the server. But even if locked, it'll be a catch-all account, so instead for an unexistent user name the error will be not "wrong password" anymore, but "account locked". I think that might be quite unexpected.

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 ]

Sergei Golubchik added a comment - 2019-11-01 07:57

Two other options:

not to create test db with everybody's full access to it. This is what Debian is doing for many years already
use ~~MDEV-5215~~ (when it's implemented) instead of 3.22 pre-grant method for allowing everyone to access test db

Sergei Golubchik added a comment - 2019-11-01 07:57 Two other options: not to create test db with everybody's full access to it. This is what Debian is doing for many years already use MDEV-5215 (when it's implemented) instead of 3.22 pre-grant method for allowing everyone to access test db

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) added a comment - 2019-11-01 19:10

use ~~MDEV-5215~~ (when it's implemented) instead of 3.22 pre-grant method for allowing everyone to access test db

I created ~~MDEV-20947~~ to track that idea.

Geoff Montee (Inactive) added a comment - 2019-11-01 19:10 use MDEV-5215 (when it's implemented) instead of 3.22 pre-grant method for allowing everyone to access test db I created MDEV-20947 to track that idea.

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 ]

Daniel Black added a comment - 2021-06-08 01:55

While waiting for ~~MDEV-5215~~ and ~~MDEV-20947~~ how about giving access to the test databases to anonymous users only? 10.6?

diff --git a/scripts/mysql_test_db.sql b/scripts/mysql_test_db.sql

index 9f8a0cf604c..edf1242c6c5 100644

--- a/scripts/mysql_test_db.sql

+++ b/scripts/mysql_test_db.sql

@@ -15,17 +15,12 @@

 CREATE DATABASE IF NOT EXISTS test CHARACTER SET latin1 COLLATE latin1_swedish_ci;

--- Fill "db" table with default grants for anyone to

--- access database 'test' and 'test_%' if "db" table didn't exist

-CREATE TEMPORARY TABLE tmp_db LIKE db;

-INSERT INTO tmp_db VALUES ('%','test','','Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y','Y','Y','Y','N','N','Y','Y','Y');

-INSERT INTO tmp_db VALUES ('%','test\_%','','Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y','Y','Y','Y','N','N','Y','Y','Y');

-INSERT INTO db SELECT * FROM tmp_db WHERE @had_db_table=0;

-DROP TABLE tmp_db;

 -- Anonymous user with no privileges.

 CREATE TEMPORARY TABLE tmp_user_anonymous LIKE global_priv;

 INSERT INTO tmp_user_anonymous (host,user) VALUES ('localhost','');

 INSERT INTO tmp_user_anonymous (host,user) SELECT @current_hostname,'' FROM dual WHERE @current_hostname != 'localhost';

 INSERT INTO global_priv SELECT * FROM tmp_user_anonymous WHERE @had_user_table=0;

+-- access database 'test' and 'test_%' for anonymous users if "db" table exists

+INSERT INTO db SELECT host,'test',user,'Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y','Y','Y','Y','N','N','Y','Y','Y' FROM tmp_user_anonymous WHERE @had_db_table=0 AND @had_db_table=0;

+INSERT INTO db SELECT host,'test\_%',user,'Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y','Y','Y','Y','N','N','Y','Y','Y' FROM tmp_user_anonymous WHERE @had_db_table=0 AND @had_db_table=0;

 DROP TABLE tmp_user_anonymous;

+ mysql_secure_installation changes to cope with old and new versions.

Daniel Black added a comment - 2021-06-08 01:55 While waiting for MDEV-5215 and MDEV-20947 how about giving access to the test databases to anonymous users only? 10.6? diff --git a/scripts/mysql_test_db.sql b/scripts/mysql_test_db.sql index 9f8a0cf604c..edf1242c6c5 100644 --- a/scripts/mysql_test_db.sql +++ b/scripts/mysql_test_db.sql @@ -15,17 +15,12 @@ CREATE DATABASE IF NOT EXISTS test CHARACTER SET latin1 COLLATE latin1_swedish_ci; --- Fill "db" table with default grants for anyone to --- access database 'test' and 'test_%' if "db" table didn't exist -CREATE TEMPORARY TABLE tmp_db LIKE db; -INSERT INTO tmp_db VALUES ('%','test','','Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y','Y','Y','Y','N','N','Y','Y','Y'); -INSERT INTO tmp_db VALUES ('%','test\_%','','Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y','Y','Y','Y','N','N','Y','Y','Y'); -INSERT INTO db SELECT * FROM tmp_db WHERE @had_db_table=0; -DROP TABLE tmp_db; - -- Anonymous user with no privileges. CREATE TEMPORARY TABLE tmp_user_anonymous LIKE global_priv; INSERT INTO tmp_user_anonymous (host,user) VALUES ('localhost',''); INSERT INTO tmp_user_anonymous (host,user) SELECT @current_hostname,'' FROM dual WHERE @current_hostname != 'localhost'; INSERT INTO global_priv SELECT * FROM tmp_user_anonymous WHERE @had_user_table=0; +-- access database 'test' and 'test_%' for anonymous users if "db" table exists +INSERT INTO db SELECT host,'test',user,'Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y','Y','Y','Y','N','N','Y','Y','Y' FROM tmp_user_anonymous WHERE @had_db_table=0 AND @had_db_table=0; +INSERT INTO db SELECT host,'test\_%',user,'Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y','Y','Y','Y','N','N','Y','Y','Y' FROM tmp_user_anonymous WHERE @had_db_table=0 AND @had_db_table=0; DROP TABLE tmp_user_anonymous; + mysql_secure_installation changes to cope with old and new versions.

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 added a comment - 2022-10-21 14:24

Made obsolete by ~~MDEV-20947~~

Sergei Golubchik added a comment - 2022-10-21 14:24 Made obsolete by MDEV-20947

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