※試してませんが以前のようにGRANTでいきなりユーザ作るってことができなくなっているみたいなのでまずはCREATE USERを使ってユーザ作成してください
バージョン
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.23 |
+-----------+
発生エラー
パターン１
grant all on *.* to 'audit'@'%';
ERROR 1045 (28000): Access denied for user 'admin'@'%' (using password: YES)
パターン２
grant all on `%`.* to 'audit'@'%' identified by 'password';
ERROR 1064 (42000): 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 'identified by 'password' with grant option' at line 1
原因&解決策
本バージョンでGRANT ALL *.*ができないから
対処
ルートユーザの権限を確認
mysql> show grants for 'admin'@`%`;
+-----------------------------------------------+
| Grants for admin@% |
+-----------------------------------------------+
| GRANT USAGE ON *.* TO `admin`@`%` |
| GRANT `rds_superuser_role`@`%` TO `admin`@`%` |
+-----------------------------------------------+
2 rows in set (0.00 sec)
ロールの中身を確認
mysql> show grants for 'admin'@`%` using rds_superuser_role;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for admin@% |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPL ICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE ROLE, DROP ROLE ON *.* TO `admin`@`%`WITH GRANT OPTION |
| GRANT APPLICATION_PASSWORD_ADMIN,CONNECTION_ADMIN,REPLICATION_APPLIER,ROLE_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,XA_RECOVER_ADMIN ON *.* TO `admin`@`%` WITH GRANT OPTION |
| GRANT `rds_superuser_role`@`%` TO `admin`@`%` |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.03 sec)
好きなユーザーに権限付与（監査人大暴走可）
mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE ROLE, DROP ROLE ON *.* TO `audit`@`%` WITH GRANT OPTION;
Query OK, 0 rows affected (0.05 sec)