Home / server / Questions / 980224
In Process
Collin Fox
Asked: 2019-08-22 14:47:48 +0800 CST

If I have user/host pair "root@<MyMachineName>", is it necessary to keep "root@localhost" in the mysql host/users table? (multi-part Q)

  • 772

On computer "dexwin10", the user@host pair is as follows:

root@dexwin10 

root@% 

root@localhost

When people install our software, we want them to be able to access the database from computers other than just their own. This tells me localhost should not be given as the hostname, but I'm unsure which of the other two to use.

Questions:

1A.) Is root@dexwin10 the same as root@localhost if my computer's name is dexwin10? Or does root@dexwin10 mean I can only login/access the database from the machine dexwin10, which would make sense since you can only login to root@localhost from the localhost.

B.) If I'm supposed to use root@dexwin10 and not root@localhost, does this mean root@localhost can be deleted? Or do I need localhost present to prevent problems?

2.) Should I specify the hostname "%" during our software's installation where it asks us for the hostname containing the database we wish to use, or should I enter "dexwin10"? What could be some side effects of entering just "%" as the hostname?

3.(optional) if root@(MachineName) and root@% are the same, why doesn't the MySQL installer make the default host whatever your computer's name is, and not some seemingly random string called "localhost"?

I tried to figure out why there were 3 hosts for root (one with my computer name, one with %, and one with localhost) and all the answers are talking about the default mysql users/hosts. These are not the default users.

mysql

1 Answers

  1. "localhost" is a special name. name@localhost implies a "socket" connection. That can be used only from a client on the same machine as the mysql server.

    Anything else implies a TCP/IP connection, which may be to a different machine.

    When validating a user, the user's name and origin are used to lookup a row in the GRANT tables (mysql.user, etc). That is, if you are coming in via TCP/IP, no localhost entry is looked at. And vice versa.

    The TCP/IP address for "the current machine" is 127.0.0.1. So...

    mysql -h localhost  -- uses a socket and checks `localhost` entries
mysql -h 127.0.0.1 -P 13306  -- uses TCP/IP on same machine
                        (and a non-standard Port)

    A somewhat 'secure' set of GRANTs would include:

    GRANT ... TO root@localhost ...;  -- you have to get into the machine to use it
_no_ GRANT ... TO root@hostname ...;  -- don't allow access from elsewhere
GRANT ... ON dbname.* TO user@hostname ...;  -- allow a user into one database from anywhere

    Using % (or other wild card specifications) for hostname exposes the server to attacks from arbitrary servers. Using IP addresses in place of hostnames is slightly more secure.

    Also note:

    CREATE USAGE ON *.* TO root@'%';

    without any GRANTs effectively lets 'root' in from anywhere, but gives him no permissions. It is sort of a "keep out" sign.

    Take a look at

    SELECT * FROM mysql.user;

    and keep in mind that N mean "not allowed". Probably you will see all Ns for user=root host=%. For user=root host=localhost, you will probably see all Ys, including Grant_priv, which comes from WITH GRANT OPTION.

    While I am rambling on, REVOKE is half-baked. You can "revoke" only exactly what was previously GRANTed. You cannot give lots of permissions, then remove a subset.

    • 0