Logging into a MySQL server is generally dead simple—supply a username, a password, and you’re all set!

There are, however, more complex use cases such as when making use of our Enterprise Authentication plugins. It’s also sometimes helpful to have a more detailed understanding of what happens “under the hood”. So I’ll attempt to lift the hood and walk you through all of the nitty-gritty details regarding exactly just what happens when you log into a MySQL server.

Firstly, there are no less than 4 “users” involved in the authentication process. And a distinction between a user id and a user account exists. And it gets more and more advanced with each release. So I thought I’d take the time to try to walk you through the MySQL authentication process and clarify once and for all the stages involved, the information exchanged at each stage and the expected outcome.

I’ll use the tools that come with the server for my examples, but I’m sure it can be extended to most of the existing clients.

It all starts with the application, e.g. the mysql command line tool.

To open a connection it calls mysql_real_connect() and supplies, among other parameters,  a user id and a password. Let’s call this user id “application user name“.

Now the client library opens a network connection to the server. It then reads the server hello packet.

The client library takes from the server hello packet the data coming from the server’s default authentication plugin, combines them with the password supplied by the application and calls the client’s default authentication plugin.

The client’s default authentication plugin uses the information to create the authentication data to be sent into the client’s hello packet, together with the application user name.

Now the server has the all it needs to begin its own authentication process:

• The application user name
• The host from which the client is connecting from. We’ll call this “application user host“
• The name of the client’s default authentication plugin
• The first batch of authentication data sent by the client authentication plugin
• The server’s user account table

The server first stores the application user name into the security context so it can later be retrieved through the USER() SQL function.

Next it needs to find the user account to authenticate against. A user account is the object created by CREATE USER or GRANT, that also can have privileges granted to it.

MySQL user accounts are a user name@host combinations. But we happen to have one at this point. We’ll call “application user” the combination of “application user name“@”application user host“.

Now the server iterates over the user accounts in “specificity order” and tries to match the application user against one of them.

Specificity order means that it starts from the most specific ones (no wildcards) and them moves towards the mixed ones (letters and wildcards) and finally scans the least specific ones, consisting of wildcards only.

This is where we need to mention the infamous anonymous account. An anonymous account is defined as having an empty user name (“”). This means that there can be several anonymous accounts : e.g. “”@localhost, “”@”%.domain.com”, “”@”%” etc.

After the matching process is done we end up with a user account of the form: <user name mask>@<host name mask>.

Since it’s a wildcard match the user account may or may not be equal to the application user.

E.g. one can expect that the application user joro@localhost will match the user account “joro”@”%”.

Of course if there was another user account “joro”@”l%” the application user “joro”@”localhost” would have matched that since it’s more specific than the “joro”@”%” user account.

What the server does next is to store the user account found into the session’s security context so it can be returned later. We’ll call this account “authentication user account“.

Since each MySQL user account defines what authentication plugin to use to authenticate to it the server now knows what server authentication plugin to call to conclude the authentication process.

If the selected server authentication plugin is compatible with the default client authentication plugin it can work with the data already submitted in the client hello packet. In this case the server just passes along the data already received to the server’s authentication plugin.

If they are not compatible then the server needs to ask the client to re-start the authentication dialog using another client authentication plugin that’s compatible with the selected server authentication plugin.

Either way, the client/server authentication plugin pair concludes the authentication process and the server plugin returns the following pieces of information to the server:

1. Result of the authentication (success or failure)
2. The user name of the external user account used by the plugin to conclude the authentication against an external repository
3. An proxy user name this session should use instead of the authentication user account name.

#1 is obvious. It’s a “yes” or “no”.

The server stores #2 into the system variable @@external_user. Since it’s not a MySQL user account nor is it a application login name it’s just for information and can be used to track the access to 3d party user directories that the plugin does. Currently @@external_user is used by the windows authentication plugin to return the windows user account name that was used (since it can be different from the supplied application user id).

Now #3 is a bit less straightforward. The MySQL server supports the concept of proxy users. The authentication plugin for the authentication user account can “redirect” the session to use the credentials of a more suitable MySQL user account based on some authentication plugin logic. This allows external selection of MySQL user accounts relevant for the external user at hand.

Why is this useful ? Imagine you want to use UNIX as a back-end to authenticate your MySQL server users. But you may have 1000s of UNIX users. You can’t define a corresponding MySQL user account for each of them ! That would defeat the purpose, since you’ll have to maintain both sets in parallel and keep them in sync.

You can of course define a wildcard account (e.g. “”@”%”) and all the application user names will match it. But this only gives you a single set of MySQL permissions (the ones defined for the wildcard account) to work with. And you definitely don’t want all of your external users to share the same MySQL permissions !

Proxy users to the rescue. You define a handful of “proxy” user accounts that carry  different permissions sets. You then make your authentication plugin return one of these when it sees that a UNIX account is a member of a certain UNIX group. Now all you have to do to make your UNIX users part of one of these groups. And they map to the relevant MySQL “proxy” user account through the magic of the proxy users support.

Note that the proxy user name defaults to the application user name. So if the plugin chooses not to submit a different one the server knows that no proxying will be used and can conclude the authentication and use the authentication user account.

Note that the proxy user name is only a user name. The application user host is reused. We’ll call the “proxy user name”@”application user host” couple “proxy user“.

The proxy user is also matched in specificity order to the list of MySQL user accounts until an account is found. We’ll call this a “proxy user account“.

Now the server has the MySQL user account to use for the session. It’s either the authentication user account or the proxy user account.

The server stores this account into the security context so that it can be returned through the CURRENT_USER() SQL function.

And, if a proxy user account is used, the authentication user account is stored into the @@proxy_user system variable.

So to recap, when a new session is created we have the following user functions and status variables:

• USER(): the application user, or a combination of the application user name and the application host name.
• @@EXTERNAL_USER: the non-MySQL related user id that was used by the plugin to authenticate. Plugin specific.
• CURRENT_USER():
  • if proxying was used this is the selected proxy user account
  • if no proxying was used this is the selected authentication user account
• @@PROXY_USER
  • if proxying was used this is the authentication user account
  • if not proxying was used it’s empty (NULL)

Now you know everything there is to know about users, logins and accounts in MySQL.