MySQL Bugs: #34120: ON DELETE CASCADE bypasses user privileges

Bug #34120	ON DELETE CASCADE bypasses user privileges
Submitted:	29 Jan 2008 1:07	Modified:	10 Nov 2008 15:11
Reporter:	Carlos Fernandes	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.0.45	OS:	Any
Assigned to:	Calvin Sun	CPU Architecture:	Any
Tags:	bypasses, foreign, on delete cascade, permissions, privileges, user

Description:
A user without privileges on a InnoDB table can delete rows from it if he has the DELETE privilege on a referenced table with a foreign key of ON DELETE CASCADE.

How to repeat:
CREATE TABLE table1(field1, ... ,primary key(field1) type=innodb);
CREATE TABLE table2(field1, ... ,primary key(field1) type=innodb);

alter table table2 add foreign key table2(field1) references table1(field1) on delete cascade;

... add some data to table1 and table2 ...

Create userA with SELECT, INSERT, DELETE privileges to table1 and SELECT to table2

With userA, issue: DELETE FROM table1 WHERE field1=<something valid>

Because of the constraint ON DELETE CASCADE on table2, by deleting referenced data from table1 it is also deleted from table2.

However, since userA does not have the DELETE privilege on table2, the database should prevent it from happening. It does not.

Suggested fix:
Not really sure how, but perhaps check user DELETE or UPDATE privileges on tables that have ON DELETE CASCADE or ON UPDATE CASCADE constraints before allowing the operation.

Thnak you for the report.

Verified as described.

I believe this is documentation bug as this collision is not qualified anywhere.

Workaround: don't give user not sufficient privileges.

Assigning this to Calvin. Calvin promised to find out if there is an easy way for InnoDB to check the user permissions when it cascades an operation to another table.

The user who creates the foreign-key table is deliberately adding
the words ON DELETE CASCADE, that is, is deliberately saying that
deletions from the referenced-key table will cause deletions from
the referencing-key table. It's not an implicit grant but it's an
acknowledgment that the check for DELETE privilege is unnecessary.
The table creator has the right to make the constraint, so no
privilege has been bypassed.

As I read the SQL standard, no DELETE privilege is required in
this circumstance.

I tried the same operation on another famous DBMS, and it did
not check for a DELETE privilege.

Thanks, but this is not a bug.

Another known database states: 

"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."