Permissions required for ER_NO_REFERENCED_ROW_2. Key violation messages
I am currently porting a project to mysql 8.0 from mysql 5.7. This project takes advantage of extracting the table/column name information in ERR_NO_REFERENCED_ROW_2 - e.g. ERROR 1451 (23000) at line 1: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`joinit`, CONSTRAINT `joinit_ibfk_1` FOREIGN KEY (`g`) REFERENCES `joinit_fk` (`i`) ON DELETE RESTRICT ON UPDATE CASCADE)
WL#8910 changed the behaviour in Mysql8 to tighten up security and require permissions on the relevant tables. This perfectly makes sense so far.
It seems that CREATE/ALTER/DROP + more are required.
On the system I am working on, there are essentially 2 users - which I am guessing is relatively common basic setup. An "admin" user has permission for to issue DDL (and DML) operations, to manage the schema and data. The day to day application accesses with only "DML" operations (INSERT/UPDATE/DELETE) for the schema. There are no specific table or column level permissions for either user. The basic user account, is able to run "SHOW CREATE TABLE..." on both tables to dump out the full DDL with constraints anyway.
I am wondering if someone could explain to me, why the permissions required are so high? I am presuming there is a good reason that I am not grasping. This was already logged as a bug by someone else,
https://bugs.mysql.com/bug.php?id=112589 - however was ultimately closed as a documentation problem.
Thanks for any pointers you can give as to the reason.