1

MySQL 8.0.34

Limited-privilege users see less verbose errors (1216/1217) per the design intent. I've granted reference privileges, as suggested here, in an attempt to show more verbose errors (1451/1452) for all users. But mybasic user still sees the less verbose versions.

Difference in user privileges:

mysql> show grants for 'myadmin';
+--------------------------------------------------------------+
| Grants for myadmin@%                                         |
+--------------------------------------------------------------+
| GRANT USAGE ON *.* TO `myadmin`@`%`                          |
| GRANT ALL PRIVILEGES ON `%`.* TO `myadmin`@`%`               |
| GRANT ALL PRIVILEGES ON `common\_%`.* TO `myadmin`@`%`       |
+--------------------------------------------------------------+

mysql> show grants for 'mybasic';
+--------------------------------------------------------------+
| Grants for mybasic@%                                         |
+--------------------------------------------------------------+
| GRANT REFERENCES ON *.* TO `mybasic`@`%`                     |
| GRANT SELECT, REFERENCES ON `%`.* TO `mybasic`@`%`           |
| GRANT ALL PRIVILEGES ON `common\_%`.* TO `mybasic`@`%`       |
+--------------------------------------------------------------+

I then tried to run the following delete from each account:

DELETE FROM `common_one`.`one` WHERE ( (`field`="value"));

On myadmin, the result is as expected. I've followed this trail, and it seems like all dependencies are within the common database.

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`common_two`.`_two`, CONSTRAINT `_two_ibfk_1` FOREIGN KEY (`field`) REFERENCES `common_one`.`one` (`field`) ON DELETE RESTRICT ON UPDATE CASCADE)

On mybasic, the result is the less verbose version:

ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

What changes can I make to the mybasic user to see the more verbose errors? Are there settings I can adjust or logs I can use to inspect the problem? The Python interface my team uses, DataJoint, relies on the verbose version to suggest cascading deletes, so this has been a major hurdle for us.

EDIT: GRANT ALL PRIVILEGES ON `%`.* TO `mybasic`@`%`; leads to the desired error, but reduces security in a way I'd like to avoid

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.