9

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?

migrated from stackoverflow.com Feb 2 '15 at 13:43

This question came from our site for professional and enthusiast programmers.

14

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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