MySQL Gotcha: Remote Root Access

Posted on in software

cover image for article

By default, MySQL only allows access by the 'root' account from the host running the database server ('localhost'). Often times that's perfectly adequate. There are occasions when you need remote access as the 'root' account, however. In these cases, it's helpful to be aware of one little gotcha when creating a 'root' account.

Generally speaking, the theory behind adding a 'root' account is to grant that user full access to all databases on the server. In MySQL, we do this with the GRANT ALL command:

GRANT ALL ON *.* TO 'root'@'%'
  IDENTIFIED BY '[password]'

For the most part, that works just fine. In this example, the user 'root' from any host (that's the '%' in the SQL above) is able to perform most functions on any database and table on the server. But this is where the gotcha comes in...

One thing the GRANT ALL function does not do is allow the user to grant permissions to other users. This is a fairly critical function of a 'root' account. For example, without this ability, an application installer that must add a user to a remote database server will not be able to properly install. So, when setting up our remote 'root' account, we need to make sure and give it the ability to assign permissions to new and existing users.

GRANT ALL ON *.* TO 'root'@'%'
  IDENTIFIED BY '[password]'
  WITH GRANT OPTION

Take note, this example allows remote 'root' level access from any host. This is highly insecure and should only be done with the greatest precaution. I would recommend replacing the '%' in the examples above with '[ip_address]' or '[hostname]'.

Further Reading

Slaptijack's Koding Kraken