Have you ever struggled to manage a large set of users and their authentication credentials in MySQL? Wouldn't it be great if I could tell you that now you can outsource this to your favorite external identity provider? Yes, you read that right. Lets see how to do it with MySQL...

With MySQL 9.1 we introduced support for OpenID Connect (OIDC) based authentication in our Enterprise edition. Why is this important? OpenID Connect (OIDC), which is based on an open standard, helps unify the user identity across multiple applications, meaning more and more applications can through OpenID connect with MySQL.

This blog will delve into what OpenID Connect is, why it’s beneficial for MySQL, and how you can implement it in MySQL Database System. We will be using Microsoft Azure as the Identity Provider in our example.

What is OpenID Connect?

Overview

OpenID Connect is an authentication protocol built on top of the OAuth 2.0 authorization framework. It enables clients (such as web applications or mobile apps) to verify the identity of users based on the authentication performed by an external authorization server.

Benefits of OpenID Connect

Key benefits:

• It is convenient, simple to manage, secure, and portable.
• It provides a completely standardized setup.
• It is simple to implement relative to other methods and is more flexible.
• In our case the OpenID identification can be used for authentication within MySQL which we will discuss in detail.
• OpenID is gaining traction and thus in many cases organizations have the infrastructure in place, e.g. ADFS.
• MySQL Database on-prem with application in the cloud is the most common benefit as many regulations require data to be on-prem.
• OIDC streamlines the authentication process by enabling SSO across multiple applications.
• Users can authenticate once with their OpenID provider and access multiple services without re-entering their credentials.
• It provides the benefits of Kerberos without the complexity.
• OpenID allows you to connect from any data center or cloud to another.

How it works

1. User Authentication: The user logs into an application registered in the Identity provider using his/her credentials.
2. Token Exchange: The application requests an Identity token from the Identity provider and either provides it to the user or forwards it directly to MySQL client on behalf of the user.
3. Verification: The token is verified by the MySQL server to authenticate the user.
4. Access: Based on the token, the MySQL server allows or denies access to resources.

OpenID Connect flow in MySQL

Configuration tasks for the Database Administrator

1. Install the plugin authentication_openid_connect

The administrator must install the server side plugin to be able to configure the server for OpenID Connect authentication. To install the authentication_openid_connect plugin, use the INSTALL PLUGIN statement, adjusting the .so suffix for your platform as necessary:

2. Specify supported Identity providers, their formal name and public signing key

Admin can specify the list of Identity providers, their formal name and public signing key by using the system variable authentication_openid_connect_configuration. For Microsoft Azure the details can be found here. The "issuer" field value on the Microsoft Azure openid configuration page is the formal issuer name which will go to the name field. The public signing key for the required key ID can be found at the link mentioned in "jwks_uri" field which will be used to populate rest of the fields. We will give this set a name, Azure. The same name will be used while user mapping.

The configuration can also be specified in a file as follows:

SET GLOBAL authentication_openid_connect_configuration = "file://<absolute_path_to_file_location>";

3. Map a MySQL user to an external user

Admin can map a MYSQL user to an external user managed in an identity provider as follows:

Where, "3nOXatqpWvCqv6LRSR5lmgtr5ewzfTPnoFamVOPrDeU" is the user's identifier in the Microsoft Azure domain.

Setup tasks for the Connecting User

1. Obtain the Identity token

There are several ways to obtain an Identity token from Azure. In this example we will be using Azure CLI. Use the following steps to obtain the Identity token:

• Login to Azure Cloud Shell using your Microsoft credentials.
• It will launch the Azure Cloud Shell:

• Run the following command to generate a Microsoft Entra ID token:

  az account get-access-token --resource <RESOURCE>

• The value of field "accessToken" is your Microsoft Entra ID token. Copy and save it to a file.

2.  Connect to the DB system

User can connect to the DB system using the following command:

where the file id_token contains the Microsoft Entra ID token we generated using the Azure Cloud Shell.

Conclusion

Integrating OpenID Connect with MySQL Enterprise edition not only strengthens the security of your database environment but also simplifies the process of managing user identities making it another differentiator for you to upgrade to MySQL Enterprise. As businesses continue to embrace distributed systems, adopting standardized authentication mechanisms like OIDC is becoming increasingly important. By leveraging OIDC, MySQL provides a robust and scalable solution for modern authentication needs.

Whether you are building a new application or enhancing an existing one, incorporating OpenID Connect into your MySQL setup is a step towards a more secure and user-friendly authentication experience.

Additional Resources

• MySQL Documentation on Authentication
• OpenID Connect Documentation
• Set up sign-up and sign-in with generic OpenID Connect using Azure Active Directory B2C
• Get Microsoft Entra ID (formerly Azure Active Directory) tokens for users by using the Azure CLI

Implementing OpenID Connect in MySQL can significantly enhance your authentication strategy, paving the way for more secure, scalable, and user-friendly applications. Happy coding! 🚀