Bug #140 A bug in checking privileges with multi-table updates
Submitted: 10 Mar 2003 7:10 Modified: 10 Mar 2003 7:10
Reporter: Sinisa Milivojevic Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.0 OS:Any (ALL)
Assigned to: CPU Architecture:Any

[10 Mar 2003 7:10] Sinisa Milivojevic
Description:

 UPDATE command fail with multiple myisam tables with error:
 ERROR 1142: update command denied to user: 'user@***' for table '***'
 This situation occures only when  non-root user make UPDATE and
 'tables_priv' not empty (no matter what it contains).
  Seems, that mysql ignores 'db' ad read 'tables_priv' first.

How to repeat:

So, just grant any privilege for any user on the table level. 

Suggested fix:
===== /mnt/work/mysql-4.0/sql/sql_parse.cc 1.296 vs edited =====
*** /tmp/sql_parse.cc-1.296-21842       Fri Mar  7 12:55:04 2003
--- edited//mnt/work/mysql-4.0/sql/sql_parse.cc Mon Mar 10 16:38:09 2003
***************
*** 1840,1847 ****
      break;
    }
    case SQLCOM_UPDATE:
!     if (check_access(thd,UPDATE_ACL,tables->db,&tables->grant.privilege))
        goto error;
      if (grant_option && check_grant(thd,UPDATE_ACL,tables))
        goto error;
      if (select_lex->item_list.elements != lex->value_list.elements)
--- 1840,1853 ----
      break;
    }
    case SQLCOM_UPDATE:
!     TABLE_LIST *table;
!     if (check_db_used(thd,tables))
        goto error;
+     for (table=tables ; table ; table=table->next)
+     {
+       if (check_access(thd,UPDATE_ACL,table->db,&table->grant.privilege))
+       goto error;
+     }
      if (grant_option && check_grant(thd,UPDATE_ACL,tables))
        goto error;
      if (select_lex->item_list.elements != lex->value_list.elements)
[10 Mar 2003 7:10] Sinisa Milivojevic
Fixed
[16 Sep 2003 6:50] Marco Kranz
This bug also happens with MySQL 4.1.0-alpha-max-nt.

e.g. INSERT INTO dbtable (something,else) VALUES ((SELECT something),'else')

#1142 - insert not allowed for: 'user@127.0.0.1' for table 'dbtable'

The user has full rights to the db, but it seems mysql uses the table-rights.
After I have added these rights it works fine.