Aurora 3 not recognising user GRANT permissions on schema with escape characters

Question

I've recently upgraded my database from mysql5.7 to mysql8.0.26 which also incorporated an upgrade from Aurora 2 to Aurora 3 which is where I think the issue lies as there are no references to the following issue in the mySQL documentation.

During the upgrade the myql.user table is copied to the new database (using a Blue/Green deployment). GRANT permissions are also copied across and all the details remain unchanged as I have verified with `SHOW GRANTS` on both the blue and green environments. However, my users in the mysql8 DB do not have access to the resources identified in the GRANTS where those resources contain an underscore.

To reproduce the issue:
1. Create an Aurora 3 DB instance running MySQL 8.0.26
2. CREATE SCHEMA new_schema ;
3. CREATE TABLE new_schema.test_table (id int);
4. GRANT ALL PRIVILEGES ON `new\_schema`.* TO `my-user`@`%`

Run `SHOW GRANTS for my-user;` and you will see the following response:

```
GRANT ALL PRIVILEGES ON `new\_schema`.* TO `my-user`@`%`
```

That is the expected response, and mySQL documentation advises keeping the escape character in place to avoid any issues related to partial_revokes (which I can confirm is OFF by default).

Now the problem is, in Aurora 3, if I log in with `my-user` and run `SELECT * FROM new_schema.test_table` I get a permission denied error.

I have investigated a similar sounding issue in re:post [here](https://repost.aws/questions/QUeD79WVUQT4WJ9bpMNJ7hfg/aurora-3-my-sql-the-value-of-the-partial-revokes-parameter-in-the-parameter-group-is-different-from-the-value-of-the-same-variable-in-my-sql) however, I have already confirmed that `partial_revokes` as set to 0 (disabled) both in my database parameter groups and if I query it directly in mysql.

Answer

Hello,

Thank you for sharing detailed information for this issue.

Though unfortunately I had attempted to reproduce this issue by following the listed steps and had been unable to do so.

I first created a new Aurora 3.03 cluster (compatible with MySQL 8.0.26).
I then connected to the writer instance with the default master user.
With the master user I performed the following:

```
CREATE SCHEMA new_schema;
	- 1 row(s) affected

CREATE TABLE new_schema.test_table (id int);
	- 0 row(s) affected

CREATE USER `my-user`@`%` IDENTIFIED BY 'password';
	- 0 row(s) affected

GRANT ALL PRIVILEGES ON `new\_schema`.* TO `my-user`@`%`;
	- 0 row(s) affected

SHOW GRANTS for `my-user`;
	- 2 row(s) returned
	  
	GRANT USAGE ON *.* TO `my-user`@`%`
        GRANT ALL PRIVILEGES ON `new\_schema`.* TO `my-user`@`%`
```

I then connected to the instance with `my-user` and performed the following:

```
SELECT * FROM new_schema.test_table;
	- 0 row(s) returned
```

As I had not inserted anything to the `test_table` no rows were returned but I had not encountered the permission denied error when attempting to select from the table.

Aurora 3 not recognising user GRANT permissions on schema with escape characters

Relevant content