ERROR 1142 (42000): REFERENCES command denied to user 'user'

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

问题现象

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

ERROR 1142 (42000): REFERENCES command denied to user 'user'@'host' 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 bee MySQL 5.5.41. As of 5.5.41, creation of a eign key constraint requires at least one of the SELECT, INSERT, UPDATE, DELETE, or REFERENCES privileges the parent table.
  • MySQL5.6版本This privilege is unused bee MySQL 5.6.22. As of 5.6.22, creation of a eign key constraint requires at least one of the SELECT, INSERT, UPDATE, DELETE, or REFERENCES privileges the parent table.
  • MySQL5.7版本Creation of a eign key constraint requires the REFERENCES privilege 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' table 'tb_01';

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