the REFERENCES privilege is only about creating a foreign key constraint? Practical use cases?

Question

Today I learned about GRANT REFERENCES. In years of SQL admin and dev work I never heard about it and never had issues with it.

quote from MySQL 5.7 Reference Manual / GRANT Syntax

REFERENCES Enable foreign key creation. Levels: Global, database, table, column.

quote from PostgreSQL 9.6 / GRANT

REFERENCES To create a foreign key constraint, it is necessary to have this privilege on both the referencing and referenced columns. The privilege may be granted for all columns of a table, or just specific columns.

is GRANT REFERENCES only about creating a foreign key constraint? In what business case does it make sense to forbid creating a foreign key constraint (but allow to create tables)? Can you give me real world examples?

Answer 1

If you have the ability to create a foreign key constraint, you are actually getting permission to check for the existence/non-existence of a certain value in a certain column (or set of columns) of a table. You may not have the privilege to SELECT the whole list of values in this column, but you can already know something about them. You could try to insert values on the referencing table. The ones that are allowed, you already know exist in the referenced table.

If you should know absolutely nothing about those values, you cannot be granted the privilege to make a foreign key constraint on the column.

Add to that the considerations pointed out by @Erwin, WRT to being able to prevent deletes from the referenced tables.

Answer 2

What joanolo describes is a valid consideration. A user can achieve partial SELECT privileges on the referenced table if (s)he is allowed to create a FK reference.

More importantly, a user that can create an FK reference to a table can effectively also prevent deletion of rows and update of key columns. Rows with a FK reference to it are protected that way (unless CASCADE has been defined for DELETE and UPDATE. The Postgres manual:

Restricting and cascading deletes are the two most common options. RESTRICT prevents deletion of a referenced row. NO ACTION means that if any referencing rows still exist when the constraint is checked, an error is raised; this is the default behavior if you do not specify anything.

Answer 3

To extend on Erwin's answer, there's a third case why the creation of foreign key constraints may require special permissions:

• a foreign key grants users of the table the ability to test for existence of rows in the referenced table
• a foreign key with RESTRICT or NO ACTION rules prevents users of the referenced table from updating or deleting referenced row, and gives them the ability to test for the existence of referencing rows
• a foreign key with CASCADE or SET NULL/DEFAULT rules also grants users of the referenced table the ability to update or delete rows in the referencing table

I have not found this anywhere in the Postgres documentation, but only mentioned in passing on the mailing list here

When a foreign key reference delete cascades, I can cause records to disappear from a referencing table even if I don't have delete permission on that table.

and there

FK CASCADE queries are run as the owner of the table in question.

This perhaps surprising behaviour is also topic of this MySQL bug report.

IBM states in their Informix documentation

To have DELETE actions cascade to a table that has a referential constraint on a parent table, you need the Delete privilege only on the parent table that you reference in the DELETE statement.