OpenBSD Journal

MySQL security concerns

Contributed by jose on from the secure-data dept.

dawg asks:
"I installed the mysql-server-3.23.55 package on a standard install of 3.2. I followed the post install instructions from the local shell but I kept getting acess denied errors. They either claimed that the root user didn't have access to the db or that the localhost was not authorized.

I know that I probably have missed something stupid but my concern is as follows: when I access the mysql server module from webmin as root (yeah I know that's not a good idea but this is a test machine), I get right in and can administer anything. The point being that I have full access to mysql from a remote computer through webmin using the same credentials that get denied at the local shell.

That doesn't seem good to me. How can I possibly have more access from a remote computer?

Anyway, if anyone has any thoughts on what could cause the root user/localhost denied problems, they would be much appreciated:

  • release version of 3.2
  • release version of mysql 3.23.55 package
  • hosts file has both localhost and hostname
  • able to set password on root user and authorize ALL incoming hosts from webmin
  • able to start mysqld but unable to access any db functions, including the admin program from the local machine
Thanks in advance :-)"
I have to admit, running databases securely isn't something we talk about much here. I was looking to post this article from SecurityFocus in the coming few days, so now is as good a time as any. Anyone have any good recipes for securing their databases they wish to share?

(Comments are closed)

  1. By Michael Anuzis () on

    Hmmm, I'm no MySQL guru, but I think I have a good enough handle on its system of allowing access to say it's all about how you set it up.

    Yea it may seem funny to allow full access for root from some remote location and not allow it from the localhost, and I would agree, but that's apparently how it was set up and it *is* do-able.


    Setting up the initial MySQL privileges. You also may be interested in the topic directly below it, "Adding New Users to MySQL" as you can see in the syntax there it explicitly shows how you allow from *only* remote locations, or only localhost, etc.


    1. By dawg () on

      I follow your logic but I can't even get to the >mysql prompt

      the mysql documentation basically says that the permissions are wide open initially after install which is why the first thing you do is reset the root password etc. But if the system refuses to allow a connection from the local host, I can't get to the >mysql prompt to make any changes to the priveledge table.

      The fact that the initial priveledges for the db server are quite liberal and I still can't access the server at the local computer leads me to believe there is some obsd setting that is preventing such action.

    2. By dawg () on

      I just went into webmin (since that's the only way this thing lets me in) and edited the priveledge table as follows:

      -all access
      -all tables
      -from any host

      Now when I go to the local computer, I can access the >mysql prompt using:

      mysql -u root -p

      and I can see the DBs

      But when I use the -h option and specify either localhost or the computer's actual hostname, I get denied access????

      btw, I have reinstalled obsd and mysql from scratch and have the same issue.

      So now, I can access the db server if I simply specify the user but as soon as I specify any host info, I get the hand.

      1. By Anonymous Coward () on

        check your nameservers and at least outputs of
        "host localhost" and "host 127.1" and same way for every connection you want to make

      2. By Nick () on

        If you don't specify a "-h localhost", it uses a unix socket instead of a tcp connection. Maybe you're running mysql with --skip-networking or are blocking access to its port with pf? Remember, webmin runs on the local machine, so it is also able to connect with a unix socket (I don't know if it does, but it would make sense) -- just because you are connecting from over the network with a web-browser doesn't mean the webmin daemon is talking to the mysql daemon over the network also. Can you telnet to localhost on mysql's port, and at least get an open connection? If not, no amount of access changes in the database are going to allow "mysql -u root -h localhost -p" to work.

  2. By jtorin () on

    The interested may take a look at:

    From the abstract:
    FiLDB: An Architecture for Securely Connecting Databases to the Internet
    Today more and more Information systems exist and they contain more and more information. Many information systems contain information about people that is secret or sensitive. Such information should not be allowed to leak from a database. This problem grows more and more as databases are made available via the Internet.

    There have been a number of publicised occasions where hackers have passed security barriers and got information that is not intended to be publicly available. There have also been cases where the administrators of systems have made mistakes, so that classified information was published on the Internet.

    The FiLDB architecture uses existing technology together with new components to provide an environment in which databases can be connected to the Internet without losing security. Two databases, with physical separation between them, are used as a security measure. Secret information is stored only in an internal database, which is separated from the Internet. An external database contains information which is to be used from the Internet, and hence sensitive information is not stored in this database.

    1. By Anonymous Coward () on

      do masters papers always say "more and more" 3 times in 4 sentences?

      1. By jtorin () on

        They do if english isn't your native language... :-)

        1. By Anonymous Coward () on

          Ahh understandable! Sorry =)

  3. By Anonymous Coward () on

    try: mysql -u root -h localhost -p

    then "select host,user from mysql.user"
    to see the user table

    1. By dawg () on

      when I do that it prompts for a password and gives:

      error 1130: host 'hostname' is not allowed to access this mysql server.

      and again, my 'hostname' is in the hosts file.

      I would check the host table in mysql but I can't even get in from the local host! argghh

  4. By janus () janus at errornet dot de on

    an OpenBSD issue, or?

    Try `mysql -h127.0.0.1 -uroot -p'.

    1. By dawg () on

      nope, same problem. all it did was resolve to localhost and gave me the same answer:

      error 1130: host localhost is not allowed to access this mysql server

      1. By janus () janus at errornet dot de on

        It _is_ a difference if you use localhost or! because is allowed in every case, even if your /etc/hosts don't match the hosts in mysql.user.
        Was only an idea.

  5. By Anonymous Coward () on

    I've spent much time debugging mysql acl stuff due to not flushing privileges. Hopefully this clears the problem up.

  6. By dawg () on

    I don't know if anyone made the connection yet based on my reply to Michael Anuzis above but in essence I may have tripped upon something.

    -I accessed the server using webmin as root

    -I was able to load the mysql admin module through webmin

    -When I looked at the priveledge table for mysql, the root user was not listed at all

    -The above was what kept me from accessing the db admin tool from the local computer

    -Why was I able to access the db through webmin if the root user was not in the priveledge table?!?!

    -Through the webmin tool, I added the root user to the priveledge table with full access to any db from any host

    -I was then able to access any db locally

    There seems to be something about how webmin and obsd work together that allowed me to add myself to a db priveledge table remotely that I couldn't even access at the local computer.

    Keep in mind that I couldn't even have accessed the server remotely through webmin if I didn't know the root password but the above just doesn't sit right in my stomach.

    1. By Christopher () on

      Perhaps webmin is using mysql-admin instead of the mysql client? I've never used it, but I believe mysql-admin can be used to do things like add users and set passwords, so it's a complete guess.

      The other thing I seem to remember, not when I installed on OpenBSD awhile back, but when someone else did the initial install on red hat, was that I had to build a database before I could login.

  7. By Anonymous Coward () on

    with default privs:

    # mysql -h localhost -u root

    if you get access denied, you arent running the default privs, and should reset to them.

    afterwards, i suggest deletion of all entries in mysql.users and re-addition based on strict need credentials.

    all of this is basic mysql know-how. there's no special security problem with mysql and openbsd 3.2 (which the article subject suggests), and unless I'm missing something, this really has nothing todo with OpenBSD at all.

    1. By Anonymous Coward () on

      and make it work without webmin before messing with it inside of webmin (that stuff creeps me out - personal opinion). it really sounds like you need to setup the default (root, no password) mysql database and try again.

  8. By Chisara () on

    after pkg_add I do the following
    safe_mysqld &
    This will kick up the server and then
    /usr/local/bin/mysqladmin -u root -p password mynewpasswd (Enter)
    Then it will prompt for the current password of the root user which after an clean install should be empty, so when it gives the Enter password: prompt just press enter.
    This should have changed the password for the root account from empty to mynewpasswd, and with that password change you should be able to login immediately into the database using the mysql command.
    mysql -u root -p (enter)
    it will then prompt you for a password using the Enter password: prompt, here fill in your mynewpasswd and press enter.

    If you interpreted the install instructions ike I did the first time you are not able to login or change your password, hope it helps you

  9. By Peter H. Coffin () on

    Blow the package away, put it back in, and DON'T FORGET to FLUSH PRIVILEGE; after makeing changes to the user table.

Latest Articles


Copyright © - Daniel Hartmeier. All rights reserved. Articles and comments are copyright their respective authors, submission implies license to publish on this web site. Contents of the archive prior to as well as images and HTML templates were copied from the fabulous original with Jose's and Jim's kind permission. This journal runs as CGI with httpd(8) on OpenBSD, the source code is BSD licensed. undeadly \Un*dead"ly\, a. Not subject to death; immortal. [Obs.]