MySQL Bugs: #20901: CREATE privilege is enough to insert into a table

Bug #20901	CREATE privilege is enough to insert into a table
Submitted:	7 Jul 2006 12:26	Modified:	21 Dec 2007 17:04
Reporter:	Tomash Brechko	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: General	Severity:	S2 (Serious)
Version:	4.1.21-BK, 5.0.23-BK, 5.1.12-BK	OS:	Any (any)
Assigned to:	Tatiana Azundris Nuernberg	CPU Architecture:	Any

Description:
Having CREATE privilege on a table is enough to insert data into it from another table if one also have SELECT privilege on source table.

How to repeat:
CREATE USER bug@localhost;
GRANT CREATE ON test.* TO bug@localhost;
GRANT SELECT, INSERT ON test.t2 TO bug@localhost;
CREATE TABLE t1 (i INT);

As bug@localhost:

INSERT INTO t1 VALUES (1);
ERROR 1142 (42000): INSERT command denied to user 'bug'@'localhost' for table 't1'
-- The error is correct, but:
CREATE TABLE t2 (i INT);
INSERT INTO t2 VALUES (1);
CREATE TABLE IF NOT EXISTS t1 SELECT * FROM t2;
SELECT * FROM t1;
+------+
| i    |
+------+
|    1 |
+------+

Suggested fix:
Check INSERT privilege for CREATE TABLE .. SELECT (at least in IF NOT EXISTS case when table already exists).

Verified, using a different sequence of commands though as
table t2 needs to exist before bug can be granted SELECT
and INSERT on it

If we wanted to block CREATE ... SELECT unless someone actually has INSERT, the code would be the following; this would however prevent all CREATE ... SELECT (as the table has to be created first before we can grant the user INSERT on that specific table) unless someone has INSERT on the entire DB.

sql_parse.cc:~2586:
SQLCOM_CREATE_TABLE:

    if (select_lex->item_list.elements)		// With select
    {
...
+     if (!check_table_access(thd, INSERT_ACL, create_table,0) &&
          !(res=open_and_lock_tables(thd,tables)))
      {
	res= -1;				// If error
        /*
          select_create is currently not re-execution friendly and
          needs to be created for every execution of a PS/SP.
        */

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/34326

ChangeSet@1.2675, 2007-09-17 07:54:49+02:00, tnurnberg@mysql.com +5 -0
  Bug #20901: CREATE privilege is enough to insert into a table
  
  CREATE privilege let you CREATE...SELECT into an existing table,
  and one you didn't had INSERT on to boot. On existing table,
  CREATE...SELECT will now fail with an error, CREATE TABLE IF
  NOT EXISTS...SELECT with a warning; in either case, no rows will
  be inserted.

I believe the old behavior must be preserved as much as possible here.  It must be possible, assuming that permissions allow, to do "CREATE TABLE IF NOT EXISTS t SELECT ..." on an existing table t, and insert the rows from the select.  It must not fail to insert the rows with a warning in this case.  The documentation is explicit about this behavior:

http://dev.mysql.com/doc/refman/5.0/en/create-table.html

"If you use IF NOT EXISTS in a CREATE TABLE ... SELECT statement, any rows selected by the SELECT part are inserted regardless of whether the table already exists."

I believe the only behavior change should be that INSERT privileges are required on table t, for this to succeed.

One final question remains:  what to do in the case where table t does not exist, and the user has CREATE but not INSERT privileges on table t.  How should "CREATE TABLE [IF NOT EXISTS] t SELECT ..." behave?  My preference is that it fail with a permissions error.  That is, INSERT permissions are required, whether or not the table exists.

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/35863

ChangeSet@1.2675, 2007-10-18 19:46:53+02:00, tnurnberg@sin.intern.azundris.com +5 -0
  Bug #20901: CREATE privilege is enough to insert into a table
  
  CREATE TABLE IF NOT EXISTS ... SELECT let you insert into an existing
  table as long as you had the CREATE privilege.
  CREATE ... SELECT variants now always require INSERT privilege on target table.

pushed 5.1.23-maint

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/39477

ChangeSet@1.2670, 2007-12-06 16:23:03-07:00, tsmith@ramayana.hindu.god +4 -0
  Move the test case for bug #20901 from create.test to grant.test, so
  testing embedded server works correctly.

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/39494

ChangeSet@1.2670, 2007-12-07 00:54:32-07:00, tsmith@ramayana.hindu.god +4 -0
  Move the test case for bug #20901 from create.test to grant.test, so
  testing embedded server works correctly.

Pushed into 6.0.5-alpha

Pushed into 5.1.23-rc

Noted in 5.1.23, 6.0.5 changelogs.

It was possible to execute CREATE TABLE t1 ... SELECT ... FROM t2
with the CREATE privilege for t1 and SELECT privilege for t2, even in
the absence of the INSERT privilege for t1.