Bug #10979 USE does not refresh db privilege changes if the same db is used in command.
Submitted: 31 May 2005 9:36 Modified: 9 Aug 2006 13:10
Reporter: Disha Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.5 Beta OS:Microsoft Windows (Windows 2000 Server)
Assigned to: Alexander Barkov CPU Architecture:Any

[31 May 2005 9:36] Disha
Description:
The USE command does not refresh the database level privilege changes if the same database name is used in the USE command.

How to repeat:
1. Start the MySQL client and connect to the database with admin user (SESSION1).
2. Set the delimiter to //
3. Create a user u1 and a database db1.

   create user u1 identified by 'test'//
   create database db1//
   use db1//

4. Create a table in db1.

   create table tb1(f1 int)//

5. Open another MySQL client and connect to the server using u1 user account. We will call this SESSION2.
6. From session 1 grant all privileges on tb1 to u1.

   grant all on db1.tb1 to u1//

7. From SESSION2 verify that the following commands work properly.

   use db1//
   select * from tb1//

8. Now from SESSION1 grant all privileges on db1 to user u1.

   grant all on db1.* to u1//

9. From SESSION2 try the following commends.

   create table t2(f2 int)//
   use db1//
   create table t3(f3 int)//

Expected Results: 
1. The creation of table t2 should give error and the creation of t3 should succeed. 

Actual Results: 
1. The USE command does not refresh the database privileges. Both table creation commands fail.

Additional information:
1. If we select some other database and then select the original database again with the USE command then the changed privileges take effect.
[31 May 2005 9:48] Vasily Kishkin
Tested on Win 2000 Sp4, MySQL server 5.0.7 beta

mysql> create table t2(f2 int)//
ERROR 1142 (42000): CREATE command denied to user 'u1'@'localhost' for table 't2'
mysql>    use db1//
Database changed
mysql>    create table t3(f3 int)//
ERROR 1142 (42000): CREATE command denied to user 'u1'@'localhost' for table 't3'
mysql>
[31 May 2005 11:40] Sergei Golubchik
Why do you think that USE should "refresh the database privileges" ?
Is it the manual that made you believe that this is the intended behaviour ?
Where in the manual we say it ?
[31 May 2005 12:33] Disha
The following is the link:
"http://dev.mysql.com/doc/mysql/en/privilege-changes.html"

On the page specified in the above link the second bullet states as follows:

"Database privilege changes take effect at the next USE db_name statement."

The expected result is based on the above information.
[31 May 2005 16:21] Sergei Golubchik
ok, thanks.
[24 May 2006 11:15] 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/6826
[28 Jun 2006 10:52] Sergey Glukhov
ok to push
[7 Jul 2006 12:45] Alexander Barkov
Pushed into 5.0-kt.
TODO: merge into 5.0 common and 5.1.
[7 Aug 2006 6:30] Alexander Barkov
Merged to 5.0.25 and 5.1.12
[9 Aug 2006 13:10] Jon Stephens
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix in 5.0.25 & 5.1.12 changelog; updated section of Manual cited by submitter per comments from Monty.