Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-13486

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

    XMLWordPrintable

    Details

      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

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              serg Sergei Golubchik
              Reporter:
              GeoffMontee Geoff Montee
              Votes:
              0 Vote for this issue
              Watchers:
              6 Start watching this issue

                Dates

                Created:
                Updated: