Bug #96252 | ER_NO_REFERENCED_ROW returned while user has database level permissions | ||
---|---|---|---|
Submitted: | 19 Jul 2019 4:26 | Modified: | 19 Jul 2019 14:37 |
Reporter: | John Ott | Email Updates: | |
Status: | Not a Bug | Impact on me: | |
Category: | MySQL Server | Severity: | S3 (Non-critical) |
Version: | 8.0.16 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[19 Jul 2019 4:26]
John Ott
[19 Jul 2019 4:27]
John Ott
Create Statements
Attachment: test_create.sql (application/octet-stream, text), 202 bytes.
[19 Jul 2019 4:27]
John Ott
Test File
Attachment: test.sql (application/octet-stream, text), 109 bytes.
[19 Jul 2019 4:27]
John Ott
Grant Permissions Statements
Attachment: add_permission.sql (application/octet-stream, text), 51 bytes.
[19 Jul 2019 4:43]
John Ott
See: Commit: https://github.com/mysql/mysql-server/commit/f26bc31bd462278866430b6b37c2b02bd2db34d0#diff... Worklog: https://dev.mysql.com/worklog/task/?id=8910
[19 Jul 2019 12:35]
MySQL Verification Team
Hi Mr. Ott, Thank you for your bug report. I truly do not know what is a problem here. You are trying to insert a row into a child table, while there is not a row in the parent table with the corresponding value ....... That is breaking the principles of the referential integrity and is unrelated to the permissions.
[19 Jul 2019 13:03]
John Ott
Ah I'm sorry - I think I was a little bit unclear since I've been staring at this problem trying to figure it out. If you take a look at the WL I linked (WL#8910) this is a feature that changes the error thrown when a foreign key fails depending on the users permissions. It's intended to prevent information leakage about the contents on the parent table if the user doesn't have permissions to access the parent table. It appears this error permissions check doesn't use database level permissions, and rather only checks if the user has table level access. In my example above, my user (mysql@%) has ALL PRIVILEGES on ecto_test.*. I receive the "unprivileged" error 1216 ER_NO_REFERENCED_ROW. Once I've granted SELECT on ecto_test.t1, the error changes to 1452 ER_NO_REFERENCED_ROW_2 (with the corresponding detailed information message about the constraint that fails). However with both sets of permissions I can SELECT the parent table, so I'd expect to always see the more detailed 1452 ER_NO_REFERENCED_ROW_2 error.
[19 Jul 2019 14:25]
MySQL Verification Team
Hello Mr. Ott, I fully understood what are you looking for. Indeed, the manner in which privilege systems works now, as you presented is the correct way. When the user does not have a privilege to access the table metadata, then how can you get fully verbose info on which part of table metadata has caused this error ??? The way it functions now guarantees the security of the server.
[19 Jul 2019 14:37]
John Ott
The user already has GRANT ALL PRIVILEGES ON `ecto_test`.* TO `mysql`@`%` though - does database level permissions like that not imply table level permissions? The user can select t1 with those permissions, even before they have been granted SELECT on the table level. However, the error returned does not reflect this.
[19 Jul 2019 15:12]
MySQL Verification Team
No, database level permissions do not imply all tables permissions ..... This is explained in our Reference Manual.