I'm trying to setup a MySQL database (foo) and user (bar) that will be
used to store the data for a PHPBB website. For security reasons, the
webserver and database will be on separate machines (ideally, the MySQL
server will host other databases in the future). I want to restrict the
user "bar" to only have access to database "foo".

I've setup the new user using the following command:

grant select,insert,update,delete,create,drop on foo.* to
'bar'@'webserver' identified by 'secretpassword';

When I log in as user "bar" from server "webserver" and run a "show
grants;", I get the following:

GRANT USAGE ON *.* TO 'bar'@'webserver' IDENTIFIED BY PASSWORD '12345'
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON `bar`.* TO
'bar'@'webserver'

The "GRANT USAGE ON *.*" appears to give me complete access to other
databases. For example, I can switch to another database "test" and
successfully run select.

I assume I'm doing something stupid - please can someone advise? I've
tried running a revoke on the usage, but this doesn't work. How do I
stop user bar from accessing the other databases?

I'm running MySQL Ver 14.7 Distrib 4.1.7, for redhat-linux-gnu (i386) on
Red Hat Enterprise Linux 4.0

Thanks in advance.

JR

JR wrote:
The behavior you are witnessing is due to the default privileges that apply to the test
database and any database with a name starting with "test_%".
To fix the problem, you can use an utility provided with MySQL default installation, called
mysql_secure_installation

Or you can manually issue these commands, as root

use mysql;
set password=password('put_your_password_for_root_here ')
delete from user where password='';
delete from db where db like 'test%';
flush privileges;

After that, your user 'bar' will only access the database it was assigned.

ciao
gmax

--
_ _ _ _
(_|| | |(_|>< The Data Charmer
_|
http://datacharmer.org/

Giuseppe Maxia wrote:
Giuseppe - many thanks for your quick response. I have just run the
mysql_secure_installation script and it is now working perfectly.

JR