Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: weired behavior of mysql

  1. #1

    Default 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

  2. #2
    Join Date
    Jun 2008
    Location
    West Yorkshire, UK
    Posts
    3,448

    Default Re: weired behavior of mysql

    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?

  3. #3
    Join Date
    Jun 2008
    Location
    UTC+10
    Posts
    9,686
    Blog Entries
    4

    Default Re: weired behavior of mysql

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

  4. #4

    Default Re: weired behavior of mysql

    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

  5. #5
    Join Date
    Jun 2008
    Location
    UTC+10
    Posts
    9,686
    Blog Entries
    4

    Default Re: weired behavior of mysql

    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.

  6. #6

    Default Re: weired behavior of mysql

    Hi,

    thanks again for your support.

    Quote Originally Posted by ken_yap View Post
    grant ... to user@'%' doesn't cover localhost.
    That's a key information! Great. Didn't know that.

    Quote Originally Posted by ken_yap View Post
    And you haven't paid any attention to what I wrote about grant usage.
    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

  7. #7

    Default Re: weired behavior of mysql

    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

  8. #8
    Join Date
    Jun 2008
    Location
    UTC+10
    Posts
    9,686
    Blog Entries
    4

    Default Re: weired behavior of mysql

    Connect to 127.0.0.1.

  9. #9

    Default Re: weired behavior of mysql

    Quote Originally Posted by ken_yap View Post
    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

  10. #10
    Join Date
    Jun 2008
    Location
    UTC+10
    Posts
    9,686
    Blog Entries
    4

    Default Re: weired behavior of mysql

    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:

    Code:
    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.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •