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

mysql_secure_installation should use DDL and DCL instead of DML

    XMLWordPrintable

    Details

      Description

      mysql_secure_installation currently uses DML for all of its tasks. I think it should use DDL and DCL instead. The main reason is that I think it would be safer in environments where replication is used. Currently, slaves can see errors like this:

      2019-08-05 17:09:10 11 [ERROR] mysqld: Can't find record in 'global_priv'
      2019-08-05 17:09:10 11 [ERROR] Slave SQL: Could not execute Delete_rows_v1 event on table mysql.global_priv; Can't find record in 'global_priv', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mariadb-bin.000002, end_log_pos 7529, Gtid 0-1-111, Internal MariaDB error code: 1032
      2019-08-05 17:09:10 11 [Warning] Slave: Can't find record in 'global_priv' Error_code: 1032
      2019-08-05 17:09:10 11 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mariadb-bin.000002' position 7289
      

      • To set the root user's password, it currently uses the following statement:

      UPDATE mysql.global_priv SET priv=json_set(priv, '$.plugin', 'mysql_native_password', '$.authentication_string', PASSWORD('$esc_pass')) WHERE User='root';"
      

      https://github.com/MariaDB/server/blob/mariadb-10.4.7/scripts/mysql_secure_installation.sh#L311

      I think it should use ALTER USER IF EXISTS instead. For example:

      ALTER USER IF EXISTS 'root'@'localhost' IDENTIFIED VIA mysql_native_password USING PASSWORD('$esc_pass');
      ALTER USER IF EXISTS 'root'@'127.0.0.1' IDENTIFIED VIA mysql_native_password USING PASSWORD('$esc_pass');
      ALTER USER IF EXISTS 'root'@'::1' IDENTIFIED VIA mysql_native_password USING PASSWORD('$esc_pass');
      

      • To delete the anonymous users, it currently uses the following statement:

      DELETE FROM mysql.global_priv WHERE User='';"
      

      https://github.com/MariaDB/server/blob/mariadb-10.4.7/scripts/mysql_secure_installation.sh#L331

      I think it should use DROP USER IF EXISTS instead. For example:

      DROP USER IF EXISTS ''@'%';
      

      MDEV-13486 might have to be fixed before this could work.

      • To delete the non-local root users, it currently uses the following statement:

      DELETE FROM mysql.global_priv WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');"
      

      https://github.com/MariaDB/server/blob/mariadb-10.4.7/scripts/mysql_secure_installation.sh#L343

      I think it should use DROP USER IF EXISTS instead. For example:

      DROP USER IF EXISTS 'root'@'$HOSTNAME';
      

      • To remove privileges on the test database, it currently uses the following statement:

      DELETE FROM mysql.db WHERE Db='test' OR Db='test\\_%'
      

      https://github.com/MariaDB/server/blob/mariadb-10.4.7/scripts/mysql_secure_installation.sh#L361

      I think it should use REVOKE instead. For example:

      REVOKE ALL PRIVILEGES ON test.* FROM ''@'%';
      

      MDEV-13486 might have to be fixed before this could work.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              ralf.gebhardt@mariadb.com Ralf Gebhardt
              Reporter:
              GeoffMontee Geoff Montee
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated: