pt-show-grants support for MySQL 8.0

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.

- 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;

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)

//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

✘ 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`@`%`

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)