WL#4099: Foreign Keys: references privilege

WL#148 "Foreign Keys ..." said that MySQL would support the
REFERENCES privilege in the standard way. In the Heidelberg
meetings about foreign keys, Konstantin asked that this be
a separate task. So the REFERENCES requirement is no longer
in WL#148, but WL#148 should depend on this task, "Foreign
keys: references privilege".

Syntax
------

MySQL already handles this:
GRANT REFERENCES
REVOKE REFERENCES
GRANT ALL PRIVILEGES /* including REFERENCES */
REVOKE ALL PRIVILEGES /* including REFERENCES */

There are no known problems with these columns:
mysql.db.References_priv
mysql.tables_priv.Table_priv
mysql.columns_priv.Column_priv
mysql.host.References_priv
information_schema.table_privileges.privilege_type
information_schema.column_privileges.privilege_type

But, as the MySQL Reference Manual puts it,
"The REFERENCES privilege currently is unused."

New Behaviour
-------------

There is a new variable due to WL#148:
@@foreign_key_all_engines, which is equal to 1 if
one starts with mysqld --foreign-key-all-engines=1.

If and only if @@foreign_key_all_engines = 1,
check for REFERENCES privileges at these times:
CREATE TABLE
ALTER TABLE
And ... WL#148 says:
"If a problem arises and @@foreign_key_checks is set,
then we emit an error. Otherwise the error is
postponed until execution of the statement
which will cause problems."
That means: one could create a foreign-key table
while @@foreign_key_all_engines=0, so we must
check again for every data-change statement:
INSERT
LOAD
REPLACE
UPDATE
(we don't need to check for DELETE or TRUNCATE
because they don't require us to look at the parent)

The check is, effectively:
If @@foreign_key_checks=1
  For each table
    For each foreign key
      Look at parent i.e. referenced table.
      If user has table-level REFERENCES
      privileges on parent table:
        okay.
      If user has column-level REFERENCES
      privileges on all columns in parent
      table's referenced key:
        okay.
      Oherwise:
        error().

The error will be ERROR 1142 (42000):
REFERENCES command denied to user ... for table ...

Extracts from WL#148
--------------------

These quotations come from WL#148 Foreign Keys (all storage engines).

"
Checks during CREATE TABLE or ALTER TABLE ...
6. For each column in pk_column list:
     If any column is nullable, error
     If user does not have REFERENCES privilege on column, error
     (see WL#4099 "Foreign keys: reference privilege")
...
PRIVILEGES. Privilege checks will occur both at CREATE time
and for every statement, for tables that reference or are
referenced by foreign keys, i.e. are children or parents.
...
fk_prepare() ...
  /* Error if: missing appropriate privilege.
     Once WL#4099 "Foreign keys: references privilege" is
     complete we will have appropriate check for REFERENCES privilege
     here. */
...
fk_check_constraint_added() ...
  /* User must have appropriate privilege on all parent table, or
     on all columns of parent table. */
  /* Once WL#4099 "Foreign keys: references privilege" is
     complete we will have appropriate check for REFERENCES privilege
     here. */
...
GRANT ...
However, now that 'REFERENCES' is more than meaningless,
the category and priority of this bug should be reconsidered:
BUG#5503 Views: GRANT REFERENCES statement should be disallowed
...
REVOKE ...
The original LLD had this question in the Q+A:
"Q. How we should handle REFERENCES privilege? Should we really
 do nothing when it is revoked ..."
And the answer was -- we really should do nothing.
To prevent a major security breach (cascaded updates
changing tables that users have no privileges on),
there will be runtime privilege checks, see 
fk_prepare().

"

The standard
------------

The standard says that for an
UPDATE you need an UPDATE privilege on every
column that's updated, but a cascading update
does not go through the general rules for
, it goes
directly into the rules for "effect of replacing
rows in base tables" (look for example at the
description after the words "If the 
specifies CASCADE ..." in general rules for
referential constraint definition). The only
applicable access rule, then, is the one for
making the referential constraint definition
itself, that is,
"1) The applicable privileges for the owner
of T [i.e. the referencing table] shall include
REFERENCES for each referenced column [which is
implied by REFERENCES for the referenced table]."

In other words, after

CREATE TABLE t1 (s1 INT PRIMARY KEY);
CREATE TABLE t2 (s1 INT, FOREIGN KEY (s1)
  REFERENCES t1 (s1) ON UPDATE CASCADE);

a person who updates t1 (and therefore updates
t2) does not need UPDATE privileges on t2.
See also the comment on BUG#34120.

For CREATE TABLE this looks okay; the person
who creates the table has all privileges and
can therefore take responsibility. For ALTER,
e.g. if I alter to add a foreign key when I
have ALTER privilege, then I should have
REFERENCES privilege too. This detail is
not standard since the standard assumption
is that the creator is doing the altering.

Apparently Oracle and SQL Server follow the standard.
Looking at the Oracle and SQL Server manuals,
I see no suggestion that any privilege other
than REFERENCES is necessary for referencing
another table.

BUG#29672 Security breach via foreign key
-----------------------------------------

The current InnoDB situation is: no checks.
However, implementing WL#148 does not fix
BUG#29672 Security breach via foreign key,
because "--foreign-key-all-engines=1" is not
the default setting, and anyway InnoDB's
checks are not brought in if one starts with
mysqld --foreign-key-all-engines=1.

Therefore BUG#29672 should not say
Target Version = 6.1. It has nothing to do
with the new foreign-keys implementation.

BUG#5503 Views: GRANT REFERENCES statement should be disallowed
----------------------------------------------------------------

MySQL may continue to allow granting REFERENCES
privileges on views. However, WL#148 said we
must "consider" making BUG#5503 higher priority.

We won't. It's too late to fix this now.
It's legal to grant REFERENCES on a view, but pointless.

Upgrading from 6.0 to 6.1
-------------------------

Since almost everyone has REFERENCES privilege
now, I see no great difficulty for an upgrade
to the version that supports REFERENCES privilege.

The alternative would have been:
* require SELECT privilege on the referenced table
* require SELECT privilege on the referencing table
* require UPDATE privilege on the referencing table
  if referential action is SET NULL, SET DEFAULT,
  or (update) CASCADE
* require DELETE privilege on the referencing table
  if referential action is (update) CASCADE.
None of that will happen.

There are no changes to the privileges required
for access to INFORMATION_SCHEMA views.

Assume that WL#4099 will be coded before WL#148 is alpha.

Examples
--------

1. As user=root, say:
   CREATE TABLE d.t1 (s1 INT PRIMARY KEY);
   GRANT CREATE ON d.* TO joe;
   GRANT SELECT, UPDATE, INSERT ON d.t1 TO joe;
   As user=joe, say:
   CREATE TABLE d.t2 (s1 INT REFERENCES t1 (s1));
   Result: Error, joe needs REFERENCES privilege on t1.

2. As user=root, say:
   CREATE TABLE d.t1 (s1 INT PRIMARY KEY);
   CREATE TABLE d.t2 (s1 INT REFERENCES t1 (s1));
   GRANT REFERENCES ON d.t1 TO joe;
   GRANT INSERT ON d.t2 TO joe;
   INSERT INTO d.t1 VALUES (1);
   As user=joe, say:
   INSERT INTO d.t2 VALUES (1);
   Result: No error, Joe has INSERT privilege on t2
   and REFERENCES privilege on t1. Joe does not need
   SELECT privilege on t1, even though he is in effect
   reading it (finding out that it contains '1').

3. As user=root, say:
   CREATE TABLE d.t1 (s1 INT, s2 INT, PRIMARY KEY (s1,s2));
   CREATE TABLE d.t2 (s1 INT, s2 INT, FOREIGN KEY (s1,s2) REFERENCES d.t1 (s1,s2));
   GRANT REFERENCES ON d.t1 (s2) TO joe WITH GRANT OPTION;
   GRANT UPDATE ON d.* TO mary;
   As user=joe, say:
   GRANT ALL PRIVILEGES ON d.t1 TO mary;
   As user=mary, say:
   UPDATE d.t2 SET s2 = 5;
   Result: error. When joe granted ALL PRIVILEGES, he
   only had one privilege to grant: REFERENCES on column
   d.t1.s2. Mary needs REFERENCES on column d.t1.s1. It does not
   matter that there are no rows to update. It does not
   matter that Mary is not changing a column that references
   d.t1.s1.

References
----------

dev-private thread "WL#148 and REFERENCES privilege"
https://intranet.mysql.com/secure/mailarchive/mail.php?folder=4&mail=20828