Bug #15195 Security Breach with MERGE table
Submitted: 23 Nov 2005 18:00 Modified: 4 Aug 2006 19:42
Reporter: Peter Gulutzan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Merge storage engine Severity:S3 (Non-critical)
Version:5.0.17-debug/4.1BK/5.1 OS:Linux (SUSE 10.0)
Assigned to: Ramil Kalimullin CPU Architecture:Any

[23 Nov 2005 18:00] Peter Gulutzan
Description:
If a user can make a MERGE table based on table T,
he/she can ensure that later REVOKE of privileges
won't take effect -- the contents of table T will
still be accessible. This is a security breach.
A similar trick with VIEW tables is not possible.

How to repeat:
As user root, say:
CREATE DATABASE db1;
CREATE DATABASE db2;
CREATE TABLE db1.t1 (s1 INT);
INSERT INTO db1.t1 VALUES (0);
GRANT CREATE ON db2.* TO pg2@localhost;
GRANT SELECT, INSERT, UPDATE, DELETE ON db2.* TO pg2@localhost;
GRANT SELECT, INSERT, UPDATE, DELETE ON db1.t1 TO pg2@localhost;
As user pg2@localhost, say;
CREATE TABLE db2.t2 (s1 INT) engine=MERGE UNION=(db1.t1) INSERT_METHOD=LAST;
As user root, say:
REVOKE SELECT, INSERT, UPDATE, DELETE on db1.t1 FROM pg2@localhost;
As user pg2@localhost, say;
SELECT * FROM db2.t2;
[12 Jul 2006 18:07] Brian Aker
After reviewing this bug, we need to look at it with a bit more focus.
[14 Jul 2006 11:28] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/9170
[14 Jul 2006 17:06] Paul Dubois
Noted in 4.1.21 changelog.

Security fix: If a user has access to MyISAM table t, that user can
create a MERGE table m that accesses t. However, if the user's
privileges on t are subsequently revoked, the user can continue to
access t by doing so through m. If this behavior is undesirable, you
can start the server with the new --skip-merge option to disable the
MERGE storage engine.

Setting bug report back to NDI pending push of fixes to 5.0/5.1.
[20 Jul 2006 6:09] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/9363
[20 Jul 2006 6:46] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/9364
[20 Jul 2006 19:41] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/9395
[21 Jul 2006 17:29] Timothy Smith
Pushed.  Fixed in 4.1.21 and 5.0.24.  Currently merged into mysql-4.1 and mysql-5.0 global trees, will be merged to mysql-5.1 when possible.
[23 Jul 2006 3:04] Paul Dubois
Propagated changelog entry to 5.0.24 changelog, and
other notes about --skip-merge to the mysqld server
options and MERGE storage engines sections in the 5.0
manual.

Setting bug report back to NDI pending push of fix into 5.1.x.
[28 Jul 2006 20:30] Christian Hammers
This bug also applies to 3.23 and 4.0!

I've just verified it with 3.23.49 (Debian 3.0 woody) and 4.0.24 (Debian 3.1 sarge). 

In 4.0 merge tables could not yet span database boundaries but it was possible to grant access rights to individual tables inside one database and a merge table db1.t2 can still be read if the rights on db1.t1 are revoked. Same goes for 3.23 here is to be noted that the syntax was "type=merge" and not "engine=merge" and that "insert_method=last" was not yet known.

As I'm from Debian I'll contact our security team and ask them to register a CVE id if they find the issue severe enough.

bye,

-christian-
[29 Jul 2006 19:33] Sergei Golubchik
Christian, this is not a security issue, but an intentional and useful behaviour of MERGE tables.

Consequently, it could not be "fixed" - but we implemented a --skip-merge mysqld command-line option that would allow to disable MERGE tables if this behaviour is not desired. Also --skip-merge is a good thing on itself, there're many - documented - problems with MERGE tables, so it makes sense to disable them unless there're really necessary.
[30 Jul 2006 10:24] Christian Hammers
Sergey? Your collegue marked it as "Security Fix" in the Changelog of 4.0.22, how can you say that it is none? :)

Additionalyl, if an admin wants to revoke read privileges for a specific table from a user, should he really not just do a "REVOKE SELECT FROM db.table FOR user;" but instead additionally check for any previously created and thus already existing merge tables that this user has rights to and revoke the rights for this tables, too? Sounds a bit complicated and unexpected to me? Most admins probably see merge tables access rights like some kind of symbolic link in the filesystem, you can link to whatever you like but if you can read it depends on the thing you link against!

(Which is exactly the behaviour the 4.0.22 security fix release creates...)

The only difference between 4.0 and older versions is that in 4.0 it works across database boundaries and in older versions only within one database.

bye,

-christian-
[30 Jul 2006 11:50] Sergei Golubchik
Practical explanation: users may rely on the existing behaviour (providing access to some tables only via MERGE but not directly).

Conceptual explanation: when user accesses a table X, MySQL checks privileges on the table X. The table is opaque, details of the implementation of a storage engine are not relevant - that's how the abstraction of a "storage engine layer" works. To have privilege checks on the underlying tables one need to use objects that exist on the SQL layer - the same layer where privilege checks take place - that is views.
[4 Aug 2006 18:41] Timothy Smith
Pushed to 5.1; will be in 5.1.13.
[4 Aug 2006 19:18] Timothy Smith
Oops, I apoligize; this will be in 5.1.12.
[4 Aug 2006 19:42] Paul Dubois
Propagated changelog entry to 5.1.12 changelog, and
other notes about --skip-merge to the mysqld server
options and MERGE storage engines sections in the 5.1
manual.
[30 Sep 2008 9:03] Sarah Corner
I think this bug is remaining yet, because,
i do instructions had been said, in mysql 5.0.51a, 5.1.22 on windows xp sp2, and i see the same result.