Bug #41597 After rename of user, there are additional grants when grants are reapplied.

• Submitted: 18 Dec 2008 17:16
• Modified: 18 Dec 2009 20:39
• Reporter: Hema Sridharan
• Status: Closed
• Category: MySQL Server: Security: Privileges
• Severity: S3 (Non-critical)
• Version: mysql-6.0-backup, 5.1, 5.0, azalea
• OS: Linux
• Assigned to: Satya B
• CPU Architecture: Any

[18 Dec 2008 17:16] Hema Sridharan

Description:
1) Create database(try) and tables in the database try.
2) Create user jim@'%';
4) Grants privileges to users on try.t1 and try.t2.
5) Perform backup database operation(try1.bak)
6) Rename the user from jim to tom and check the grants for tom.
7) Perform backup database to try2.bak.
8) Drop the database and perform restore operation from try2.bak.
9) Check the grants for tom. It will show additional grants.

create user jim@'%';
create database try;
create table try.t1(a int, b char(20));
create table try.t2(a char(4), b int);
Grant select(a), insert(b) on try.t1 to jim@'%';
Grant update(b) on try.t2 to jim@'%';
show grants for jim;
backup database try to 'try1.bak';
rename user jim@'%' to tom@'%';
show grants for tom;
backup database try to 'try2.bak';
drop database try;
restore from 'try2.bak';
show grants for tom; 

How to repeat:
mysql> create user jim@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> create database try;
Query OK, 1 row affected (0.00 sec)
mysql> create table try.t1(a int, b char(20));
Query OK, 0 rows affected (0.00 sec)
mysql> create table try.t2(a char(4), b int);
Query OK, 0 rows affected (0.00 sec)
mysql> Grant select(a), insert(b) on try.t1 to jim@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> Grant update(b) on try.t2 to jim@'%';
Query OK, 0 rows affected (0.01 sec)
mysql> show grants for jim;
+---------------------------------------------------------+
| Grants for jim@%                                        |
+---------------------------------------------------------+
| GRANT USAGE ON *.* TO 'jim'@'%'                         |
| GRANT UPDATE (b) ON `try`.`t2` TO 'jim'@'%'             |
| GRANT SELECT (a), INSERT (b) ON `try`.`t1` TO 'jim'@'%' |
+---------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> backup database try to 'try1.bak';
+-----------+
| backup_id |
+-----------+
| 270       |
+-----------+
1 row in set (0.17 sec)

mysql> rename user jim@'%' to tom@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for tom;
+---------------------------------------------------------+
| Grants for tom@%                                        |
+---------------------------------------------------------+
| GRANT USAGE ON *.* TO 'tom'@'%'                         |
| GRANT UPDATE (b) ON `try`.`t2` TO 'tom'@'%'             |
| GRANT SELECT (a), INSERT (b) ON `try`.`t1` TO 'tom'@'%' |
+---------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> backup database try to 'try2.bak';
+-----------+
| backup_id |
+-----------+
| 271       |
+-----------+
1 row in set (0.05 sec)

mysql> drop database try;
Query OK, 2 rows affected (0.00 sec)

mysql> restore from 'try2.bak';
+-----------+
| backup_id |
+-----------+
| 272       |
+-----------+
1 row in set (0.01 sec)

mysql> show grants for tom;
+---------------------------------------------------------+
| Grants for tom@%                                        |
+---------------------------------------------------------+
| GRANT USAGE ON *.* TO 'tom'@'%'                         |
| GRANT UPDATE ON `try`.`t2` TO 'tom'@'%'                 |
| GRANT UPDATE (b) ON `try`.`t2` TO 'tom'@'%'             |
| GRANT SELECT, INSERT ON `try`.`t1` TO 'tom'@'%'         |
| GRANT SELECT (a), INSERT (b) ON `try`.`t1` TO 'tom'@'%' |
+---------------------------------------------------------+
5 rows in set (0.00 sec)

Also create a new user with name jim@'%' and perform restore from try1.bak and verify the grants for jim and hema.

mysql> drop database try;
Query OK, 2 rows affected (0.00 sec)

mysql> create user jim@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> restore from 'try1.bak';
+-----------+
| backup_id |
+-----------+
| 273       |
+-----------+
1 row in set (0.01 sec)

mysql> show grants for jim;
+---------------------------------+
| Grants for jim@%                |
+---------------------------------+
| GRANT USAGE ON *.* TO 'jim'@'%' |
+---------------------------------+
1 row in set (0.00 sec)

