Permissions for CREATE TEMPORARY TABLE are incorrectly global-only scoped

[morgo]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

DROP USER IF EXISTS u1;
CREATE USER u1;
GRANT CREATE TEMPORARY TABLES on test.* to u1;
GRANT CREATE TEMPORARY TABLES on *.* to u1;

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

mysql [localhost:8024] {root} ((none)) > DROP USER IF EXISTS u1;
Query OK, 0 rows affected (0.00 sec)

mysql [localhost:8024] {root} ((none)) > CREATE USER u1;
Query OK, 0 rows affected (0.00 sec)

mysql [localhost:8024] {root} ((none)) > GRANT CREATE TEMPORARY TABLES on test.* to u1;
Query OK, 0 rows affected (0.00 sec)

mysql [localhost:8024] {root} ((none)) > GRANT CREATE TEMPORARY TABLES on *.* to u1;
Query OK, 0 rows affected (0.00 sec)

3. What did you see instead (Required)

mysql> CREATE USER u1;
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT CREATE TEMPORARY TABLES on test.* to u1;
ERROR 1221 (HY000): Incorrect usage of DB GRANT and NON-DB PRIVILEGES
mysql> GRANT CREATE TEMPORARY TABLES on *.* to u1;
Query OK, 0 rows affected (0.01 sec)

4. What is your TiDB version? (Required)

mysql> SELECT tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v5.3.0-alpha-256-gb0b559f23
Edition: Community
Git Commit Hash: b0b559f2389de5e27d0d2e12d2bfae049186b54b
Git Branch: master
UTC Build Time: 2021-10-01 14:52:33
GoVersion: go1.16.7
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
1 row in set (0.00 sec)

[morgo]

This is similar to #26703 and #27290

Please see their PRs for how to fix :-)

[morgo]

The following is also broken. We don't support routines or events, but users might migrate accounts that have this permission and the GRANT will fail:

mysql> grant event on test.* to u1; # should by DB scoped
ERROR 1221 (HY000): Incorrect usage of DB GRANT and NON-DB PRIVILEGES

mysql> grant alter routine on test.* to u1;
ERROR 1221 (HY000): Incorrect usage of DB GRANT and NON-DB PRIVILEGES

mysql> grant create routine on test.* to u1;
ERROR 1221 (HY000): Incorrect usage of DB GRANT and NON-DB PRIVILEGES

[TszKitLo40]

/assign

[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.