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]'.
