-
MySQL 26.7 and the Power of Community Contribution
As we have discussed through the new MySQL Community Engagement initiative, we are sharing metrics that highlight how the community contributes to MySQL. These measures help us understand the growth of community participation, recognize the people helping improve the product, and identify where we can continue to strengthen the contributor experience. The MySQL community delivered […]
-
150+ SQL Commands Explained With Examples (2026 Update)
In this guide, we explain 150+ SQL commands in simple words, covering everything from basic queries to advanced functions for 2026. We cover almost every SQL command that exists in one single place, so you never have to go search for anything anywhere else. If you master these 150 commands, you will become an SQL […]
-
OAuth2 and JWT Logins for MySQL
An engineer leaves your company. You disable their single sign-on account, and their access to the wiki, the cloud console, and the CI system goes with it. Typically, access to database resources doesn't follow such a simple plan. Database user and role maintenance too often happens within the database, oblivious to single sign-on. User accounts carry a password which has to be managed and rotated (and the password is probably already sprawled out across shell history and a shared password manager).An easier way to manage this is to bring the identity-provider model to the database. This is what the VillageSQL Server vsql-oauth2 extension does. It adds an authentication method that accepts an OAuth2/OpenID Connect token (a JWT) from your identity provider in place of a password. Your identity provider decides who can log in, MySQL privileges decide what they can do inside, and disabling someone stops them getting another token. The token already in hand works until it expires, so your provider's token lifetime dictates the access window.VillageSQL is the innovation platform for MySQL that adds an extension framework (similar to PostgreSQL's extension framework) to enable permissionless innovation. Instead of waiting for a feature to be implemented in a future version of MySQL in a few years, new functionality can be dynamically added to the version of MySQL you run today. VillageSQL Server supports MySQL 8.4, 9.7, and Percona Server 8.4. The vsql-oauth2 extension is an example of what is meant by permissionless innovation.The rest of this post shows how the vsql-oauth2 extension works. The examples use Microsoft Entra ID, because Entra puts readable role names directly into the token. We start with the simple case: install the extension, point it at Entra's signing keys, and log in to the database with a token instead of a password. We then add role mapping, so a person the database has never seen before can log in for the first time and come away with an account and the roles their identity provider assigned them.
If you would rather stop reading here and have your AI agent demonstrate this for you with a mock identity provider, open this dropdown and copy the prompt into your preferred AI coding tool.
Set up a working demo of passwordless MySQL logins on my machine, using the
vsql-oauth2 extension for VillageSQL. Act as my own identity provider so no real
IdP is needed. Work only against a local throwaway server — if the only
VillageSQL or MySQL server you find looks like something I depend on, stop and
ask me before touching it.
Do all of this yourself, and show me the real output of each step:
1. Find a VillageSQL server, or install one. The install script needs a method:
`curl -fsSL https://install.villagesql.com | INSTALL_METHOD=prebuilt bash`.
Start it with `--vsql_allow_preview_extensions=ON`; on a server already
running, `SET PERSIST vsql_allow_preview_extensions=ON` takes effect at once.
Confirm with `SELECT VERSION()` before continuing.
2. Install the extension: `INSTALL EXTENSION vsql_oauth2;`. It ships with the
server, so there is nothing to download. Read
`INFORMATION_SCHEMA.EXTENSION_REGISTRATION` and
`SHOW GLOBAL VARIABLES LIKE 'vsql_oauth2.%'` and tell me what settings exist.
3. Stand in for the identity provider. Generate an RSA keypair with openssl, and
write a small script that mints signed RS256 JWTs from a claims payload, each
header carrying a `kid`. Use openssl only — do not install a JWT library.
Then publish the public key the way a real provider does: serve a JWKS
document over HTTP on localhost, holding the key as a JWK with `kty`, `n`,
`e` and that same `kid`. Let the operating system choose the port rather than
picking one yourself, and tell me the URL.
4. Do the basic case. Set `issuer`, `audience` and `username_claim`, and point
`jwks_url` at the JWKS URL from step 3. Create an account bound to
`vsql_oauth2`, create the account it maps onto, and grant the proxy. Then log
in with a token in place of the password and show me
`SELECT CURRENT_USER(), @@external_user;`. Show me your JWKS server's own log
as evidence that the database really fetched the keys.
5. Do the joiner case. Turn on `roles_claim`, `roles_filter`,
`roles_transform_pattern`, `roles_transform_replacement`, `auto_create` and
`auto_grant`. Create two roles named in the rewritten form the transform
produces, then log in as a user who has never touched this database with a
token carrying a roles claim. Show me that the account and the role grant both
appeared, and that the role is active for the session.
6. Try to break it. Take a token you have just shown works and change exactly
one thing at a time: expire it, alter the issuer, alter the audience, name a
`kid` your JWKS document does not carry, strip the signature with
`alg: none`, and re-sign it with HMAC using the public key as the shared
secret. Show me all six refused. Every refusal prints the same message, so
for each one name the single change you made, and log in with the unchanged
token in the same run to prove the refusal came from that change.
Then give me a table of what you ran and what came back, tell me anything that
did not behave the way this asked, and drop every user, role and setting you
created, stop the JWKS server, and leave my server back where it started.
Log in without a database password
Below is the server-side setup for the basic case, using Microsoft Entra ID:
INSTALL EXTENSION vsql_oauth2;
SET GLOBAL vsql_oauth2.issuer = 'https://login.microsoftonline.com/<tenant-guid>/v2.0';
SET GLOBAL vsql_oauth2.jwks_url = 'https://login.microsoftonline.com/<tenant-guid>/discovery/v2.0/keys';
SET GLOBAL vsql_oauth2.audience = '<database-app-client-id>';
SET GLOBAL vsql_oauth2.username_claim = 'preferred_username';
CREATE USER oauth_user IDENTIFIED WITH vsql_oauth2;
CREATE USER 'dana@myco.example';
GRANT SELECT ON *.* TO 'dana@myco.example';
GRANT PROXY ON 'dana@myco.example' TO oauth_user;
jwks_url is Entra's key endpoint. The extension fetches the signing keys from it and refreshes them hourly by default, so Entra can rotate its keys without anyone touching the database. The public_key setting is the alternative: it pins one key you paste in yourself, which suits a self-signed test and stops working at the provider's next rotation. Set both and jwks_url wins.
Point audience at the app registration that stands for the database, and send Entra's access token for that app rather than the id_token. Only the access token carries App Roles under the readable names the next section filters on. The README's provider settings cover the app registrations Entra needs and the values for other providers.
From there, the standard mysql client logs in with a token where the password would normally go. Passing it through MYSQL_PWD keeps it out of the process list:
$ MYSQL_PWD='<the JWT>' mysql --enable-cleartext-plugin --user=oauth_user \
-e "SELECT CURRENT_USER(), @@external_user;"
+---------------------+-------------------+
| CURRENT_USER() | @@external_user |
+---------------------+-------------------+
| dana@myco.example@% | dana@myco.example |
+---------------------+-------------------+
The credential is a short-lived token from your identity provider, so there is no password to store or rotate. Where the token comes from depends on who is connecting, e.g., a person at a shell, a CI job, or an application. The extension takes the same token in all cases. Obtaining it is ordinary OAuth for your provider.
The vsql-oauth2 README has the commands for all three. It also covers the vsql_oauth_client plugin, which fetches the token itself so it never touches the command line. However you send it, the token travels in cleartext at the protocol level, so the connection has to run over TLS.
You connect as oauth_user, which is bound to the extension and holds no privileges of its own. The username_claim setting says which claim names the account to run as, and GRANT PROXY is what allows the switch. Entra's sub is an opaque identifier, so this example reads preferred_username and gets Dana's sign-in name. The session then has dana@myco.example's privileges. @@external_user reports the identity that arrived in the token, so the audit trail keeps it even though the session runs as another account.
The token has to be signed with one of your provider's published keys for any of this to happen, and the extension accepts RSA and ECDSA signatures only. An unsigned token is refused before any signature check runs, and so is one that switches to a symmetric algorithm hoping the server will reuse your public key as a shared secret.
Easy database access management
The basic case maps a token to an account you created ahead of time. The extension can go further and take its cues from the App Roles Entra assigned the person signing in. You tell it which claim to read and how to rewrite the names, then create the roles those App Roles map onto:
SET GLOBAL vsql_oauth2.roles_claim = 'roles';
SET GLOBAL vsql_oauth2.roles_filter = 'mysql-grp-.*';
SET GLOBAL vsql_oauth2.roles_transform_pattern = '-';
SET GLOBAL vsql_oauth2.roles_transform_replacement = '_';
SET GLOBAL vsql_oauth2.auto_create = ON;
SET GLOBAL vsql_oauth2.auto_grant = ON;
CREATE ROLE mysql_grp_dba;
The two transform settings rewrite each matched App Role before it becomes a role name, turning mysql-grp-dba into mysql_grp_dba. Now someone who has never touched this database logs in with a token that says preferred_username: alice@myco.example, roles: [mysql-grp-dba]:
$ MYSQL_PWD='<her JWT>' mysql --enable-cleartext-plugin --user='alice@myco.example' \
-e "SELECT CURRENT_USER(); SELECT CURRENT_ROLE();"
+----------------------+
| CURRENT_USER() |
+----------------------+
| alice@myco.example@% |
+----------------------+
+---------------------+
| CURRENT_ROLE() |
+---------------------+
| `mysql_grp_dba`@`%` |
+---------------------+
One login created the account, granted the role her App Role entitles her to, and activated it for the session. An auto-created account runs as itself, so this path needs no proxy grant. The DBA never saw a ticket and never ran a CREATE USER. The DBA's job moves up a level: grant each role its privileges once, and let the identity provider say who holds the App Role. When an App Role later disappears from someone's token, that role stops activating at their logins, but the granted membership stays until a human revokes it.
Alice had no account, so auto_create did all of it. auto_grant covers the other case, someone who already has an account, and it grants the roles their token claims each time they log in. Both default to off and work independently, so you can turn on either one without the other. If you leave them off, tokens only ever activate roles you granted by hand, and unknown users stay unknown.
Try it out
Please try out vsql-oauth2 against your identity provider, tell us how it goes, especially which claim layouts your tokens carry. If you are not on Entra, the same settings apply — your provider's discovery document (/.well-known/openid-configuration) gives the issuer and jwks_url values. We would love to hear from you on Discord or leave an issue on vsql-oauth2.
To get started with VillageSQL Server, go to villagesql.com.
-
10 Simple Steps to Solve SQL Problems [2026]
SQL problems become manageable when you turn the prompt into decisions about rows, columns, grouping, and result order, and I ran the worked example below with Python 3.11.16 and SQLite 3.53.1 so you can inspect how each clause changes the result. Start with the requested result Before writing SQL, mark the rows the prompt should […]
-
OpenID Connect Authentication for MySQL, Now Fully Open Source
Percona Server for MySQL now ships with a fully open source OpenID Connect (OIDC) authentication plugin, available starting with Percona Server for MySQL 8.4.11-11 and 9.7.2-2 (not yet released as of this writing). It allows a MySQL account to authenticate against any standards-compliant Identity Provider (IdP) instead of relying on a locally stored password, closing the gap with MySQL Enterprise Edition, which has offered OIDC authentication since MySQL 9.1 and, in several respects, going beyond it.
Oracle offers the same category of functionality, but its server-side plugin is part of the paid MySQL Enterprise Edition. Percona’s implementation is open source and adds three capabilities the Enterprise plugin does not provide: automatic signing-key synchronization from a JWKS endpoint, IdP group-to-role mapping, and proxy-user support. This article explains how the plugin works and why those differences matter in practice.
What OpenID Connect Brings to MySQL Authentication
OpenID Connect is an identity layer built on top of the OAuth 2.0 authorization framework [5]. Whereas OAuth 2.0 governs delegated access to resources, OIDC adds a standardized way for a client to establish who a user is. After a user signs in to an Identity Provider, the IdP issues a signed JSON Web Token (JWT), called an ID token, that carries the user’s identity and attributes in a verifiable, tamper-evident form.
Using that model for MySQL authentication brings several practical advantages over password-based accounts:
Alignment with single sign-on. Users authenticate once with their IdP and can reuse that session context across OIDC-aware applications, including databases. User lifecycle and password management remain centralized.
No long-lived secrets on the wire. ID tokens are short-lived and cryptographically signed, so there is no static password to steal, rotate, or accidentally commit to a configuration file.
Support for hybrid deployments. Organizations that run MySQL on-premises while hosting applications in the cloud can still authenticate through the same identity plane on both sides.
Broad interoperability. Because OpenID Connect is a widely adopted standard, the plugin can work with any compliant provider, including Keycloak, Okta, Microsoft Entra ID, and Google Identity.
None of that is unique to Percona; Oracle makes a similar value proposition for the Enterprise plugin. The real difference lies in how much operational burden the plugin removes from the administrator, which becomes clear in the next sections.
How OpenID Connect Authentication Works
Once the plugin and its configuration are in place, the authentication path is the same regardless of which IdP issued the token:
The user authenticates to the IdP and receives a signed ID token.
The token is written to a local file that only the client operating system account can read.
The MySQL client uses an option that causes the client-side OIDC plugin to load and read the token from the file. The token is sent to the server as part of the authentication handshake.
The server validates the secure channel and decodes the token. It then verifies the token signature using the selected IdP’s public key, checks the expiration time, and validates the configured claims.
The server resolves the final identity as either a personal account or a group-based proxy target. The plugin may also return roles mapped from the user’s group membership.
Configuring Trusted Providers and Letting the Plugin Manage the Keys
Identity Providers rotate their signing keys periodically as a basic security measure. If a key is ever compromised, limiting its lifetime reduces the potential impact, and regular rotation also lowers the long-term value of any one key as a target. In practice, rotation is gradual: a new key is published and accepted before it starts signing tokens, and an old key remains valid for a period after it stops signing so that tokens already in flight can still be verified.
Public keys are exposed through the standard JWKS (JSON Web Key Set) endpoint, which applications can use to verify tokens issued by the IdP [6].
The Percona OpenID Connect authentication plugin can download public keys from a configured JWKS endpoint when the plugin is loaded, typically during installation and server startup, and store them in a cache. It also provides a User Defined Function (UDF) that can refresh the cache on demand or periodically through the Event Scheduler.
By contrast, Oracle’s counterpart plugin requires signing keys to be configured statically through the authentication_openid_connect_configuration server variable, supplied either as an inline JSON string or as a path to a JSON file. There is no retrieval or refresh from the JWKS endpoint, so keeping keys current after each rotation remains a manual task for the administrator. In the window just after a rotation, tokens signed with the previous key are still valid but cannot be verified until the configuration is updated. Percona’s plugin supports static key configuration as well, but that mode is better suited to testing or temporary setups than to production.
Example
Using the feature requires two simple steps. First, JWKS endpoint URL must be set in the plugin’s configuration. For example, the below configuration defines IdP named as example-keycloak (pay attention to jwks-url element):{
"example-keycloak": {
"issuer-name": "https://keycloak.example.com/realms/master",
"jwks-url": "https://keycloak.example.com/realms/master/protocol/openid-connect/certs",
"audiences": [ "mysql-oidc" ]
}
}The second step is ensuring the MySQL event scheduler is running and creating an event updating the keys. For example, to enable updating the keys for example-keycloak every hour run from MySQL client:CREATE EVENT update_oidc_keys
ON SCHEDULE EVERY 1 HOUR
DO SELECT update_jwks("example-keycloak");
Benefits of Using IdP Groups
This is where Percona’s plugin diverges most clearly from the Enterprise implementation.
Groups are managed by the corporate Identity Provider and group membership may be carried by ID tokens. OIDC does not define a standard claim for that, but most IdP implementations allow adding a group claim to the tokens. The Percona’s plugin allows the administrator to configure the group claim name so that it matches the token format used by the chosen IdP.
There are two practical ways to take advantage of this feature: group-to-role mapping and proxy users.
Group-to-Role Mapping
Membership in a group can automatically translate into MySQL roles and therefore privileges across multiple MySQL servers at the same time. On a single server, the flow looks like this:
The administrator creates roles and grants them privileges.
The administrator defines the IdP group-to-MySQL role mapping in the plugin configuration file.
When the user connects, the plugin returns the roles that match the user’s groups, and the server automatically grants those roles to the user.
The user can activate any granted role and exercise the privileges assigned to it.
Please note, that group-to-role mapping still requires an account created for each user, but automates managing user privileges.
Example
To create roles and grant them some privileges one may run:CREATE ROLE accounting;
GRANT ALL PRIVILEGES ON accounting_database.* TO accounting;
CREATE ROLE sales;
GRANT ALL PRIVILEGES ON sales_database.* TO sales;Then, to to define the mapping add to IDP configuration:"group-claim": "groups",
"group-role": [
{ "/accounting": "accounting" },
{ "/marketing": "marketing" }
]Any user connecting with an ID token containing claim “groups”:[“/accounting”] will be granted with role accounting and effectively obtain access to accounting_database and so on.
Proxy Users
The proxy capability in MySQL allows an authentication plugin to request that the connecting external user be logged in as a different MySQL user. In this model, the external identity is the proxy user and the mapped MySQL account is the proxied user. The purpose is to let multiple users share accounts with the same privilege set, avoiding the need to create a separate personal database account for every individual.
This feature must be supported by the authentication plugin, whose job is to choose the proxied user according to the specifics of the authentication method. In the Percona OIDC plugin, that selection is based on the group claim in the token and works as follows:
The administrator creates a proxy user identified by the OIDC plugin. This can be either a single anonymous account (”@”) without a specific group name, referred to as anonymous proxying, or multiple group-related accounts, referred to as named group proxying.
The administrator creates proxied users for each group. These accounts should not use a login plugin, so nobody can connect to them directly. The username must match the group name.
The administrator grants the PROXY privilege for each proxy user on all related proxied users.
When a user connects, in the anonymous proxying case the plugin returns the user’s first group as the proxied username. In the named group proxying case, the plugin checks whether the user belongs to the group and returns that group as the proxied username.
The server verifies that the requested proxied account exists and that the proxy user has the required PROXY privilege on it. If both checks succeed, the session runs with the proxied account’s privileges.
Other Features
Supported signing algorithms include RSASSA-PKCS1-v1_5, RSASSA-PSS, and ECDSA with SHA-256, SHA-384, and SHA-512 hashing functions.
The Percona approach uses the client-side OpenID Connect plugin from upstream MySQL, which ensures compatibility with the standard Oracle client.
Both client-side and server-side OpenID Connect plugins ensure that the token is sent via a secure channel. Accepted protocols are TCP protected by TLS, Unix sockets, and shared memory.
What OpenID Connect Authentication Does Not Do
There are some limits worth knowing.
The first comes from MySQL’s authentication design: any authentication plugin is used at connection time only. In the case of OIDC, the token is validated when the user connects, and a session that stays open may outlive the ID token that opened it. There is no out-of-the-box mechanism to force re-authentication after some time (except for idle connection timeout).
A similar situation applies to group-role mapping. The roles tied to the user’s groups in the ID token are granted or revoked at connection time. As a result, if a user is added to or removed from an IdP group, they must reconnect to Percona Server for the change to be reflected in their granted roles.
The proxying mechanism uses group membership claim instead of the token’s subject, so any token signed by a configured IdP that carries the required group is accepted. Group membership is your trust boundary in those modes, so treat it that way.
The current proxying implementation assumes the proxied user’s name matches the group name. This can be a problem when a group name isn’t a valid MySQL username (for example, it’s too long or contains disallowed characters), or when multiple groups need to map to a single account. We plan to add group-to-proxied-account mapping in future releases to address this.
The client-side plugin doesn’t verify the ID token (for example, check whether it has expired) before connecting, and the server doesn’t report the reason for access being denied (for security reasons). A good practice is to obtain a fresh token before connecting.
Conclusion
Functionally, Percona’s OpenID Connect plugin covers the same core ground as the counterpart in MySQL Enterprise Edition: signed ID tokens, claim validation, subject matching, and secure-transport enforcement.
It goes further in several important areas:
It is open source.
Keys can stay current automatically through JWKS synchronization.
Group-to-role mapping allows IdP group membership to drive MySQL role grants for the lifetime of the session.
Proxy-user support allows many IdP identities to share a smaller set of MySQL accounts.
Our OIDC implementation is suitable for real-world identity operations at scale. It can automatically map identities and groups managed by an IdP to database users and roles, and synchronize cryptographic keys.
References
Percona Server for MySQL documentation: OpenID Connect authentication.
Percona Server for MySQL documentation: Get started with OpenID Connect authentication.
MySQL 9.7 Reference Manual: OpenID Connect Pluggable Authentication.
MySQL 9.7 Reference Manual: Proxy Users.
OpenID Foundation: How OpenID Connect Works
auth0 Docs: JSON Web Key Sets.
Written by Michal Jankowski. Reviewed by Dennis Kittrell and Oleksiy Lukin.
Percona® is a registered trademark of Percona LLC. MySQL® is a registered trademark of Oracle Corporation.
The post OpenID Connect Authentication for MySQL, Now Fully Open Source appeared first on Percona.
|
