Why ALL PRIVILEGES ON `db`.* gives access to all databases?

Question

I've installed Ubuntu 12.10(quantal) and mysql-server-5.5.

When I execute:

drop user ''@'localhost';
create database `redmine`;
grant all on `redmine`.* to 'redmine'@'localhost';

User redmine@localhost can access not only redmine database, but also other databases.

How do I prevent him from accessing any database except redmine?

P.S. I can't reproduce this on other distributions. What is a proper behaviour? Is this a bug?

UPDATE1:

mysql> show grants;
+--------------------------------------------------------------+
| Grants for redmine@localhost |
+--------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'redmine'@'localhost' |
| GRANT ALL PRIVILEGES ON `redmine`.* TO 'redmine'@'localhost' |
+--------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> use test;
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| x |
+----------------+
1 row in set (0.00 sec)

Answer 1

The grants you gave on the redmine database have nothing to do with access to the test database.

The problem is the way mysql was installed. It allows full access to the test database. It is not a bug. I wrote a past post (Question and Answer) on why anybody can access a test database and how to fix it:

• MySQL : Why are there "test" entries in mysql.db? (Question)
• MySQL : Why are there "test" entries in mysql.db? (Answer)

In essence, the problem is the table mysql.db. It contains entries that allow everyone and his grandmother to do anything in a test database or any database whose first five characters are test_. Removing those entries with DELETE FROM mysql.db WHERE LEFT(db,4)='test'; will immediately solve your particular problem.

I have other posts on how to thoroughly strip anonymous users from mysql.user and why

• Cannot drop anonymous user from mysql.user
• mysqladmin user account not secure?
• MySQL error: Access denied for user 'a'@'localhost' (using password: YES)
• Grant table privileges to a user connecting from any host

Please read these carefully because my past answers will show you things like

SELECT USER(),CURRENT_USER();

This will break down the difference between how you logged in and how mysqld allowed you to login.

Answer 2

You can verify by connecting using 'redmine'@'localhost' and see how many databases by executing this query:

Show databases;

Also, you can execute another query to see exactly what are privileges given to 'redmine'@'localhost':

show grants for 'redmine'@'localhost';