Bug #288 Problem with database privilege
Submitted: 14 Apr 2003 9:37 Modified: 14 Apr 2003 9:38
Reporter: Sinisa Milivojevic Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:3.23 and up OS:Any (ALL)
Assigned to: CPU Architecture:Any

[14 Apr 2003 9:37] Sinisa Milivojevic
Description:
UPDATE command not working due to SELECT privilege problem

How to repeat:
use mysql;
delete from user where User != 'root';
delete from db;
delete from tables_priv;
delete from columns_priv;
flush privileges;
# now all permissions are cleared

create database somedb;
create table somedb.mytest (a int, b int);
grant insert, update on *.* to test identified by 'test';
grant select on somedb.* to 'test';

grant update on somedb.mytest to anotheruser;

# now, quit, and connect as test (password 'test')
use somedb;
insert into mytest values (2, 3);

# no problem selecting
mysql> select * from mytest;
+------+------+
| a    | b    |
+------+------+
|    2 |    3 |
+------+------+
1 row in set (0.00 sec)

# no problem updating
mysql> update mytest set a = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

# however, this doesn't work
mysql> update mytest set a = b + 2 where b = 3;
ERROR 1143: select command denied to user: 'test@localhost' for column 'b'
in table 'mytest'

Suggested fix:
seems like SELECT privilege from database level was not cached properly.
[14 Apr 2003 9:38] Sinisa Milivojevic
Fixed in 4.0.13. This is a patch:
 
===== sql/sql_parse.cc 1.306 vs edited =====
*** /tmp/sql_parse.cc-1.306-12654       Tue Apr  8 08:35:10 2003
--- edited/sql/sql_parse.cc     Mon Apr 14 19:11:11 2003
***************
*** 2550,2556 ****
  
    if ((thd->master_access & want_access) == want_access)
    {
!     *save_priv=thd->master_access;
      DBUG_RETURN(FALSE);
    }
    if (((want_access & ~thd->master_access) & ~(DB_ACLS | EXTRA_ACL)) ||
--- 2550,2556 ----
  
    if ((thd->master_access & want_access) == want_access)
    {
!     *save_priv=thd->master_access | thd->db_access;
      DBUG_RETURN(FALSE);
    }
    if (((want_access & ~thd->master_access) & ~(DB_ACLS | EXTRA_ACL)) ||