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

[7 Jul 2006 12:26] Tomash Brechko
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).
[7 Jul 2006 15:10] Hartmut Holzgraefe
Verified, using a different sequence of commands though as
table t2 needs to exist before bug can be granted SELECT
and INSERT on it
[17 Sep 2007 5:16] Tatiana Azundris Nuernberg
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.
        */
[17 Sep 2007 5:48] 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/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.
[3 Oct 2007 17:48] Timothy Smith
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.
[18 Oct 2007 18:25] 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/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.
[23 Oct 2007 15:38] Tatiana Azundris Nuernberg
pushed 5.1.23-maint
[6 Dec 2007 23:23] 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/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.
[7 Dec 2007 7:54] 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/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.
[7 Dec 2007 23:08] Bugs System
Pushed into 6.0.5-alpha
[7 Dec 2007 23:09] Bugs System
Pushed into 5.1.23-rc
[21 Dec 2007 17:04] Paul Dubois
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.