Newbie: User and Host question

Today on MySql Forums, there was a question in the newbie section about two users — I have a doubt on db host and db user relationship . What does this mean ?

name host
tom %

New DBAs are often confused by the quirky methods of authentication that MySQL uses. Heck, extremely experienced MySQL DBAs can get confused.

From the manual, 6.2.4. Access Control, Stage 1: Connection Verification

When you attempt to connect to a MySQL server, the server accepts or rejects the connection based on your identity and whether you can verify your identity by supplying the correct password. If not, the server denies access to you completely. Otherwise, the server accepts the connection, and then enters Stage 2 and waits for requests.

Your identity is based on two pieces of information:

The client host from which you connect

Your MySQL user name

So the first user, Tom, is allowed to connect from ‘%’ and ‘%’ is a wildcard for any system. And the second user is only allowed to connect from a host with the address ‘’. Usually systems have a network loop-back (think short circuit) assigned to and uses that for its own traffic internally1.

Also from the same manual page (abbreviated) to provide a little more clarity.

The following table shows how various combinations of Host and User values in the user table apply to incoming connections.

Host Value User Value Permissible Connections

‘’ ‘fred’ fred, connecting from

‘’ Any user, connecting from

‘%’ ‘fred’ fred, connecting from any host

‘%’ Any user, connecting from any host

‘’ ‘fred’ fred, connecting from any host in the domain

It helps to occasionally re-read the The MySQL Access Privilege System of the manual to help remember how users get into the system as well as the edge cases. It is all too easy to set up multiple users with the same user name value but different privileges depending on where they connect.

And thanks to all who take the time to answer questions on the forums!

  1. Hugh simplification used here for brevity.

Published by


Dave Stokes is a MySQL Community Manager for Oracle and previously was the MySQL Certification Manager for MySQL AB and Sun Microsystems. He has a MBA from the University Of San Diego.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s