Which MySQL users are necessary?

Question

My MySQL server has a number of strange users which I did not add. Are these all necessary?

'root'@'127.0.0.1'
'root'@'localhost'
'root'@'SERVERNAME'
'root'@'::1'
''@'SERVERNAME'
''@'LOCALHOST'

If I remove all the root's except root@localhost, could I end up locking myself out of the database? And what is the purpose of the empty user names? They seem to just have the 'GRANT USAGE'??

Is there a difference between 127.0.0.1 as host and localhost? If I only have localhost and not 127.0.0.1, does that mean mysqlclient's that use TCP/IP instead of Unix sockets will not be able to connect?

Answer 1

1. These users seem to be the default users that were added when MySQL was installed. It is recommended that you run mysql_secure_installation after installing MySQL.

2. The empty usernames (''@'SERVERNAME') represent anonymous users. If you didn't run mysql_secure_installation or set the password, then anyone can gain access. If the anonymous user has GRANT USAGE privileges, then it basically means it has no privileges, but it's still a good idea to remove the anonymous login completely.

3. No, there's no big difference between localhost and 127.0.0.1. Whatever IP address the user is trying to login from must match the @[IP Address] part of the username. If your logging in from the same machine the MySQL server is on, then @localhost and @127.0.0.1 would match. As IVlint67 pointed out, in some installations having @localhost would not work so its better to go with @127.0.0.1.

Answer 2

I usually install with the mysql_secure_installation script MySQL is shipping with now...

---

[email protected] is the @ IP address. root@localhost is @ hostname. Ditto for the server name. And the last root is @ IPv6 address for localhost.

---

From the MySQL site:

An attempt to connect to the host 127.0.0.1 normally resolves to the localhost account. However, this fails if the server is run with the --skip-name-resolve option, so the 127.0.0.1 account is useful in that case.

The empty usernames:

Some accounts are for anonymous users. These have an empty user name. The anonymous accounts have no password, so anyone can use them to connect to the MySQL server

http://dev.mysql.com/doc/refman/5.0/en/default-privileges.html

---

And finally:

If I remove all the root's except root@localhost, could I end up locking myself out of the database?

Yes, but you can get back in

See --skip-grant-tables : https://help.ubuntu.com/community/MysqlPasswordReset

Should I?

I don't care, it's your server. If it was mine, I would secure the root accounts as is with passwords and delete the anonymous accounts unless you need them.