3

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?

4

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.

3

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.

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.