mysql> show grants for tom;;
+---------------------------------------------------------------+
| Grants for tom@%                                              |
+---------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'tom'@'%'                               |
| GRANT UPDATE ON `try`.`t2` TO 'tom'@'%'                       |
| GRANT UPDATE (b, b) ON `try`.`t2` TO 'tom'@'%'                |
| GRANT SELECT, INSERT ON `try`.`t1` TO 'tom'@'%'               |
| GRANT SELECT (a, a), INSERT (b, b) ON `try`.`t1` TO 'tom'@'%' |
+---------------------------------------------------------------+
5 rows in set (0.00 sec)

[14 May 2009 21:38] Chuck Bell

This is not specific to backup. Problem can be recreated without using backup.

Run this scenario:

drop database if exists try;
create database try;
create table try.t1(a int, b char(20));
create table try.t2(a char(4), b int);
drop user jim;
drop user tom;
create user jim@'%';
Grant select(a), insert(b) on try.t1 to jim@'%';
Grant update(b) on try.t2 to jim@'%';
show grants for jim@'%';
rename user jim@'%' to tom@'%';
show grants for tom@'%';
GRANT UPDATE(b) ON try.t2 TO 'tom'@'%';
GRANT SELECT(a) ON try.t1 TO 'tom'@'%';
GRANT INSERT(b) ON try.t1 TO 'tom'@'%';
show grants for tom@'%'; 

You will get this result:

mysql> drop database if exists try;
Query OK, 2 rows affected (0.00 sec)

mysql> create database try;
Query OK, 1 row affected (0.00 sec)

mysql> create table try.t1(a int, b char(20));
Query OK, 0 rows affected (0.00 sec)

mysql> create table try.t2(a char(4), b int);
Query OK, 0 rows affected (0.05 sec)

mysql> drop user jim;
ERROR 1396 (HY000): Operation DROP USER failed for 'jim'@'%
mysql> drop user tom;
Query OK, 0 rows affected (0.00 sec)

mysql> create user jim@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> Grant select(a), insert(b) on try.t1 to jim@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> Grant update(b) on try.t2 to jim@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for jim@'%';
+---------------------------------------------------------+
| Grants for jim@%                                        |
+---------------------------------------------------------+
| GRANT USAGE ON *.* TO 'jim'@'%'                         |
| GRANT SELECT (a), INSERT (b) ON `try`.`t1` TO 'jim'@'%' |
| GRANT UPDATE (b) ON `try`.`t2` TO 'jim'@'%'             |
+---------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> rename user jim@'%' to tom@'%';
Query OK, 0 rows affected (0.02 sec)

mysql> show grants for tom@'%';
+---------------------------------------------------------+
| Grants for tom@%                                        |
+---------------------------------------------------------+
| GRANT USAGE ON *.* TO 'tom'@'%'                         |
| GRANT SELECT (a), INSERT (b) ON `try`.`t1` TO 'tom'@'%' |
| GRANT UPDATE (b) ON `try`.`t2` TO 'tom'@'%'             |
+---------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> GRANT UPDATE(b) ON try.t2 TO 'tom'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT(a) ON try.t1 TO 'tom'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT INSERT(b) ON try.t1 TO 'tom'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for tom@'%';
+---------------------------------------------------------+
| Grants for tom@%                                        |
+---------------------------------------------------------+
| GRANT USAGE ON *.* TO 'tom'@'%'                         |
| GRANT SELECT, INSERT ON `try`.`t1` TO 'tom'@'%'         |
| GRANT UPDATE ON `try`.`t2` TO 'tom'@'%'                 |
| GRANT UPDATE (b) ON `try`.`t2` TO 'tom'@'%'             |
| GRANT SELECT (a), INSERT (b) ON `try`.`t1` TO 'tom'@'%' |
+---------------------------------------------------------+
5 rows in set (0.00 sec)

mysql>

However, if you run this script, we see that reapplying grants does not cause the problem:

drop database if exists try;
create database try;
create table try.t1(a int, b char(20));
create table try.t2(a char(4), b int);
drop user tom;
Grant select(a), insert(b) on try.t1 to tom@'%';
Grant update(b) on try.t2 to tom@'%';
show grants for tom@'%';
GRANT UPDATE(b) ON try.t2 TO 'tom'@'%';
GRANT SELECT(a) ON try.t1 TO 'tom'@'%';
GRANT INSERT(b) ON try.t1 TO 'tom'@'%';
show grants for tom@'%'; 

mysql> drop database if exists try;
Query OK, 2 rows affected (0.00 sec)

mysql> create database try;
Query OK, 1 row affected (0.00 sec)

mysql> create table try.t1(a int, b char(20));
Query OK, 0 rows affected (0.02 sec)

