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

pt-show-grants --revoke option is broken on mysql 8

    XMLWordPrintable

    Details

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

      Description

      Notice the error in the last line and the output is not full (role grants are printed, but not grants for the user):

       ✘ plavi@bender  bin  ./pt-show-grants --revoke --socket=/tmp/mysql_sandbox8011.sock --user=msandbox --password=msandbox --only=msandbox
      -- Grants dumped by pt-show-grants
      -- Dumped from server Localhost via UNIX socket, MySQL 8.0.11 at 2018-05-22 11:34:11
      -- Roles
      CREATE ROLE IF NOT EXISTS `R_DO_IT_ALL`;
      -- End of roles listing
      -- Revoke statements for 'R_DO_IT_ALL'@'%'
      REVOKE 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 *.* FROM `R_DO_IT_ALL`@`%`;
      REVOKE 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 *.* FROM `R_DO_IT_ALL`@`%`;
      -- Grants for 'R_DO_IT_ALL'@'%'
      CREATE USER IF NOT EXISTS 'R_DO_IT_ALL'@'%';
      ALTER USER 'R_DO_IT_ALL'@'%' IDENTIFIED WITH 'mysql_native_password' REQUIRE NONE PASSWORD EXPIRE ACCOUNT LOCK 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 `R_DO_IT_ALL`@`%`;
      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 `R_DO_IT_ALL`@`%`;
      Use of uninitialized value $grants in concatenation (.) or string at ./pt-show-grants line 2049.
      

      If I remove the "revoke" option the output is following:

       ✘ plavi@bender  bin  ./pt-show-grants --socket=/tmp/mysql_sandbox8011.sock --user=msandbox --password=msandbox --only=msandbox
      -- Grants dumped by pt-show-grants
      -- Dumped from server Localhost via UNIX socket, MySQL 8.0.11 at 2018-05-22 11:37:54
      -- Roles
      CREATE ROLE IF NOT EXISTS `R_DO_IT_ALL`;
      -- End of roles listing
      -- Grants for 'R_DO_IT_ALL'@'%'
      CREATE USER IF NOT EXISTS 'R_DO_IT_ALL'@'%';
      ALTER USER 'R_DO_IT_ALL'@'%' IDENTIFIED WITH 'mysql_native_password' REQUIRE NONE PASSWORD EXPIRE ACCOUNT LOCK 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 `R_DO_IT_ALL`@`%`;
      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 `R_DO_IT_ALL`@`%`;
      -- Grants for 'msandbox'@'127.%'
      CREATE USER IF NOT EXISTS 'msandbox'@'127.%';
      ALTER USER 'msandbox'@'127.%' IDENTIFIED WITH 'mysql_native_password' AS '*6C387FC3893DBA1E3BA155E74754DA6682D04747' DEFAULT ROLE `R_DO_IT_ALL`@`%` REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT;
      GRANT USAGE ON *.* TO `msandbox`@`127.%`;
      GRANT `R_DO_IT_ALL`@`%` TO `msandbox`@`127.%`;
      -- Grants for 'msandbox'@'localhost'
      CREATE USER IF NOT EXISTS 'msandbox'@'localhost';
      ALTER USER 'msandbox'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*6C387FC3893DBA1E3BA155E74754DA6682D04747' DEFAULT ROLE `R_DO_IT_ALL`@`%` REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT;
      GRANT USAGE ON *.* TO `msandbox`@`localhost`;
      GRANT `R_DO_IT_ALL`@`%` TO `msandbox`@`localhost`;
      

      The "only=msandbox" option is not relevant here and it works the same without it - I just used it to lower the amount of output.

      To reproduce just use "dbdeployer single 8.0.11" since it creates some roles and grants for msandbox user already.

      Also the issue is not reproducible on MySQL 5.7.21. with revoke option.

      The issue can be seen with previous PT releases also on mysql 8 - so it's not related to latest changes in this version.

        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