Bug #288 Problem with database privilege

• Submitted: 14 Apr 2003 9:37
• Modified: 14 Apr 2003 9:38
• Reporter: SINISA MILIVOJEVIC
• Status: Closed
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 3.23 and up
• OS: Any (ALL)
• 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] MySQL Verification Team

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)) ||