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

Question

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?

score 25 · Answer 1 · edited Jan 05 '17 at 00:53

25

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

edited Jan 05 '17 at 00:53

Asaph

133
5

answered Apr 18 '16 at 00:22

smoothdvd

351
3
5

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 '17 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 '23 at 18:58

score 20 · Answer 2 · answered Oct 04 '16 at 20:59

20

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';

answered Oct 04 '16 at 20:59

Yuci

341
2
3

score 1 · Answer 3 · edited Mar 13 '15 at 14:01

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>

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

3 Answers3