Bug #105225 Creating a foreign key requires REFERENCES privilege in MySQL 5.7
Submitted: 14 Oct 9:22 Modified: 20 Oct 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

[14 Oct 9:22] Stefan Proell
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.
[14 Oct 10:24] MySQL Verification Team
Hello Stefan Proell,

Thank you for the report and feedback.

regards,
Umesh
[20 Oct 14:17] Jon Stephens
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.
[20 Oct 14:40] Jon Stephens
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.