mysql> create table try.t2(a char(4), b int);
Query OK, 0 rows affected (0.05 sec)

mysql> drop user tom;
Query OK, 0 rows affected (0.00 sec)

mysql> Grant select(a), insert(b) on try.t1 to tom@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> Grant update(b) on try.t2 to tom@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for tom@'%';
+---------------------------------------------------------+
| Grants for tom@%                                        |
+---------------------------------------------------------+
| GRANT USAGE ON *.* TO 'tom'@'%'                         |
| GRANT SELECT (a), INSERT (b) ON `try`.`t1` TO 'tom'@'%' |
| GRANT UPDATE (b) ON `try`.`t2` TO 'tom'@'%'             |
+---------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> GRANT UPDATE(b) ON try.t2 TO 'tom'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT(a) ON try.t1 TO 'tom'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT INSERT(b) ON try.t1 TO 'tom'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for tom@'%';
+---------------------------------------------------------+
| Grants for tom@%                                        |
+---------------------------------------------------------+
| GRANT USAGE ON *.* TO 'tom'@'%'                         |
| GRANT SELECT (a), INSERT (b) ON `try`.`t1` TO 'tom'@'%' |
| GRANT UPDATE (b) ON `try`.`t2` TO 'tom'@'%'             |
+---------------------------------------------------------+
3 rows in set (0.00 sec)

mysql>

Clearly, something is wrong with the RENAME USER command. 

Setting back to open and renaming title accordingly.

[4 Aug 2009 7:18] Sveta Smirnova

Re-verified with 5.0, 5.1 and azalea trees: bug exists.

[22 Sep 2009 10:44] 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/84093

2814 Satya B	2009-09-22
      Fix for Bug #41597 - After rename of user, there are additional grants when 
                           grants are reapplied.
      
      
      After renaming a user and trying to re-apply grants results in additional
      grants.
      
      This is because we use username as part of the key for GRANT_TABLE structure.
      When the user is renamed, we only change the username stored and the hash key
      still contains the old user name and this results in the extra privileges
      
      Fixed by rebuilding the hash key and updating the column_priv_hash structure
      when the user is renamed
     @ mysql-test/r/grant3.result
        Bug #41597 - After rename of user, there are additional grants when 
                     grants are reapplied.
        
        Testcase for BUG#41597
     @ mysql-test/t/grant3.test
        Bug #41597 - After rename of user, there are additional grants when 
                     grants are reapplied.
        
        Testcase for BUG#41597
     @ sql/sql_acl.cc
        Bug #41597 - After rename of user, there are additional grants when 
                     grants are reapplied.
        
        Fixed handle_grant_struct() to update the hash key when the user is renamed.

