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

Improve MySQL 8 support in pt-show-grants

    XMLWordPrintable

    Details

    • Type: Improvement
    • Status: Done
    • Priority: Medium
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 3.0.10
    • Component/s: None
    • Labels:
      None

      Description

      There seem to be some issues with the current implementation.
      I have a situation like this:

      8.0.4>show grants for developer@'%';
      +---------------------------------------+
      | Grants for developer@%                |
      +---------------------------------------+
      | GRANT USAGE ON *.* TO `developer`@`%` |
      | GRANT `tester`@`%` TO `developer`@`%` |
      +---------------------------------------+
      2 rows in set (0,00 sec)
      
      8.0.4>select * from role_edges;
      +-----------+-----------+-----------+-----------+-------------------+
      | FROM_HOST | FROM_USER | TO_HOST   | TO_USER   | WITH_ADMIN_OPTION |
      +-----------+-----------+-----------+-----------+-------------------+
      | %         | app_read  | localhost | dev1      | N                 |
      | %         | app_read  | localhost | root      | N                 |
      | %         | tester    | %         | developer | N                 |
      | %         | tester    | localhost | plavi     | N                 |
      +-----------+-----------+-----------+-----------+-------------------+
      4 rows in set (0,00 sec)
      
      8.0.4>show grants for tester@'%';
      +------------------------------------------+
      | Grants for tester@%                      |
      +------------------------------------------+
      | GRANT USAGE ON *.* TO `tester`@`%`       |
      | GRANT INSERT ON `test`.* TO `tester`@`%` |
      +------------------------------------------+
      2 rows in set (0,00 sec)
      

      Basically I want to concentrate here on the "developer" account which has "tester" role.

      ============
      TESTCASE #1:
      ============
      If I select only specific user I get his grants but not with the grants from his roles and pt-show-grants also creats empty roles which are not associated to this specific account.

      ./pt-show-grants --socket=/home/plavi/test/mysql/bin/mysql-8.0.4-rc-linux-glibc2.12-x86_64/socket.sock --user=plavi --password=test1234 --only=developer
      
      8.0.4>select Host,User from mysql.user;
      +-----------+------------------+
      | Host      | User             |
      +-----------+------------------+
      | %         | app_developer    |
      | %         | app_read         |
      | %         | app_write        |
      | %         | developer        |
      | %         | tester           |
      | localhost | mysql.infoschema |
      | localhost | mysql.session    |
      | localhost | mysql.sys        |
      | localhost | plavi            |
      | localhost | root             |
      +-----------+------------------+
      10 rows in set (0,00 sec)
      
      8.0.4>select * from mysql.role_edges;
      +-----------+-----------+---------+-----------+-------------------+
      | FROM_HOST | FROM_USER | TO_HOST | TO_USER   | WITH_ADMIN_OPTION |
      +-----------+-----------+---------+-----------+-------------------+
      | %         | tester    | %       | developer | N                 |
      +-----------+-----------+---------+-----------+-------------------+
      1 row in set (0,00 sec)
      
      8.0.4>show grants for developer;
      +---------------------------------------+
      | Grants for developer@%                |
      +---------------------------------------+
      | GRANT USAGE ON *.* TO `developer`@`%` |
      | GRANT `tester`@`%` TO `developer`@`%` |
      +---------------------------------------+
      2 rows in set (0,00 sec)
      
      8.0.4>show grants for tester;
      +------------------------------------+
      | Grants for tester@%                |
      +------------------------------------+
      | GRANT USAGE ON *.* TO `tester`@`%` |
      +------------------------------------+
      1 row in set (0,00 sec)
      

      So as you can see "GRANT INSERT ON `test`.* TO `tester`@`%`" is missing since it did not pickup the grants from tester role which is assigned to developer.

      pt-show-grants output:

      -- Grants dumped by pt-show-grants
      -- Dumped from server Localhost via UNIX socket, MySQL 8.0.4-rc-log at 2018-04-19 19:38:10
      -- Roles
      CREATE ROLE IF NOT EXISTS `app_read`;
      CREATE ROLE IF NOT EXISTS `tester`;
      CREATE ROLE IF NOT EXISTS `app_developer`;
      CREATE ROLE IF NOT EXISTS `app_write`;
      -- End of roles listing
      -- Grants for 'developer'@'%'
      CREATE USER IF NOT EXISTS 'developer'@'%';
      ALTER USER 'developer'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*3D3B92F242033365AE5BC6A8E6FC3E1679F4140A' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT;
      GRANT USAGE ON *.* TO `developer`@`%`;
      GRANT `tester`@`%` TO `developer`@`%`;
      

      Also why is it creating roles which are not associated with "developer" account?

      ============
      TESTCASE #2:
      ============
      If I list a user and all his roles in --only=user,role1,role2 format I can get a good copy of grants, here's how it looks:

      ./pt-show-grants --socket=/home/plavi/test/mysql/bin/mysql-8.0.4-rc-linux-glibc2.12-x86_64/socket.sock --user=plavi --password=test1234 --only=developer,tester
      
      8.0.4>select Host,User from mysql.user;
      +-----------+------------------+
      | Host      | User             |
      +-----------+------------------+
      | %         | app_developer    |
      | %         | app_read         |
      | %         | app_write        |
      | %         | developer        |
      | %         | tester           |
      | localhost | mysql.infoschema |
      | localhost | mysql.session    |
      | localhost | mysql.sys        |
      | localhost | plavi            |
      | localhost | root             |
      +-----------+------------------+
      10 rows in set (0,00 sec)
      
      8.0.4>select * from mysql.role_edges;
      +-----------+-----------+---------+-----------+-------------------+
      | FROM_HOST | FROM_USER | TO_HOST | TO_USER   | WITH_ADMIN_OPTION |
      +-----------+-----------+---------+-----------+-------------------+
      | %         | tester    | %       | developer | N                 |
      +-----------+-----------+---------+-----------+-------------------+
      1 row in set (0,00 sec)
      
      8.0.4>show grants for developer;
      +---------------------------------------+
      | Grants for developer@%                |
      +---------------------------------------+
      | GRANT USAGE ON *.* TO `developer`@`%` |
      | GRANT `tester`@`%` TO `developer`@`%` |
      +---------------------------------------+
      2 rows in set (0,00 sec)
      
      8.0.4>show grants for tester;
      +------------------------------------------+
      | Grants for tester@%                      |
      +------------------------------------------+
      | GRANT USAGE ON *.* TO `tester`@`%`       |
      | GRANT INSERT ON `test`.* TO `tester`@`%` |
      +------------------------------------------+
      2 rows in set (0,00 sec)
      

      pt-show-grants output for this:

      -- Grants dumped by pt-show-grants
      -- Dumped from server Localhost via UNIX socket, MySQL 8.0.4-rc-log at 2018-04-19 19:38:18
      -- Roles
      CREATE ROLE IF NOT EXISTS `app_read`;
      CREATE ROLE IF NOT EXISTS `tester`;
      CREATE ROLE IF NOT EXISTS `app_developer`;
      CREATE ROLE IF NOT EXISTS `app_write`;
      -- End of roles listing
      -- Grants for 'developer'@'%'
      CREATE USER IF NOT EXISTS 'developer'@'%';
      ALTER USER 'developer'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*3D3B92F242033365AE5BC6A8E6FC3E1679F4140A' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT;
      GRANT USAGE ON *.* TO `developer`@`%`;
      GRANT `tester`@`%` TO `developer`@`%`;
      -- Grants for 'tester'@'%'
      CREATE USER IF NOT EXISTS 'tester'@'%';
      ALTER USER 'tester'@'%' IDENTIFIED WITH 'caching_sha2_password' REQUIRE NONE PASSWORD EXPIRE ACCOUNT LOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT;
      GRANT INSERT ON `test`.* TO `tester`@`%`;
      GRANT USAGE ON *.* TO `tester`@`%`;
      

      ============
      TESTCASE #3:
      ============
      If I list a user and roles in --only=user,role1,role2 format but use --drop statement then it also doesn't look good.

      ./pt-show-grants --socket=/home/plavi/test/mysql/bin/mysql-8.0.4-rc-linux-glibc2.12-x86_64/socket.sock --user=plavi --password=test1234 --only=developer,tester --drop
      
      8.0.4>select * from mysql.role_edges;
      Empty set (0,00 sec)
      
      8.0.4>select Host,User from mysql.user;
      +-----------+------------------+
      | Host      | User             |
      +-----------+------------------+
      | %         | app_developer    |
      | %         | app_read         |
      | %         | app_write        |
      | %         | developer        |
      | %         | tester           |
      | localhost | mysql.infoschema |
      | localhost | mysql.session    |
      | localhost | mysql.sys        |
      | localhost | plavi            |
      | localhost | root             |
      +-----------+------------------+
      10 rows in set (0,00 sec)
      
      8.0.4>show grants for tester;
      +------------------------------------------+
      | Grants for tester@%                      |
      +------------------------------------------+
      | GRANT USAGE ON *.* TO `tester`@`%`       |
      | GRANT INSERT ON `test`.* TO `tester`@`%` |
      +------------------------------------------+
      2 rows in set (0,00 sec)
      
      8.0.4>show grants for developer;
      +---------------------------------------+
      | Grants for developer@%                |
      +---------------------------------------+
      | GRANT USAGE ON *.* TO `developer`@`%` |
      +---------------------------------------+
      1 row in set (0,00 sec)
      

      Notice that the "mysql.role_edges" table is empty now which is not the case in original and the tester role exists but it is not assigned to developer account.

      pt-show-grants output:

      -- Grants dumped by pt-show-grants
      -- Dumped from server Localhost via UNIX socket, MySQL 8.0.4-rc-log at 2018-04-19 16:49:28
      -- Roles
      CREATE ROLE IF NOT EXISTS `app_read`;
      CREATE ROLE IF NOT EXISTS `tester`;
      CREATE ROLE IF NOT EXISTS `app_developer`;
      CREATE ROLE IF NOT EXISTS `app_write`;
      -- End of roles listing
      DROP USER 'developer'@'%';
      DELETE FROM `mysql`.`user` WHERE `User`='developer' AND `Host`='%';
      -- Grants for 'developer'@'%'
      CREATE USER IF NOT EXISTS 'developer'@'%';
      ALTER USER 'developer'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*3D3B92F242033365AE5BC6A8E6FC3E1679F4140A' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT;
      GRANT USAGE ON *.* TO `developer`@`%`;
      GRANT `tester`@`%` TO `developer`@`%`;
      DROP USER 'tester'@'%';
      DELETE FROM `mysql`.`user` WHERE `User`='tester' AND `Host`='%';
      -- Grants for 'tester'@'%'
      CREATE USER IF NOT EXISTS 'tester'@'%';
      ALTER USER 'tester'@'%' IDENTIFIED WITH 'caching_sha2_password' REQUIRE NONE PASSWORD EXPIRE ACCOUNT LOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT;
      GRANT INSERT ON `test`.* TO `tester`@`%`;
      GRANT USAGE ON *.* TO `tester`@`%`;
      FLUSH PRIVILEGES;

        Smart Checklist

          Attachments

            Issue Links

              Activity

                People

                • Assignee:
                  carlos.salguero Carlos Salguero
                  Reporter:
                  carlos.salguero Carlos Salguero
                • Votes:
                  0 Vote for this issue
                  Watchers:
                  1 Start watching this issue

                  Dates

                  • Created:
                    Updated:
                    Resolved:

                    Time Tracking

                    Estimated:
                    Original Estimate - Not Specified
                    Not Specified
                    Remaining:
                    Remaining Estimate - 0 minutes
                    0m
                    Logged:
                    Time Spent - 1 day
                    1d