Uploaded image for project: 'Percona Toolkit'
  1. Percona Toolkit
  2. PT-1560

pt-show-grants doesn't handle hosts part of MySQL 8 roles

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open
    • Priority: High
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None
    • Labels:
      None

      Description

      MySQL manual says:

      Role names are much like user account names and consist of a user part and host part in 'user_name'@'host_name' format. The host part, if omitted, defaults to '%'. The user and host parts can be unquoted unless they contain special characters such as - or %. Unlike account names, the user part of role names cannot be blank. For additional information, see Section 6.2.5, “Specifying Role Names”.
      

      If I create a role like this: "create role W_TEST@'localhost';" and I do a pt-show-grants dump and then re-enter that data into empty instance I'll get two entries in user table instead of one because the first create role command from pt-show-grants omitted the host part.

      Here's how it looks:
      INITIAL CREATION:

      mysql> select User,Host,account_locked,plugin from mysql.user where User like '%W_TEST%';
      Empty set (0,00 sec)
      
      mysql> create role W_TEST@'localhost';
      Query OK, 0 rows affected (0,05 sec)
      
      mysql> grant update on test.* to W_TEST@'localhost';
      Query OK, 0 rows affected (0,06 sec)
      
      mysql> grant W_TEST@'localhost' to root@'localhost';
      Query OK, 0 rows affected (0,04 sec)
      
      mysql> select User,Host,account_locked,plugin from mysql.user where User like '%W_TEST%';
      +--------+-----------+----------------+-----------------------+
      | User   | Host      | account_locked | plugin                |
      +--------+-----------+----------------+-----------------------+
      | W_TEST | localhost | Y              | mysql_native_password |
      +--------+-----------+----------------+-----------------------+
      1 row in set (0,00 sec)
      
      mysql> select * from mysql.role_edges;
      +-----------+---------------+-----------+-------------+-------------------+
      | FROM_HOST | FROM_USER     | TO_HOST   | TO_USER     | WITH_ADMIN_OPTION |
      +-----------+---------------+-----------+-------------+-------------------+
      | %         | R_DO_IT_ALL   | 127.%     | msandbox    | N                 |
      | %         | R_DO_IT_ALL   | localhost | msandbox    | N                 |
      | %         | R_READ_ONLY   | 127.%     | msandbox_ro | N                 |
      | %         | R_READ_ONLY   | localhost | msandbox_ro | N                 |
      | %         | R_READ_WRITE  | 127.%     | msandbox_rw | N                 |
      | %         | R_READ_WRITE  | localhost | msandbox_rw | N                 |
      | %         | R_REPLICATION | 127.%     | rsandbox    | N                 |
      | localhost | W_TEST        | localhost | root        | N                 |
      +-----------+---------------+-----------+-------------+-------------------+
      8 rows in set (0,00 sec)
      

      PT-SHOW-GRANTS OUTPUT:

      plavi@bender  bin  ./pt-show-grants --socket=/tmp/mysql_sandbox8011.sock --user=msandbox --password=msandbox --only=root
      -- Grants dumped by pt-show-grants
      -- Dumped from server Localhost via UNIX socket, MySQL 8.0.11 at 2018-05-22 13:35:09
      -- Roles
      CREATE ROLE IF NOT EXISTS `W_TEST`;
      -- End of roles listing
      -- Grants for 'W_TEST'@'localhost'
      CREATE USER IF NOT EXISTS 'W_TEST'@'localhost';
      ALTER USER 'W_TEST'@'localhost' IDENTIFIED WITH 'mysql_native_password' REQUIRE NONE PASSWORD EXPIRE ACCOUNT LOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT;
      GRANT UPDATE ON `test`.* TO `W_TEST`@`localhost`;
      GRANT USAGE ON *.* TO `W_TEST`@`localhost`;
      -- Grants for 'root'@'localhost'
      CREATE USER IF NOT EXISTS 'root'@'localhost';
      ALTER USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*6C387FC3893DBA1E3BA155E74754DA6682D04747' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT;
      GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROLE, CREATE ROUTINE, CREATE TABLESPACE, CREATE TEMPORARY TABLES, CREATE USER, CREATE VIEW, DELETE, DROP, DROP ROLE, EVENT, EXECUTE, FILE, INDEX, INSERT, LOCK TABLES, PROCESS, REFERENCES, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SELECT, SHOW DATABASES, SHOW VIEW, SHUTDOWN, SUPER, TRIGGER, UPDATE ON *.* TO `root`@`localhost` WITH GRANT OPTION;
      GRANT BACKUP_ADMIN,BINLOG_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SET_USER_ID,SYSTEM_VARIABLES_ADMIN,XA_RECOVER_ADMIN ON *.* TO `root`@`localhost` WITH GRANT OPTION;
      GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION;
      GRANT `W_TEST`@`localhost` TO `root`@`localhost`;
      

      MANUAL ENTER STUFF FROM PT-SHOW-GRANTS:

      mysql> select User,Host,account_locked,plugin from mysql.user where User like '%W_TEST%';
      Empty set (0,00 sec)
      
      mysql> select * from mysql.role_edges;
      +-----------+---------------+-----------+-------------+-------------------+
      | FROM_HOST | FROM_USER     | TO_HOST   | TO_USER     | WITH_ADMIN_OPTION |
      +-----------+---------------+-----------+-------------+-------------------+
      | %         | R_DO_IT_ALL   | 127.%     | msandbox    | N                 |
      | %         | R_DO_IT_ALL   | localhost | msandbox    | N                 |
      | %         | R_READ_ONLY   | 127.%     | msandbox_ro | N                 |
      | %         | R_READ_ONLY   | localhost | msandbox_ro | N                 |
      | %         | R_READ_WRITE  | 127.%     | msandbox_rw | N                 |
      | %         | R_READ_WRITE  | localhost | msandbox_rw | N                 |
      | %         | R_REPLICATION | 127.%     | rsandbox    | N                 |
      +-----------+---------------+-----------+-------------+-------------------+
      7 rows in set (0,00 sec)
      
      mysql> CREATE ROLE IF NOT EXISTS `W_TEST`;
      Query OK, 0 rows affected (0,10 sec)
      
      mysql> CREATE USER IF NOT EXISTS 'W_TEST'@'localhost';
      Query OK, 0 rows affected (0,10 sec)
      
      mysql> ALTER USER 'W_TEST'@'localhost' IDENTIFIED WITH 'mysql_native_password' REQUIRE NONE PASSWORD EXPIRE ACCOUNT LOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT;
      Query OK, 0 rows affected (0,02 sec)
      
      mysql> GRANT UPDATE ON `test`.* TO `W_TEST`@`localhost`;
      Query OK, 0 rows affected (0,06 sec)
      
      mysql> GRANT USAGE ON *.* TO `W_TEST`@`localhost`;
      Query OK, 0 rows affected (0,00 sec)
      
      mysql> GRANT `W_TEST`@`localhost` TO `root`@`localhost`;
      Query OK, 0 rows affected (0,10 sec)
      
      mysql> select * from mysql.role_edges;
      +-----------+---------------+-----------+-------------+-------------------+
      | FROM_HOST | FROM_USER     | TO_HOST   | TO_USER     | WITH_ADMIN_OPTION |
      +-----------+---------------+-----------+-------------+-------------------+
      | %         | R_DO_IT_ALL   | 127.%     | msandbox    | N                 |
      | %         | R_DO_IT_ALL   | localhost | msandbox    | N                 |
      | %         | R_READ_ONLY   | 127.%     | msandbox_ro | N                 |
      | %         | R_READ_ONLY   | localhost | msandbox_ro | N                 |
      | %         | R_READ_WRITE  | 127.%     | msandbox_rw | N                 |
      | %         | R_READ_WRITE  | localhost | msandbox_rw | N                 |
      | %         | R_REPLICATION | 127.%     | rsandbox    | N                 |
      | localhost | W_TEST        | localhost | root        | N                 |
      +-----------+---------------+-----------+-------------+-------------------+
      8 rows in set (0,00 sec)
      
      mysql> select User,Host,account_locked,plugin from mysql.user where User like '%W_TEST%';
      +--------+-----------+----------------+-----------------------+
      | User   | Host      | account_locked | plugin                |
      +--------+-----------+----------------+-----------------------+
      | W_TEST | %         | Y              | mysql_native_password |
      | W_TEST | localhost | Y              | mysql_native_password |
      +--------+-----------+----------------+-----------------------+
      2 rows in set (0,00 sec)
      

      As you can see in the last query we have two rows but we should have only one as in the initial setup, "W_TEST@%" is not needed here but pt-show-grants created it because it used only "CREATE ROLE IF NOT EXISTS `W_TEST`;" without specifying host part.

        Smart Checklist

          Attachments

            Activity

              People

              • Assignee:
                carlos.salguero Carlos Salguero
                Reporter:
                tomislav.plavcic@percona.com Tomislav Plavcic
              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated:

                  Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0 minutes
                  0m
                  Logged:
                  Time Spent - 15 minutes
                  15m