Bug #99336 Don't get error 1451/1452 if user does not have full DDL permissions
Submitted: 23 Apr 2020 15:11 Modified: 24 Apr 2020 13:59
Reporter: Graham Clifford Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:8.0.19 OS:Ubuntu (18.04.4 LTS)
Assigned to: CPU Architecture:x86
Tags: error, permission

[23 Apr 2020 15:11] Graham Clifford
Description:
If the user does not have full DDL permissions you get the error code 1216/1217 instead of 1451/1452.

This also happens in my AWS RDS instance running MySQL 8.0.17

How to repeat:
1) Have a user with GRANT ALL PRIVILEGES
'GRANT ALL PRIVILEGES ON `DB`.* TO `USER`@`%`'

2) Perform a delete that would result in a 1451/1452 error

3) You will get the 1451/1452 error code

4) Remove DDL permissions
REVOKE 
    CREATE,
    ALTER,
    REFERENCES,
    INDEX,
    CREATE VIEW,
    CREATE ROUTINE,
    ALTER ROUTINE,
    EVENT,
    DROP,
    TRIGGER
ON DB.* FROM 'USER'@'%';

FLUSH PRIVILEGES;

5) Perform the same delete operation as in step 2

6) You will get 1216/1217 error instead
[24 Apr 2020 13:01] MySQL Verification Team
Hi Mr. Clifford,

Thank you for your bug report.

However, this is not a bug. Let me explain ...

You are not doing any DDL operation, but DML operation.

Now what is important is that one error applies to the DML on the child table, while the other pertains to the parent table.

The only relevant privilege that was removed with the REVOKE command is the REFERENCES privilege. Hence, that user is not allowed any more to touch the parent table, with foreign key relationship.

This explains fully the change in error number.

Not a bug.
[24 Apr 2020 13:45] Graham Clifford
I would not of thought a DELETE FROM would not return a DDL error, since DELETE FROM is DML.

Now if I give my user the REFERENCE privilege, it still returns 1217. From what I can tell if I remove any DDL privilege it will downgrade the error. It seems to be an all or nothing.

The main reason I ask is that when a 1216/1217 error occurs the error message is very sparse any detail information while 1451/1452 has the constraint name, etc.

I was using that information to display nicer error messages to our users. But since I have locked down the DB accounts i no longer get it the needed information.
[24 Apr 2020 13:54] MySQL Verification Team
It is intentional behaviour.

Since user does not have the necessary privileges, he is not allowed to know parent / child relationship.
[24 Apr 2020 13:59] Graham Clifford
OK.

Thank you for the help.
[24 Apr 2020 14:02] MySQL Verification Team
You are welcome.
[5 Feb 21:38] Chris Brozdowski
With the following in mind:

> The only relevant privilege that was removed with the REVOKE command is the REFERENCES privilege. Hence, that user is not allowed any more to touch the parent table, with foreign key relationship.

I granted my 'basic' user references privilege to all databases. Should this have resulted in the more verbose error 1451/1452? What is the minimum grant required for me more verbose error? 

If I run the same delete command with an 'admin' user with all privileges, the more verbose error points to a database for which the basic user has all privileges, so I'm not sure what is causing the less verbose error.
[6 Feb 10:57] MySQL Verification Team
Hi Mr. Brozdowski,

First of all, please use 8.0.36 or 8.3.0, because this is an old bug for an old release.

Second, your user has to have privileges that are sufficient for the SQL statement , so that the error returned is not just about missing privileges.

Third and most important, returning fully verbose error messages to anyone who tries to run the SQL statement, for which that user has no privileges, would be a hard security bug. Hence, non-verbose error messages are , in the above cases, obligatory.
[6 Feb 17:39] Chris Brozdowski
Thank you for your prompt response. Our release is 8.0.34. I will suggest upgrading to the team. I'm happy to open a new thread if this isn't the appropriate place for this discussion.

I understand the possible security implications and do not expect it to operate otherwise for no-privilege users. Is it possible for a user to see the verbose error with read-only access? Or, are create/delete permissions required? 

My team operates with shared databases on which individuals' databases depend. If user A tries to delete shared item X, I would like for them to see that there is a foreign key dependency Y owned by user B in order to discuss changes to X with user B. How can I grant user A read access to Y? To my understanding, low-verbosity errors allow me prevent disclosing the existence of Y to user A, but this is not a concern in my case.

Thank you for your time