MySQL Bugs: #92032: Restrict usage of session foreign_key

Bug #92032	Restrict usage of session foreign_key_checks
Submitted:	15 Aug 2018 23:07	Modified:	17 Aug 2018 13:02
Reporter:	Federico Razzoli (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Options	Severity:	S3 (Non-critical)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Any user can change the session value of foreign_key_checks, therefore any user is allow to violate foreign key constraints.

However, integrity constraints are rules that define what makes our data valid and useful. The ability to violate such rules should be compared to the ability of running an ALTER TABLE: normal users shouldn't be allowed to modify the definition of data.

Therefore, it would be desirable to restrict the usage of foreign_key_checks.

While I don't have a definitive opinion on how this should be done, I suggest that only users allowed to add/drop foreign keys are allowed to violate them.

A possible objection is that MySQL does not allow to postpone data validation to the end of a successful transaction, and setting foreign_key_checks for the current session is a workaround for this. However, it is not only that - changing that variable allows to permanently violate an integrity constraint. Not just postponing the check, but avoiding it at all.

How to repeat:
mysql> set global foreign_key_checks = 0;
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation
mysql> set session foreign_key_checks = 0;
Query OK, 0 rows affected (0.00 sec)

Hi,

Thank you for your bug report.

I tend to agree with you that this variable should not be allowed at session level, without proper privileges.

Verified as reported.

Posted by developer:
 
We should consider 1) privilege for SET FOREIGN_KEY_CHECKS=0 2) ALTER TABLE ENABLE/DISABLE FK
Reusing privilege for add/drop FKs to limit SET FOREIGN_KEY_CHECKS is not desireable as this would mean requiring global CREATE/DROP privs, which seem too much.