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

pt-show-grants support for MySQL 8.0

    XMLWordPrintable

    Details

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

      Description

      There's at least two issues here if not more.
      Roles doc page: https://dev.mysql.com/doc/refman/8.0/en/roles.html
      One of the interesting bits:

      User and Role Interchangeability
      As has been hinted at earlier for SHOW GRANTS, which displays grants for user accounts or roles, accounts and roles can be used interchangeably. You can treat a user account like a role and grant that account to another user or a role. The effect is to grant the account's privileges and roles to the other user or role.
      

      Two new tables are present in mysql database:

      - default_roles: This table lists default roles to be activated after a user connects and authenticates, or executes SET ROLE DEFAULT.
      
      - role_edges: This table lists edges for role subgraphs.
      A given user table row might refer to a user account or a role. The server can distinquish whether a row represents a user account, a role, or both by consulting the role_edges table for information about relations between authentication IDs.
      
      CREATE ROLE 'app_developer', 'app_read', 'app_write', 'tester';
      
      GRANT ALL ON test.* TO 'app_developer';
      GRANT SELECT ON test.* TO 'app_read';
      GRANT SELECT ON test.* TO 'tester';
      GRANT INSERT, UPDATE, DELETE ON test.* TO 'app_write';
      
      CREATE USER 'plavi'@'localhost' IDENTIFIED WITH 'mysql_native_password' BY 'test1234';
      
      GRANT 'app_read','app_write' TO 'plavi'@'localhost';
      GRANT 'tester' TO 'plavi'@'localhost' WITH ADMIN OPTION;

      Content of my tables for user plavi (for #1 and #2, for #3 it was changed):

      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 | plavi   | N                 |
      | %         | app_read  | localhost | root    | N                 |
      | %         | app_write | localhost | plavi   | N                 |
      | %         | tester    | localhost | plavi   | Y                 |
      +-----------+-----------+-----------+---------+-------------------+
      5 rows in set (0,00 sec)
      
      8.0.4>select * from default_roles;
      +-----------+-------+-------------------+-------------------+
      | HOST      | USER  | DEFAULT_ROLE_HOST | DEFAULT_ROLE_USER |
      +-----------+-------+-------------------+-------------------+
      | localhost | plavi | %                 | app_read          |
      | localhost | plavi | %                 | app_write         |
      | localhost | plavi | %                 | tester            |
      +-----------+-------+-------------------+-------------------+
      3 rows in set (0,00 sec)
      
      8.0.4>show grants for 'plavi'@'localhost'\G;
      *************************** 1. row ***************************
      Grants for plavi@localhost: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `plavi`@`localhost` WITH GRANT OPTION
      *************************** 2. row ***************************
      Grants for plavi@localhost: 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 `plavi`@`localhost` WITH GRANT OPTION
      *************************** 3. row ***************************
      Grants for plavi@localhost: GRANT `app_read`@`%`,`app_write`@`%` TO `plavi`@`localhost`
      *************************** 4. row ***************************
      Grants for plavi@localhost: GRANT `tester`@`%` TO `plavi`@`localhost` WITH ADMIN OPTION
      4 rows in set (0,00 sec)
      

      Issues:
      #1: show grants for user with drop and then try to load again (check pt-show-grants-3.txt)

      //plavi@bender  bin   mysql-8  ./pt-show-grants --drop --flush --socket=/home/plavi/test/mysql/bin/mysql-8.0.4-rc-linux-glibc2.12-x86_64/socket.sock --user=plavi --password=test1234 --only=plavi > pt-show-grants-3.txt 2>&1
      
      plavi@bender  mysql-8.0.4-rc-linux-glibc2.12-x86_64  /home/plavi/test/mysql/bin/mysql-8.0.4-rc-linux-glibc2.12-x86_64-new/bin/mysql -A -uroot -S/home/plavi/test/mysql/bin/mysql-8.0.4-rc-linux-glibc2.12-x86_64/socket.sock < pt-show-grants-3.txt
      ERROR 1064 (42000) at line 7: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DEFAULT ROLE `app_read`@`%`,`app_write`@`%`,`tester`@`%` REQUIRE NONE PASSWORD E' at line 1
      

      #2: As roles are treated as users but now we have a relationship between roles or users if I do a dump and try to reload I get unknown authorization because the user is created first and then a role - this is in a case where I want to dump a user on one server and create the same on another. (check pt-show-grants-2.txt)

      ✘ plavi@bender  mysql-8.0.4-rc-linux-glibc2.12-x86_64-new  /home/plavi/test/mysql/bin/mysql-8.0.4-rc-linux-glibc2.12-x86_64-new/bin/mysql -A -uroot -S/hom
      e/plavi/test/mysql/bin/mysql-8.0.4-rc-linux-glibc2.12-x86_64-new/socket.sock < pt-show-grants-2.txt
      ERROR 3523 (HY000) at line 59: Unknown authorization ID `tester`@`%`
      

      #3: mandatory roles are not visible (check pt-show-grants-4.txt), although this can be discussed if it's needed or not

      8.0.4>show grants for 'plavi'@'localhost' using 'app_read'\G;
      *************************** 1. row ***************************
      Grants for plavi@localhost: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `plavi`@`localhost` WITH GRANT OPTION
      *************************** 2. row ***************************
      Grants for plavi@localhost: 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 `plavi`@`localhost` WITH GRANT OPTION
      *************************** 3. row ***************************
      Grants for plavi@localhost: GRANT SELECT ON `test`.* TO `plavi`@`localhost`
      3 rows in set (0,00 sec)
      
      8.0.4>show variables like 'mandatory_roles';
      +-----------------+----------+
      | Variable_name   | Value    |
      +-----------------+----------+
      | mandatory_roles | app_read |
      +-----------------+----------+
      1 row 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                 |
      +-----------+-----------+-----------+---------+-------------------+
      2 rows in set (0,00 sec)
      
      8.0.4>select * from default_roles;
      Empty set (0,00 sec)
      
      mysql> select current_role();
      +----------------+
      | current_role() |
      +----------------+
      | `app_read`@`%` |
      +----------------+
      1 row in set (0,00 sec)
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                carlos.salguero Carlos Salguero
                Reporter:
                tomislav.plavcic@percona.com Tomislav Plavcic
              • Votes:
                0 Vote for this issue
                Watchers:
                2 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 - 2 days, 1 hour, 32 minutes
                  2d 1h 32m