How MySQL Authenticates Users

In this post, we’re going to take a deep into the various methods for MySQL authentication. We’re only going to focus on authentication (AuthN), check out this post to learn more about authentication (AuthN) versus authorization (AuthZ). One of the key features of Cyral is being able to map database users to users in identity providers. As such, we have reverse engineered a number of different authentication mechanisms. This post will cover what we’ve learned about MySQL’s authentication mechanism.

One of the central concepts in what we do at Cyral is authentication. That is the process of confirming that a certain user attempting to connect to a datasource is indeed who they say they are, and it’s important because it is the basis for controlling who can access data in a system. There are multiple ways to verify a user identity, but historically one of the most widely used approaches has been password based authentication. In this method, a password is set for each user in a system, and whenever a user wants to log in, they must provide this password. The system then compares the input password and checks if there is a match with the one stored for that user. If so, the user is allowed in.

The idea sounds simple enough but, over time, as security (and those trying to bypass it) evolved, these concepts grew in complexity, with database systems introducing more sophisticated authentication mechanisms to strengthen data protection. At Cyral, we strive to build a product that is compatible with the great diversity of datasources out there, so we have had the chance to explore different database wire protocols and their security aspects. In this post we’ll take a closer look at how MySQL, one of the most popular databases authenticates users.

The connection phase

When a MySQL server receives a connection request, it exchanges capabilities with the client (to determine which parts of the protocol can be used), encrypts the communication channel (if requested by the client and supported by the server) and then authenticates the user identity [1]. So we see that this connection phase is heavily dedicated to security purposes, checking if the user has access to the database and allowing the communication traffic to be exchanged in a secure fashion. In fact, the very first packet the server sends already contains data that should be used by the client to complete authentication.

The authentication mechanisms in MySQL are implemented by authentication plugins, and each user is associated with one of them. This pluggable authentication [2] scheme makes it easy to switch authentication methods or use external ones, like LDAP. As of MySQL 8.0, there are many built-in authentication plugins, and Cyral works with them to expand the level of security in the initial authentication phase.

MySQL authentication plugins

A simple example to start digging into the MySQL authentication plugins is the mysql_clear_password plugin. Using this, the clients will send their passwords without any hashing or encryption to the server. While this is clearly not secure by itself, since the system would be vulnerable to sniffing attacks [3], it is actually a requirement for some server-side plugins like PAM, LDAP, and to use identity providers with Cyral. How can you use this plugin and still guarantee some level of security? For that, we rely on communication channel encryption, which the client and server should agree to use during their initial exchange. Using TLS to encrypt the communication channel is not mandatory in MySQL, but for the mysql_clear_password in particular, we decided to enforce this use in our product. This enforcement is aided by having support for asymmetric TLS, i.e., using Cyral, a MySQL client is able to use an encrypted connection channel to send data, even if the server does not support encryption.

A safer approach, compared to using plain text passwords, is using hash functions [4]. Now, instead of storing and sending a password in plain text, we use it as an argument to a mathematical function that returns a fixed length string of text, the hash, which is stored. A client that wants to connect to the database must send to the server the password hash that is generated by using the correct password. A malicious actor who gains access to the hash will not be able to gain access to the system because hash functions are one-way (they give you a hash from a password, but not a password from a hash). This idea has been a constant in the MySQL authentication plugins that came after MySQL 4.1, however, it was not enough by itself to stop hackers. If they get access to the hash, they could then use a pre-computed list of hashes for common words (rainbow table) and compare to the password hash, or even reverse the hash using brute force, by testing out different character combinations as arguments to the hash function.

Another technique was introduced to improve the security provided by hash functions, and consists in the addition of a usually small random piece of data – the salt – that is attached to the password before hashing. Since the salt is unique for each user, the generated hashes will be different even if two users use the same password, which prevents the rainbow tables attacks and slows down someone trying to guess a password via brute force. Using salts when exchanging or storing password hashes has been the base for the most recent authentication plugins in MySQL, and with time, the choice of hash functions changed to include more secure algorithms: mysq_native_password uses SHA1 and both sha256_password and caching_sha2_password use SHA256. The default plugin for MySQL 8 is caching_sha2_password which focuses on performance. This plugin implements a server-side cache to store user credentials upon successful authentication.

Security is never too much!

These SHA256 based plugins surely represent a great improvement in terms of security, and the protocol reflects that as well. A full authentication (when credentials are not cached) happens differently according to whether the communication channel is encrypted or not. If it is, the client safely sends the password in clear text, but if it isn’t, then the client must first obtain and use the server public key to encrypt the password and send that instead [5]. This has one implication to using asymmetric TLS, because the client and the server would be expecting different things at a given point in the protocol, so Cyral must intermediate the communication between the endpoints. This intermediation was also necessary for us to support identity providers, such as Okta. When these are in use, there are actually two authentication processes going on: (i) the user credentials must be authenticated against the identity provider; and (ii) the credentials returned by the identity provider must be authenticated against the server. The main key to how we handle these situations is maintaining a state for the connection phase as perceived by both the client and the server, and keeping track of what each of them are expecting to send or receive from each other at any given point while we haven’t completed authentication.

Our product keeps the MySQL native authentication as it is and works with it to strengthen security where there’s room for it. Because malicious hackers are out there and their motivation does not seem to decline, we are working to keep them unsuccessful. In terms of authentication, this reflects in many of our features, such as user access grants, which limit who can log into the database and for how long, and multi factor and federated authentication, providing an extra layer of authentication to be attached to the native database one.

References