21

What's the minimum privilege needed to alter a foreign key constraint?

My migration script stopped working after MySQL 5.5.41 fixed this bug:

  • InnoDB permitted a foreign key to be created which referenced a parent table for which the user did not have sufficient privileges. (Bug #18790730)

I get this error:

SQLSTATE[42000]: Syntax error or access violation: 1142 REFERENCES command denied to user 'foo'@'localhost' for table 'core.users' (SQL: alter table `user_baz` add constraint user_baz_user_id_foreign foreign key (`user_id`) references `core`.`users` (`id`) on delete cascade on update cascade)

Which means I need to fix the privileges. What's the minimum privilege I need?

Improve this question
0

3 Answers 3

Reset to default
23

You need to add the "REFERENCES" privilege to your role.

Improve this answer
2
  • 3
    This type of "theoretical" answer leads just for additional googling on how to add the privilege in practice. See @Yuci's answer, which gives all the needed details. That is, GRANT REFERENCES ON test.user_baz TO 'foo'@'localhost';
    – John Mayor
    May 22, 2017 at 14:22
  • @JohnMayor I would say the answer is addressing the moot question. The fact that the asker didnt ask something generic like "what do I do to resolve this", rather asked specifically about the privileges, confirms that he/she knows about privileges and natually expectedly how to deal with them. A holistic answer is definitely helpful, but exploration/googling teaches a lot of additional stuff on the way to reach the exact answer from the lead given.
    – SubhasisM
    Jan 31 at 18:58
18 
GRANT [type of permission] ON [database name].[table name] TO '[username]'@'[host name or IP address]';

For example:

GRANT REFERENCES ON test.user_baz TO 'foo'@'localhost';
Improve this answer
1

Firstly, if all else fails, read the documentation (Usage Notes section).

To use `ALTER TABLE`, you need `ALTER`, `CREATE` and `INSERT` privileges for the table. Note that the user (billy) granted these privileges cannot drop the table.

Below is an example.

mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |    <=== now root user
+----------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE a(b VARCHAR(3) PRIMARY KEY); <=== Must be PK to be FK in another table.
Query OK, 0 rows affected (0.28 sec)

mysql> CREATE TABLE c(d VARCHAR(3), KEY c_ix (d));
Query OK, 0 rows affected (0.35 sec)

mysql> GRANT ALTER, CREATE, INSERT ON c TO billy;  <=== Privileges to billy
Query OK, 0 rows affected (0.00 sec)

mysql> exit;
Bye

logon as billy

[pol@localhost dbahelper-master]$ /mysql/5.7/inst/bin/mysql -S /mysql/5.7/inst/mysql.sock -u billy -pdba

mysql> use test;
Database changed
mysql> 
mysql> ALTER TABLE c ADD CONSTRAINT fk_c_a FOREIGN KEY (d) REFERENCES a(b);
Query OK, 0 rows affected (0.64 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE c;
| c     | CREATE TABLE `c` (
  `d` varchar(3) DEFAULT NULL,
  KEY `c_ix` (`d`),
  CONSTRAINT `fk_c_a` FOREIGN KEY (`d`) REFERENCES `a` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
mysql> 
mysql> drop table c;
ERROR 1142 (42000): DROP command denied to user 'billy'@'localhost' for table 'c'
mysql>
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.