Background

MySQL's REFERENCES privilege is a privilege for control over the establishment of foreign key correlation for the table.

Failure Phenomenon

From MySQL5.6 database, export the table containing foreign key, and then import the table into MySQL5.7 database, during which there is an error report as follows:

ERROR 1142 (42000): REFERENCES command denied to user 'user'@'host' for table 'tb_01';

View the account privilege used in the database of Version 5.7, finding that there is no "REFERENCES" privilege information. Further check the account privilege in the database of Version 5.6, finding that there is no "REFERENCES" privilege either, but the table containing the foreign key is successfully created in the database of Version 5.6. Why?

Analysis of cause

From MySQL official documentation, we find that there is "REFERENCES" privilege in MySQL 5.5, 5.6, and 5.7, and the requirements about the use of "REFERENCES" privilege vary from one another:

• In MySQL5.5, this privilege is unused before MySQL 5.5.41. As of 5.5.41, the creation of a foreign key constraint requires at least one of the SELECT, INSERT, UPDATE, DELETE, or REFERENCES privileges for the parent table.
• In MySQL5.6, this privilege is unused before MySQL 5.6.22. As of 5.6.22, the creation of a foreign key constraint requires at least one of the SELECT, INSERT, UPDATE, DELETE, or REFERENCES privileges for the parent table.
• In MySQL5.7, the creation of a foreign key constraint requires the REFERENCES privilege for the parent table.

As mentioned above, in Version 5.5 and Version 5.6, Only addition, deletion, query, and modification privileges are required. However, Version 5.7 officially puts the "REFERENCES" privilege into use, and the foreign key correlation cannot be created to the table until the granted "REFERENCES" privilege is shown.

Solution

As for database account of MySQL5.7, granted "REFERENCES" privilege is shown, for resolving the error that occurs in the process of importing the table containing foreign key correlations.

Case review

SQL for table creation is as follows:

CREATE TABLE tb_01 (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(64) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE tb_02 (
  id int(11) NOT NULL AUTO_INCREMENT,
  address varchar(64) NOT NULL,
  PRIMARY KEY (id),
  CONSTRAINT tb_02_ibfk_1 FOREIGN KEY (id) REFERENCES tb_01 (id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

In MySQL5.5 and 5.6, the table is created successfully. In MySQL5.7, the error report is as follows:

ERROR 1142 (42000): REFERENCES command denied to user 'user'@'host' for table 'tb_01';

Add "REFERENCES" privilege to account for Version 5.7, execute the table creation statement anew, and successfully create the table.