MySQL Bugs: #10979: USE does not refresh db privilege changes if the same db is used in command.

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:	Windows (Windows 2000 Server)
Assigned to:	Alexander Barkov	CPU Architecture:	Any

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.

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>

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 ?

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.

ok, thanks.

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

ok to push

Pushed into 5.0-kt.
TODO: merge into 5.0 common and 5.1.

Merged to 5.0.25 and 5.1.12

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.