weired behavior of mysql

Hello,

I’m testing some (selfwritten) installer scripts and I’d like to test it on several linux systems.
From my script, I create a database and a user, which should have no rights but to the objects created on that database.

I create that user with “grant usage on <dbname>.* to ‘user’@’%’ identified by ‘…’;”

After that, the user exists, but is not allowed to login with mysql client (same procedure works fine on debian systems). At first thought, I supposed, that I did a mistake on database setup, but then I realized, that I could connect from a different workstation using that user.

To me it looks very strange, that that user sees information_schema and test, but not the database, I used at user creation.

From my script I check success of db and user creation by using the created user for connection and exec a ‘show databases’, which should show the new created db.

What am I missing with mysql on Suse systems?
Is my expectation wrong, did I make a mistake on database setup or what do I have to change to validate success of my script?

kind regards

Reinhard

Unless you have logged off since, you need to use
flush privileges;
after any grant to update the privileges.

Is that missing from your script?

If you look at the mysql documentation, you will see that grant usage means no privileges at all.

Hello,

thank you for your attention and assistance!

Meanwhile I tried to grant usage . but it did not change anything (and yes - I tried flush privileges).
I also don’t understand network setup of Suse systems.

When I run a “ping $(hostname -f)” I’ll get the 127.0.0.2 - so how do I have to setup networking the get the ip of eth0 (without coding eth0).

The point is, I’d like to setup a java database application and afaik java does not work with localhost or unix sockets.
Again - can anybody please explain me, why it is possible to connect to mysql of Suse system from another workstation, but not from the database host itself?

kind regards

Reinhard

grant … to user@’%’ doesn’t cover localhost. You need an extra grant … to user@localhost to allow connection from localhost.

And you haven’t paid any attention to what I wrote about grant usage. It’s essentially useless for what you want to do. Generally you want grant select if you are just reading, and grant all if you are modifying.

Hi,

thanks again for your support.

That’s a key information! Great. Didn’t know that.

That’s not true. Believe me! I read every word of your post.
I did not mention it, cause it is not related to my problem - or at least, I did not understand the relevance of it.

I (later in the script) grant other rights at table creation like ‘select, insert, update, delete’ to that user.

Right after database creation I only grant usage, cause I thought, this would cover the right to connect to that database (there is no login-right, is it?) and I’d like to verify user creation.

kind regards

Reinhard

Hello,

adding a grant for localhost works so far.

But how can I connect to local mysql-server using a port connection and not ‘localhost’?

On other linux I use $(hostname -f) as hostname, but there I can change /etc/hosts manually.
When I try this on Suse, hostname -f will fail and using yast I don’t know, how to setup networking right.

I have a nameserver, but when I use the domain of the nameserver for network setup, lookup will fail.
If I use a wrong domain-name for network setup and on connecting to mysql use the right domain name, things will work.
But I don’t know how to figure out the right domain name from script with such a weired setup.

Any hint is welcome.

kind regards

Reinhard

Connect to 127.0.0.1.

Did you ever tried a java jdbc-connection?

Do you think, if that would be an option, I spent that many time to find out the “real” IP-address of an host? I have a Testunit where I can play with all parameters. May be I’m too stupid, but I was not able to connect to localhost or 127.0.0.1 - ever!

So it is burned into my mind, that java needs a real IP and for so the name of that IP.

On Non-Suse Systems (even Windows) its quite easy - I only have to change /etc/hosts so that the real hostname of that machine is resolved to the real IP address.

As written before, I’ll check that ip with “ping -c 1 $(hostname -f)” and if that IP-address does not start with 127, the setup of the machine is ok.

On Suse things don’t work that way.
At first step I manually changed /etc/hosts the usual way, but then “hostname - f” fails.
OK - so I thought, I have to use yast to change the names.
But with yast, I’m not able to get an /etc/hosts without that 127.0.0.2 address and reverse resolution will always result in that address.

So if you or anybody else knows, how to setup a suse system, that “ping -c 1 $(hostname -f)” returns the real IP, I would really appreciate any hint!

kind regards

Reinhard

In MySQL the name localhost is special, it refers to the Unix socket and not 127.0.0.1. To get a TCP connection you have to use 127.0.0.1 (or any of the other 127. addresses, see below). I don’t know why your Java connector cannot handle 127.0.0.1. Generally a numeric IP address is acceptable whereever a domain name is. You could try connecting to your server with:

mysql -h 127.0.0.1 ...

to make sure that it’s listening on the loopback interface. Also if mysqld is listening on 127.0.0.1 it should also be listening on 127.0.0.2, and you can also use that. All loopback addresses are equally valid, although they may be bound to different names.

That’s right so far.

The point is, when I open mysql to tcp/ip of LAN, I have to add the parameter

bind-address = IP

to my.cnf
Doing so, connections from any other IP are rejected.
No matter, whether you have other network-cards inserted or use an address from internal adress space (127.xxx).

So having such a configuration (bind-address = 192.xxx), connections can use ‘localhost’ or the real IP.

As you already stated, localhost refers to the unix-socket and so it is not usable for java applications. TCP/IP is - but as the mysql-server is bound to a real IP, any connections with local addresses are rejected.

So I’m back at the point I started:
How can I determine and validate the real IP of current machine on Suse systems?

kind regards

Reinhard

P.S. One idea was, to use static IP address.
But when I edit network-parameters with yast and change network to static IP, it adds the 127.0.0.2 to /etc/hosts anyway.
From my point of view, that’s wrong.
If the machine is configured to have a static IP from 192-adress space, there’s no need for an additional 127.0.0.2

You seldom have to specify bind-address in my.cnf. I certainly don’t. If you comment it out, mysqld, like most services, will default to listening on all interfaces, which will include lo and eth0. That’s probably your problem.

Well, you might call it problem, I prefer calling it intention.
Most of my services are restricted as much as possible. So on a lamp system the database does not accept any tcp/ip connection at all.

I’d like the idea to be as restrictive as possible and I don’t like the idea having to tell my users, that they should open their database to the whole world.

If it is not possible, I’d prefer the recommendation not to use suse systems.
But I’d like to support suse systems the safe way too :wink:

kind regards

Reinhard

It is also a shortcoming of mysqld, AFAICT. Most other services allow you to specify multiple interfaces or addresses to listen on. If mysqld allowed that you could name only the ones you want to be active. But it isn’t catered for in mysqld, unless there are more recent developments I haven’t seen.

A couple of way to work around this: Listen on 127.0.0.1 instead of the ethernet IP address since you only want local clients to connect. This is as safe as Unix sockets. The other way is to use iptables to restrict which clients can connect.

But mysql isn’t the only software that’s deficient here. Java could use a connector that can handle Unix sockets. Here’s an effort to provide one:

https://github.com/mcfunley/juds

Hi,

thanks a lot for your time and attention!

The only shortcoming, that currently causes a problem to me, is yast with insisting on 127.0.0.2

My app is designed for distributed environments, so networking is vital.
But I want to support single-host installations too. They are not the main focus and personally I am not interested in this kind of installation, but I’d like to support it.

If you like, take a look at my project.

So thank you again for your information, that mysql separates the users for socket and tcp/ip access. That information helped me a lot.
I’ll try to change my installer for the shortcoming of yast to keep suse in the list of supported Osses.

kind regards

Reinhard