云数据库RDS

    REFERENCES-command-denied-to-user错误详解

    背景

    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权限,重新执行建表语句,创建成功。

    上一篇
    MySQL5.7ibtmp暴涨导致磁盘打满问题
    下一篇
    MySQL5.7新特性之在线调整innodb_buffer_pool_size