Show grants using more than two roles return incorrect result

[xuanyu66]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

CREATE DATABASE IF NOT EXISTS tispark_test;
CREATE TABLE IF NOT EXISTS tispark_test.table_test(i int, s varchar(128));
CREATE ROLE IF NOT EXISTS 'test_read', 'test_write';
GRANT SELECT ON tispark_test.table_test TO 'test_read'@'%';
GRANT UPDATE ON tispark_test.table_test TO 'test_write'@'%';
CREATE USER IF NOT EXISTS 'tispark_unit_test_user' IDENTIFIED BY '';
GRANT 'test_read','test_write' TO 'tispark_unit_test_user'@'%';
SHOW GRANTS FOR 'tispark_unit_test_user'@'%' USING 'test_read','test_write';

2. What did you expect to see? (Required)

MySQL version： 8.0.27

3. What did you see instead (Required)

Actually, if I use command SET ROLE 'test_read', 'test_write', it still can select rows from table tispark_test.table_test. So I think it's a bug for SHOW GRANTS

4. What is your TiDB version? (Required)

TiDB version：5.3.0

[sunxiaoguang]

cc @pingcap/tidb-committers

[wjhuang2016]

@morgo PTAL

[morgo]

I can take a look next year when I get back from vacation if this is still open :-) I have found SHOW GRANTS compatibility very hard, but it is important to get right because users make decisions based on this.

[github-actions]

Please check whether the issue should be labeled with 'affects-x.y' or 'fixes-x.y.z', and then remove 'needs-more-info' label.