WL#8910: Ensure foreign key error does not reveal information about parent table for which user has no access privileges.

Affects: Server-8.0   —   Status: Complete

Introduction
=============

A user having access(INSERT privileges) only to a subset of columns of a table
with a FK constraint performs an INSERT operation. This operation fails and
flags an error about FK constraint failure. This error message reveals
information about the parent table for which the user has no access priviliges.
Let us look at the example below to understand the problem.
 
-- As the root user:
root@localhost> CREATE DATABASE fktest;
Query OK, 1 row affected (0.00 sec)

root@localhost> use fktest;
Database changed
root@localhost> CREATE TABLE t1 (t1_id int unsigned NOT NULL PRIMARY KEY,
t1_val varchar(10)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.16 sec)

root@localhost> CREATE TABLE t2 (t2_id int unsigned NOT NULL PRIMARY KEY,
t1_id int unsigned DEFAULT 1, t2_val varchar(10), FOREIGN KEY (t1_id)
REFERENCES t1 (t1_id)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.13 sec)

root@localhost> GRANT INSERT (t2_id, t2_val) ON fktest.t2 TO fkuser@localhost
IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)


-- As the fkuser@localhost user:

fkuser@localhost> use fktest
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
fkuser@localhost> SHOW TABLES;
+------------------+
| Tables_in_fktest |
+------------------+
| t2               |
+------------------+
1 row in set (0.00 sec)

fkuser@localhost> SHOW CREATE TABLE t2\G
ERROR 1142 (42000): SHOW command denied to user 'fkuser'@'localhost' for
table 't2'
fkuser@localhost> INSERT INTO t2 (t2_id, t2_val) VALUES (1, 'a');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key
constraint fails (`fktest`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`t1_id`)
REFERENCES `t1` (`t1_id`))

The above error message displays information about the parent table 't1'
for which the 'fkuser' has no access.

Goal of this WL
===============

This worklog ensures that parent table information is not exposed in the error
message if the user does not have access to the parent table.
In such cases a generic error message is displayed:
Cannot add or update a child row: a foreign key constraint fails

If the user has TABLE level access to the parent table, then a more detailed
error message is reported:
ERROR 1452 (23000): Cannot add or update a child row: a foreign key
constraint fails (`fktest`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`t1_id`)
REFERENCES `t1` (`t1_id`))