[MDEV-13486] Make mysql_install_db create a real ''@'%' anonymous account for the test database Created: 2017-08-09  Updated: 2022-10-21  Resolved: 2022-10-21

Status: Closed
Project: MariaDB Server
Component/s: Authentication and Privilege System, Scripts & Clients
Fix Version/s: N/A

Type: Task Priority: Major
Reporter: Geoff Montee (Inactive) Assignee: Sergei Golubchik
Resolution: Won't Fix Votes: 0
Labels: privileges

Issue Links:
Blocks
blocks MDEV-20259 mysql_secure_installation should use ... Open
Relates
relates to MDEV-20947 Use GRANT ... TO PUBLIC for default t... Closed

 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:

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)

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:

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

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:

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

And then the account can be created:

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

This is documented here:

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

 Comments   
Comment by Sergei Golubchik [ 2017-08-10 ]

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.

Comment by Geoff Montee (Inactive) [ 2017-08-10 ]

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.

Comment by Ian Gilfillan [ 2018-10-24 ]

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.

Comment by Sergei Golubchik [ 2019-04-10 ]

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.

Comment by Sergei Golubchik [ 2019-11-01 ]

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
Comment by Geoff Montee (Inactive) [ 2019-11-01 ]

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.

Comment by Daniel Black [ 2021-06-08 ]

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.

Comment by Sergei Golubchik [ 2022-10-21 ]

Made obsolete by MDEV-20947

Generated at Thu Feb 08 08:05:58 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.