Description:
Hi Mysql team,
I'd like to report a bug that I found when running statement 'SHOW GRANTS FOR USER' in sql mode ANSI_QUOTES. The bug exists in Mysql > 8.0, but not in 5.7.
The bug is: When enable sql_mode ANSI_QUOTES, 'SHOW GRANTS' quotes Function and Stored procedure's name in backticks, while the other objects in double quotes.
How to repeat:
mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.36 |
+-----------+
1 row in set (0.00 sec)
mysql> use test_db
Database changed
mysql> grant execute on function fc_test to test;
Query OK, 0 rows affected (0.00 sec)
mysql> grant execute on procedure sp_test to test;
Query OK, 0 rows affected (0.00 sec)
mysql> grant select on t1 to test;
Query OK, 0 rows affected (0.01 sec)
mysql> grant all on test_db.t1 to test;
Query OK, 0 rows affected (0.01 sec)
## inconsistency quotation in mode ANSI_QUOTES
mysql> set sql_mode='ANSI_QUOTES';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for test;
+--------------------------------------------------------------+
| Grants for test@% |
+--------------------------------------------------------------+
| GRANT USAGE ON *.* TO "test"@"%" |
| GRANT ALL PRIVILEGES ON "test_db"."t1" TO "test"@"%" |
| GRANT EXECUTE ON PROCEDURE `test_db`.`sp_test` TO "test"@"%" |
| GRANT EXECUTE ON FUNCTION `test_db`.`fc_test` TO "test"@"%" |
+--------------------------------------------------------------+
4 rows in set (0.00 sec)
## but all good if ANSI_QUOTES is disabled
mysql> set sql_mode='' ;
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for test;
+--------------------------------------------------------------+
| Grants for test@% |
+--------------------------------------------------------------+
| GRANT USAGE ON *.* TO `test`@`%` |
| GRANT ALL PRIVILEGES ON `test_db`.`t1` TO `test`@`%` |
| GRANT EXECUTE ON PROCEDURE `test_db`.`sp_test` TO `test`@`%` |
| GRANT EXECUTE ON FUNCTION `test_db`.`fc_test` TO `test`@`%` |
+--------------------------------------------------------------+
4 rows in set (0.00 sec)
