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

    • New Feature
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • None
    • Scripts & Clients
    • None

    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

              ralf.gebhardt Ralf Gebhardt
              GeoffMontee Geoff Montee (Inactive)
              Votes:
              1 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.