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:
None 
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
Description:
When failing a foreign key constraint ER_NO_REFERENCED_ROW is returned even if the user has database level permissions rather than ER_NO_REFERENCED_ROW_2.

How to repeat:
$ mysql --host=127.0.0.1 -u root -p"" -vvv --force < test_create.sql
Enter password:
--------------
CREATE TABLE t1(fld1 INT PRIMARY KEY, fld2 INT) ENGINE=INNODB
--------------

Query OK, 0 rows affected (0.02 sec)

--------------
CREATE TABLE t2(fld1 INT PRIMARY KEY, fld2 INT, CONSTRAINT fk2
    FOREIGN KEY (fld1) REFERENCES t1 (fld1)) ENGINE=InnoDB
--------------

Query OK, 0 rows affected (0.01 sec)

Bye

$ mysql --host=127.0.0.1 -umysql -pmysql -vvv --force < test.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
--------------
SHOW GRANTS
--------------

+------------------------------------------------------+
| Grants for mysql@%                                   |
+------------------------------------------------------+
| GRANT USAGE ON *.* TO `mysql`@`%`                    |
| GRANT ALL PRIVILEGES ON `ecto_test`.* TO `mysql`@`%` |
+------------------------------------------------------+
2 rows in set (0.00 sec)

--------------
SELECT * FROM t1
--------------

Empty set (0.01 sec)

--------------
SELECT * FROM t2
--------------

Empty set (0.00 sec)

--------------
INSERT INTO t2 (fld1, fld2) VALUES (1, 2)
--------------

ERROR 1216 (23000) at line 7: Cannot add or update a child row: a foreign key constraint fail

$ mysql --host=127.0.0.1 -u root -p"" -vvv --force < add_permission.sql
Enter password:
--------------
GRANT SELECT ON t1 TO `mysql`@`%`
--------------

Query OK, 0 rows affected (0.00 sec)

Bye

$ mysql --host=127.0.0.1 -umysql -pmysql -vvv --force < test.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
--------------
SHOW GRANTS
--------------

+------------------------------------------------------+
| Grants for mysql@%                                   |
+------------------------------------------------------+
| GRANT USAGE ON *.* TO `mysql`@`%`                    |
| GRANT ALL PRIVILEGES ON `ecto_test`.* TO `mysql`@`%` |
| GRANT SELECT ON `ecto_test`.`t1` TO `mysql`@`%`      |
+------------------------------------------------------+
3 rows in set (0.01 sec)

--------------
SELECT * FROM t1
--------------

Empty set (0.00 sec)

--------------
SELECT * FROM t2
--------------

Empty set (0.00 sec)

--------------
INSERT INTO t2 (fld1, fld2) VALUES (1, 2)
--------------

ERROR 1452 (23000) at line 7: Cannot add or update a child row: a foreign key constraint fails (`ecto_test`.`t2`, CONSTRAINT `fk2` FOREIGN KEY (`fld1`) REFERENCES `t1` (`fld1`))
Bye
[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.