Description:
A user has few privileges on one database. This database is dropped by other. The user gains more privileges on others databases.
How to repeat:
campos@campos-laptop:~$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 19
Server version: 5.0.38-Ubuntu_0ubuntu1.1-log Ubuntu 7.04 distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show grants for 'essai';
+------------------------------------------------------------------------------------------------------+
| Grants for essai@%
|
+------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'essai'@'%' IDENTIFIED BY PASSWORD
'*447525732AA8B41C297663C4439537313E3CFF72' |
| GRANT SELECT, CREATE ON `essai`.* TO 'essai'@'%'
|
+------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| essai |
| mysql |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysql> select @@version;
+------------------------------+
| @@version |
+------------------------------+
| 5.0.38-Ubuntu_0ubuntu1.1-log |
+------------------------------+
1 row in set (0.00 sec)
mysql> exit
Bye
campos@campos-laptop:~$ mysql -u essai -pessai
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 20
Server version: 5.0.38-Ubuntu_0ubuntu1.1-log Ubuntu 7.04 distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> use essai;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+-----------------+
| Tables_in_essai |
+-----------------+
| essai |
+-----------------+
1 row in set (0.00 sec)
mysql> create table essai1 (c1 int) ;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from essai1;
Empty set (0.00 sec)
mysql> insert into essai1 values (1);
ERROR 1142 (42000): INSERT command denied to user 'essai'@'localhost' for table 'essai1'
mysql> exit
Bye
It is what we expect.
But
As root :
campos@campos-laptop:~$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 21
Server version: 5.0.38-Ubuntu_0ubuntu1.1-log Ubuntu 7.04 distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> drop database essai;
Query OK, 2 rows affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| buuuz01 |
| mysql |
| test |
+--------------------+
4 rows in set (0.00 sec)
mysql> show grants for 'essai';
+------------------------------------------------------------------------------------------------------+
| Grants for essai@%
|
+------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'essai'@'%' IDENTIFIED BY PASSWORD
'*447525732AA8B41C297663C4439537313E3CFF72' |
| GRANT SELECT, CREATE ON `essai`.* TO 'essai'@'%'
|
+------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> exit
Bye
As user essai
campos@campos-laptop:~$ mysql -u essai -pessai
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 23
Server version: 5.0.38-Ubuntu_0ubuntu1.1-log Ubuntu 7.04 distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> use essai;
ERROR 1049 (42000): Unknown database 'essai'
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| essai |
| log |
| test |
+----------------+
3 rows in set (0.00 sec)
mysql> create table essai1 (c1 int);
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| essai |
| essai1 |
| log |
| test |
+----------------+
4 rows in set (0.00 sec)
mysql> insert into essai1 values(1);
Query OK, 1 row affected (0.00 sec)
mysql> show grants for essai;
+------------------------------------------------------------------------------------------------------+
| Grants for essai@%
|
+------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'essai'@'%' IDENTIFIED BY PASSWORD
'*447525732AA8B41C297663C4439537313E3CFF72' |
| GRANT SELECT, CREATE ON `essai`.* TO 'essai'@'%'
|
+------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> select * from essai1;
+------+
| c1 |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
mysql> exit
Bye
Suggested fix:
related to other bug :
MySQL do not drop the privileges of the user when dropping a database where he has privileges
Drop the privileges attached to a database when it is dropped.
Description: A user has few privileges on one database. This database is dropped by other. The user gains more privileges on others databases. How to repeat: campos@campos-laptop:~$ mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 19 Server version: 5.0.38-Ubuntu_0ubuntu1.1-log Ubuntu 7.04 distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> show grants for 'essai'; +------------------------------------------------------------------------------------------------------+ | Grants for essai@% | +------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'essai'@'%' IDENTIFIED BY PASSWORD '*447525732AA8B41C297663C4439537313E3CFF72' | | GRANT SELECT, CREATE ON `essai`.* TO 'essai'@'%' | +------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | essai | | mysql | | test | +--------------------+ 5 rows in set (0.00 sec) mysql> select @@version; +------------------------------+ | @@version | +------------------------------+ | 5.0.38-Ubuntu_0ubuntu1.1-log | +------------------------------+ 1 row in set (0.00 sec) mysql> exit Bye campos@campos-laptop:~$ mysql -u essai -pessai Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 20 Server version: 5.0.38-Ubuntu_0ubuntu1.1-log Ubuntu 7.04 distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> use essai; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +-----------------+ | Tables_in_essai | +-----------------+ | essai | +-----------------+ 1 row in set (0.00 sec) mysql> create table essai1 (c1 int) ; Query OK, 0 rows affected (0.01 sec) mysql> select * from essai1; Empty set (0.00 sec) mysql> insert into essai1 values (1); ERROR 1142 (42000): INSERT command denied to user 'essai'@'localhost' for table 'essai1' mysql> exit Bye It is what we expect. But As root : campos@campos-laptop:~$ mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 21 Server version: 5.0.38-Ubuntu_0ubuntu1.1-log Ubuntu 7.04 distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> drop database essai; Query OK, 2 rows affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | buuuz01 | | mysql | | test | +--------------------+ 4 rows in set (0.00 sec) mysql> show grants for 'essai'; +------------------------------------------------------------------------------------------------------+ | Grants for essai@% | +------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'essai'@'%' IDENTIFIED BY PASSWORD '*447525732AA8B41C297663C4439537313E3CFF72' | | GRANT SELECT, CREATE ON `essai`.* TO 'essai'@'%' | +------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> exit Bye As user essai campos@campos-laptop:~$ mysql -u essai -pessai Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 23 Server version: 5.0.38-Ubuntu_0ubuntu1.1-log Ubuntu 7.04 distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> use essai; ERROR 1049 (42000): Unknown database 'essai' mysql> use test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | essai | | log | | test | +----------------+ 3 rows in set (0.00 sec) mysql> create table essai1 (c1 int); Query OK, 0 rows affected (0.00 sec) mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | essai | | essai1 | | log | | test | +----------------+ 4 rows in set (0.00 sec) mysql> insert into essai1 values(1); Query OK, 1 row affected (0.00 sec) mysql> show grants for essai; +------------------------------------------------------------------------------------------------------+ | Grants for essai@% | +------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'essai'@'%' IDENTIFIED BY PASSWORD '*447525732AA8B41C297663C4439537313E3CFF72' | | GRANT SELECT, CREATE ON `essai`.* TO 'essai'@'%' | +------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> select * from essai1; +------+ | c1 | +------+ | 1 | +------+ 1 row in set (0.00 sec) mysql> exit Bye Suggested fix: related to other bug : MySQL do not drop the privileges of the user when dropping a database where he has privileges Drop the privileges attached to a database when it is dropped.