MySQL Bugs: #15195: Security Breach with MERGE table

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

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;

After reviewing this bug, we need to look at it with a bit more focus.

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

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.

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

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

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

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.

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.

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-

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.

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-

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.

Pushed to 5.1; will be in 5.1.13.

Oops, I apoligize; this will be in 5.1.12.

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.

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.