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

pt-show-grants has additional create/alter line for roles

    XMLWordPrintable

    Details

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

      Description

      Consider output:

       plavi@bender  bin  ./pt-show-grants --drop --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 12:05:48
      -- 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`@`%`;
      DROP USER 'msandbox'@'127.%';
      DELETE FROM `mysql`.`user` WHERE `User`='msandbox' AND `Host`='127.%';
      -- 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.%`;
      DROP USER 'msandbox'@'localhost';
      DELETE FROM `mysql`.`user` WHERE `User`='msandbox' AND `Host`='localhost';
      -- 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`;
      

      You can see this:

      CREATE ROLE IF NOT EXISTS `R_DO_IT_ALL`;
      

      and then immediately this:

      CREATE USER IF NOT EXISTS 'R_DO_IT_ALL'@'%';
      

      This second one is a noop since the first one already creates that user:

      mysql> CREATE ROLE IF NOT EXISTS `R_TEST`;
      Query OK, 0 rows affected (0,03 sec)
      
      mysql> select User,Host,account_locked,plugin from mysql.user where User like '%R_TEST%';
      +--------+------+----------------+-----------------------+
      | User   | Host | account_locked | plugin                |
      +--------+------+----------------+-----------------------+
      | R_TEST | %    | Y              | mysql_native_password |
      +--------+------+----------------+-----------------------+
      1 row in set (0,00 sec)
      
      mysql> CREATE USER IF NOT EXISTS 'R_TEST'@'%';                                                                                                               Query OK, 0 rows affected, 1 warning (0,01 sec)
      
      mysql> show warnings;
      +-------+------+-----------------------------------------------+
      | Level | Code | Message                                       |
      +-------+------+-----------------------------------------------+
      | Note  | 3163 | Authorization ID 'R_TEST'@'%' already exists. |
      +-------+------+-----------------------------------------------+
      1 row in set (0,00 sec)
      
      mysql> select User,Host,account_locked,plugin from mysql.user where User like '%R_TEST%';
      +--------+------+----------------+-----------------------+
      | User   | Host | account_locked | plugin                |
      +--------+------+----------------+-----------------------+
      | R_TEST | %    | Y              | mysql_native_password |
      +--------+------+----------------+-----------------------+
      1 row in set (0,00 sec)
      

      I noticed we don't do "drop" for roles (IIRC it's by design) so maybe don't do create user for the role also, just create role.

        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