Strange MySQL permissions problem - MySQL

[JR]

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

[Giuseppe Maxia]

JR wrote:

> 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

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
_|
[url]http://datacharmer.org/[/url]

[JR]

Giuseppe Maxia wrote:

> JR wrote:

>> 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

>
> 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
>

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

JR