MySQL REFERENCES-command-denied-to-user错误详解 ERROR 1142 (42000)

背景

MySQL的权限REFERENCES是控制是否允许给表建立外键关联关系的权限。

问题现象

从MySQL5.6版本的数据库中导出的含外键的表，然后再导入到MySQL5.7版本的数据库中，导入过程中产生如下报错：

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

查看5.7版本数据库中使用的账号权限，确实没有REFERENCES权限信息。进一步查看5.6版本数据库中的账号权限，发现也不存在REFERENCES权限，但是5.6的数据库确实成功创建了含外键的表，这是为什么呢？

原因分析

经过查阅MySQL官方文档，发现MySQL在5.5、5.6、5.7版本中都存在REFERENCES权限，但是对REFERENCES权限的使用要求是存在差异的：

• MySQL5.5版本This privilege is unused before MySQL 5.5.41. As of 5.5.41, creation of a foreign key constraint requires at least one of the SELECT, INSERT, UPDATE, DELETE, or REFERENCES privileges for the parent table.
• MySQL5.6版本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.
• MySQL5.7版本Creation of a foreign key constraint requires the REFERENCES privilege for the parent table.

如上所示，在5.5和5.6版本中只需要增删查改权限即可，而5.7正式启用REFERENCES权限，需要显示的授予REFERENCES权限才能创建外键关系到表。

解决方案

对MySQL5.7版本的数据库账户，显示的授予REFERENCES权限，解决导入含外键关系表的过程中导致的报错。

案例复现

建表SQL如下：

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;
复制

MySQL5.5和5.6版本，直接创建成功，而MySQL5.7版本则报错如下：

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

给5.7的账号添加REFERENCES权限，重新执行建表语句，创建成功。

文章来源：https://cloud.baidu.com/doc/RDS/s/1jy5m1zp8