MySQL Bugs: #105225: Creating a foreign key requires REFERENCES privilege in MySQL 5.7

Bug #105225	Creating a foreign key requires REFERENCES privilege in MySQL 5.7
Submitted:	14 Oct 2021 9:22	Modified:	20 Oct 2021 14:40
Reporter:	Stefan Proell (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.7	OS:	Any
Assigned to:	Jon Stephens	CPU Architecture:	Any
Tags:	foreign key, privileges, references, upgrade

Description:
MySQL 5.6.22 makes use of the REFERENCES privilege which was unused in versions < 5.6.22. The documentation of MySQL 5.6 on this privilege states [1]:

> This privilege is unused before MySQL 5.6.22. As of 5.6.22, creation of a 
> foreign key constraint requires at least one of the SELECT, INSERT, UPDATE, 
> DELETE, or REFERENCES privileges for the parent table.

With MySQL 5.7 this privilege is required for creating foreign keys [2]. It is not sufficient anymore, when a user has one of the SELECT, INSERT, UPDATE, DELETE privileges for the parent table. Now the user needs the REFERENCES privilege explicitly. Not having this privilege breaks the import of a dump when it contains foreign keys. 

This should be mentioned on the migration page [3].

[1] https://dev.mysql.com/doc/refman/5.6/en/privileges-provided.html#priv_references
[2] https://dev.mysql.com/doc/refman/5.7/en/privileges-provided.html#priv_references
[3] https://dev.mysql.com/doc/refman/5.7/en/mysql-nutshell.html

How to repeat:
-- Grant user the basic privileges: USAGE, SELECT, INSERT, UPDATE, DELETE
-- Create the parent table

CREATE TABLE `Parent` (
  `_id` bigint(20) NOT NULL AUTO_INCREMENT, 
  PRIMARY KEY (`_id`)
) ENGINE=InnoDB;

-- The creation of the child table which has a foreign key to the parent table fails

CREATE TABLE `Child` (
  `_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `_parent_id` bigint(20) NOT NULL,  
  PRIMARY KEY (`_id`),
  KEY `FK_parent` (`_parent_id`),
  CONSTRAINT `FK_parent` FOREIGN KEY (`_parent_id`) REFERENCES `Parent` (`_id`)
) ENGINE=InnoDB;

Suggested fix:
Mention on the migration page of MySQL 5.7 that this privilege is now required. Granting the REFERENCES privilege fixes the problem.

Hello Stefan Proell,

Thank you for the report and feedback.

regards,
Umesh

FYI, that's not what we would consider the "migration page". For upgrades, please consult https://dev.mysql.com/doc/refman/5.7/en/upgrading.html.

This being said, the issue you highlight is not mentioned in that section, and a fix for that is in progress. Thanks for bringing this to our attention.

I've added an item to the list of issues here: 

https://dev.mysql.com/doc/refman/5.7/en/upgrading-from-previous-series.html#upgrade-server...

Fixed in mysqldoc rev 71142.

Closed.