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

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`))

a) This WL masks only the foreign key constraint errors i.e
ER_NO_REFERENCED_ROW_2/ER_ROW_IS_REFERENCED_2 with ER_NO_REFERENCED_ROW/
ER_ROW_IS_REFERENCED respectively. The masking is determined by the access
privileges of the user to the parent table. If the user has table level
privileges to the parent table a more detailed foreign key constraint error
is reported i.e ER_NO_REFERENCED_ROW_2/ER_ROW_IS_REFERENCED_2 else a generic
foregin key constraint failure error ER_NO_REFERENCED_ROW/
ER_ROW_IS_REFERENCED is reported.

b) DML operations executed within the DEFINER context(SQL SECURITY DEFINER) of
Stored Routines where the DEFINER has access privileges to the parent table, the
foreign key constraint errors will expose information about the parent table
even if the invoker of Stored Routines does not have access privilege to the
parent table. This WL will not mask information in such scenario and it is the
responsibility of the SP creator to hide the information.

c) Users having 'SET_USER_ID' administrative privilege can specify DEFINER for
Stored Routines where the definer account has access privileges to parent table.
Stored Routines containing DML executed within the DEFINER context i.e SQL
SECURITY DEFINER, which results in foreign key constraint error will expose
information about the parent table even though the invoker of Store routines has
no access privileges to the parent table. This WL will not handle the error
message masking for such cases and it is the responsibility of the
administration and stored routines creator to hide the information.

d) DMLs on views executed within a DEFINER context (SQL SECURITY
DEFINER/SET_USER_ID)will not expose information about parent table if there is a
foreign key constraint failure while performing DML on the view i.e the
underlying view table even if the DEFINER account has access to the parent
table. The information of the parent table is exposed upon a FK failure only if
the invoker has access to the parent table.

Scope of this WL
================
a) This WL masks only the foreign key constraint errors i.e
ER_NO_REFERENCED_ROW_2/ER_ROW_IS_REFERENCED_2 with ER_NO_REFERENCED_ROW/
ER_ROW_IS_REFERENCED respectively. The masking is determined by the access
privileges of the user to the parent table. If the user has table level
privileges to the parent table a more detailed foreign key constraint error
is reported i.e ER_NO_REFERENCED_ROW_2/ER_ROW_IS_REFERENCED_2 else a generic
foregin key constraint failure error ER_NO_REFERENCED_ROW/
ER_ROW_IS_REFERENCED is reported.

b) DML operations executed within the DEFINER context(SQL SECURITY DEFINER) of
Stored Routines where the DEFINER has access privileges to the parent table, the
foreign key constraint errors will expose information about the parent table
even if the invoker of Stored Routines does not have access privilege to the
parent table. This WL will not mask information in such scenario and it is the
responsibility of the SP creator to hide the information.

c) Users having 'SET_USER_ID' administrative privilege can specify DEFINER for
Stored Routines where the definer account has access privileges to parent table.
Stored Routines containing DML executed within the DEFINER context i.e SQL
SECURITY DEFINER, which results in foreign key constraint error will expose
information about the parent table even though the invoker of Store routines has
no access privileges to the parent table. This WL will not handle the error
message masking for such cases and it is the responsibility of the
administration and stored routines creator to hide the information.

d) DMLs on views executed within a DEFINER context (SQL SECURITY
DEFINER/SET_USER_ID)will not expose information about parent table if there is a
foreign key constraint failure while performing DML on the view i.e the
underlying view table even if the DEFINER account has access to the parent
table. The information of the parent table is exposed upon a FK failure only if
the invoker has access to the parent table. This is due to the fact that for a
VIEW, the THD security context is backed up and a security context is prepared
from the definer clause of the parsed view definition and access checks are
performed in this context. After which the security context is restored to the
invoker context for the subsequent execution. Since the foreign key constraint
check is performed by SE on the underlying table in the invoker's security
context, it is hard to determine the original security context and also if the
insert was triggered on the table through a DML on the view. Hence parent table
accesss check is performed in the invoker's context.

Background
===========
The foreign key constraint check is performed by the storage engine while the
privilege checks is performed at the SQL layer. However as part of WL#6292, the
meta data information about foreign key was moved to new DD. With the bug fix
for BUG#26719289, the information about foreign keys is cached in TABLE_SHARE
object which helps in implementing this WL.

Implementation approach:
=======================
Currently InnoDB and NDB are the only storage engines which supports Foreign
keys. They rely on the handler virtual function 'print_error' to map the handler
errors to SQL errors. 

1) When the foreign key constraint check fails, the handler error
HA_ERR_ROW_IS_REFERENCED/HA_ERR_NO_REFERENCED_ROW is returned by the SE. A
foreign key constraint error handler is pushed while mapping the foreign key
handler error to SQL error in the 'print_error()'.
2) The error handler accesses the parent table information from the cached
foreign key information present in the TABLE_SHARE of the child table.
3) Performs access check i.e TABLE level ACLS on the parent table. If the user
has TABLE level ACL on all the parent tables, then the error
'ER_NO_REFERENCED_ROW_2/ER_ROW_IS_REFERENCED_2'(Gives parent table information)
is reported. If the access to parent table is denied, a generic foreign key
constraint failure error ER_NO_REFERENCED_ROW/ER_ROW_IS_REFERENCED is reported.

The security context is switched(for cases a and b), hence the access check is
based on privileges of the switched context rather than the invoker. Also, the
IGNORE handler has been modified for suppressing the
ER_NO_REFERENCED_ROW/ER_ROW_IS_REFERENCED errors into warnings for the INSERT
IGNORE operations.