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

Improve MySQL 8 support in pt-show-grants

    XMLWordPrintable

Details

    • Improvement
    • Status: Done
    • Medium
    • Resolution: Fixed
    • None
    • 3.0.10
    • None
    • 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;

      Attachments

        Issue Links

          created by

          Bug - A problem which impairs or prevents the functions of the product. PT-1488 pt-show-grants support for MySQL 8.0

          • Critical - Severe issues that render the system unusable or cause data loss, demanding an immediate response.
          • Done

          Activity

            People

              carlos.salguero Carlos Salguero (Inactive)
              carlos.salguero Carlos Salguero (Inactive)
              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

                  Smart Checklist