WL#4099: Foreign Keys: references privilege
Affects: Server-6.1 — Status: Assigned
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
Copyright (c) 2000, 2021, Oracle Corporation and/or its affiliates. All rights reserved.