[7 Oct 2009 13: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/86013

2815 Satya B	2009-10-07
      Fix for Bug #41597 - After rename of user, there are additional grants when 
                           grants are reapplied.
      
      
      After renaming a user and trying to re-apply grants results in additional
      grants.
      
      This is because we use username as part of the key for GRANT_TABLE structure.
      When the user is renamed, we only change the username stored and the hash key
      still contains the old user name and this results in the extra privileges
      
      Fixed by rebuilding the hash key and updating the column_priv_hash structure
      when the user is renamed
     @ mysql-test/r/grant3.result
        Bug #41597 - After rename of user, there are additional grants when 
                     grants are reapplied.
        
        Testcase for BUG#41597
     @ mysql-test/t/grant3.test
        Bug #41597 - After rename of user, there are additional grants when 
                     grants are reapplied.
        
        Testcase for BUG#41597
     @ sql/sql_acl.cc
        Bug #41597 - After rename of user, there are additional grants when 
                     grants are reapplied.
        
        added new method setUserDetails() to GRANT_NAME class, reused this
        method to be called from constructor and also from handle_grant_struct() 
        when the username and hostname are changed.

[13 Oct 2009 12:59] 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/86692

2818 Satya B	2009-10-13
      Fix for Bug #41597 - After rename of user, there are additional grants when 
                           grants are reapplied.
      
      
      After renaming a user and trying to re-apply grants results in additional
      grants.
      
      This is because we use username as part of the key for GRANT_TABLE structure.
      When the user is renamed, we only change the username stored and the hash key
      still contains the old user name and this results in the extra privileges
      
      Fixed by rebuilding the hash key and updating the column_priv_hash structure
      when the user is renamed
     @ mysql-test/r/grant3.result
        Bug #41597 - After rename of user, there are additional grants when 
                     grants are reapplied.
        
        Testcase for BUG#41597
     @ mysql-test/t/grant3.test
        Bug #41597 - After rename of user, there are additional grants when 
                     grants are reapplied.
        
        Testcase for BUG#41597
     @ sql/sql_acl.cc
        Bug #41597 - After rename of user, there are additional grants when 
                     grants are reapplied.
        
        Fixed handle_grant_struct() to update the hash key when the user is renamed.

[20 Oct 2009 6:18] 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/87399

2818 Satya B	2009-10-20
      Fix for Bug #41597 - After rename of user, there are additional grants when 
                           grants are reapplied.
      
      
      After renaming a user and trying to re-apply grants results in additional
      grants.
      
      This is because we use username as part of the key for GRANT_TABLE structure.
      When the user is renamed, we only change the username stored and the hash key
      still contains the old user name and this results in the extra privileges
      
      Fixed by rebuilding the hash key and updating the column_priv_hash structure
      when the user is renamed
     @ mysql-test/r/grant3.result
        Bug #41597 - After rename of user, there are additional grants when 
                     grants are reapplied.
        
        Testcase for BUG#41597
     @ mysql-test/t/grant3.test
        Bug #41597 - After rename of user, there are additional grants when 
                     grants are reapplied.
        
        Testcase for BUG#41597
     @ sql/sql_acl.cc
        Bug #41597 - After rename of user, there are additional grants when 
                     grants are reapplied.
        
        Fixed handle_grant_struct() to update the hash key when the user is renamed.
        Added to set_user_details() method to GRANT_NAME class

[20 Oct 2009 7:17] Satya B

patch queued to mysql-5.0-bugteam, mysql-5.1-bugteam and mysql-pe

[27 Oct 2009 8: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/88256

3200 Sergey Vojtovich	2009-10-27 [merge]
      Null merge an addition to fix for BUG#41597.

[27 Oct 2009 9:03] 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/88264

3694 Sergey Vojtovich	2009-10-27 [merge]
      Null merge an addition to fix for BUG#41597.

[4 Nov 2009 9:16] Bugs System

Pushed into 5.0.88 (revid:[e-mail]-20091104091355-hpz6dwgkrfmokj3k) (version source revid:[e-mail]-20091027110258-maicj286kguiwy5t) (merge vers: 5.0.88) (pib:13)

[4 Nov 2009 9:25] Bugs System

Pushed into 5.1.41 (revid:[e-mail]-20091104092152-qz96bzlf2o1japwc) (version source revid:[e-mail]-20091103162305-08l4gkeuif2ozsoj) (merge vers: 5.1.41) (pib:13)

[11 Nov 2009 6:52] Bugs System

Pushed into 6.0.14-alpha (revid:[e-mail]-20091110093407-rw5g8dys2baqkt67) (version source revid:[e-mail]-20091109080109-7dxapd5y5pxlu08w) (merge vers: 6.0.14-alpha) (pib:13)

[11 Nov 2009 6:59] Bugs System

Pushed into 5.5.0-beta (revid:[e-mail]-20091109115615-nuohp02h8mdrz8m2) (version source revid:[e-mail]-20091105092041-sp6eyod7sdlfuj3b) (merge vers: 5.5.0-beta) (pib:13)

[24 Nov 2009 20:44] Paul DuBois

Noted in 5.0.88, 5.1.41, 5.5.0, 6.0.14 changelogs.

After renaming a user, granting that user privileges could result in
the user having additional privileges other than those granted.

[18 Dec 2009 10:38] Bugs System

Pushed into 5.1.41-ndb-7.1.0 (revid:[e-mail]-20091218102229-64tk47xonu3dv6r6) (version source revid:[e-mail]-20091218095730-26gwjidfsdw45dto) (merge vers: 5.1.41-ndb-7.1.0) (pib:15)

[18 Dec 2009 10:54] Bugs System

Pushed into 5.1.41-ndb-6.2.19 (revid:[e-mail]-20091218100224-vtzr0fahhsuhjsmt) (version source revid:[e-mail]-20091217101452-qwzyaig50w74xmye) (merge vers: 5.1.41-ndb-6.2.19) (pib:15)

[18 Dec 2009 11:09] Bugs System

Pushed into 5.1.41-ndb-6.3.31 (revid:[e-mail]-20091218100616-75d9tek96o6ob6k0) (version source revid:[e-mail]-20091217154335-290no45qdins5bwo) (merge vers: 5.1.41-ndb-6.3.31) (pib:15)

[18 Dec 2009 11:23] Bugs System

Pushed into 5.1.41-ndb-7.0.11 (revid:[e-mail]-20091218101303-ga32mrnr15jsa606) (version source revid:[e-mail]-20091218064304-ezreonykd9f4kelk) (merge vers: 5.1.41-ndb-7.0.11) (pib:15)