Azure中用AAD令牌实现前端、中间层与MySQL数据库的连接方案问询

Great question—this is a super common scenario when building secure layered apps on Azure with Spring, and there’s a clean, supported way to make this end-to-end flow work using Azure AD’s On-Behalf-Of (OBO) flow and Azure Database for MySQL’s native Azure AD authentication support. Here’s the step-by-step breakdown of the optimal implementation:

1. Prerequisite: Configure Azure Database for MySQL for Azure AD Authentication #

Before writing any code, you need to set up your MySQL instance to accept Azure AD tokens as valid authentication:

• Set an Azure AD admin for your Azure MySQL server (this can be a user or security group in your tenant—this account manages Azure AD-linked database users).
• Create database users mapped to Azure AD identities: For every user who needs database access, run this MySQL command (replace the UPN with the user’s Azure AD email/identifier):

  CREATE USER 'user@yourdomain.com' IDENTIFIED WITH azure_ad_auth;
  GRANT SELECT, INSERT, UPDATE ON your_target_database.* TO 'user@yourdomain.com';
  

  This links the Azure AD user to a MySQL database user with granular permissions.

2. Frontend to Middle Tier: Keep Your Existing OAuth2 Setup #

Your current frontend-to-middle-tier authentication flow is already correct:

• Frontend authenticates users via Azure AD, retrieves an access token scoped to your middle-tier Spring app.
• Middle-tier is configured as an OAuth2 resource server, validating the token and enforcing user authentication for all API endpoints.

3. Middle Tier to MySQL: Implement the On-Behalf-Of (OBO) Flow #

This is the missing piece. The frontend’s token is only valid for your middle tier—you need to exchange it for a token that Azure MySQL accepts. Here’s how to build this in Spring Boot:

a. Configure Your Middle-Tier App in Azure AD #

• In your middle-tier Azure AD app registration, add a delegated permission for Azure Database for MySQL (the resource URI is https://mysql.database.azure.com/). Request the .default scope, and ensure your tenant admin grants consent for this permission.
• Enable the OBO flow by generating a client secret or certificate for your middle-tier app (required to authenticate with Azure AD during token exchange).

b. Add Required Dependencies #

Include these dependencies in your pom.xml (adjust for Gradle if needed):

<dependency>
    <groupId>com.azure.spring</groupId>
    <artifactId>azure-spring-boot-starter-active-directory</artifactId>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-oauth2-resource-server</artifactId>
</dependency>
<dependency>
    <groupId>com.mysql.cj</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>

c. Build a Service to Fetch MySQL-Bound Tokens #

Create a service that uses the OBO flow to exchange the frontend’s token for a MySQL-compatible access token:

import com.azure.identity.OnBehalfOfCredential;
import com.azure.identity.OnBehalfOfCredentialBuilder;
import com.azure.core.credential.AccessToken;
import com.azure.core.credential.TokenRequestContext;
import com.azure.core.credential.TokenRequestContextBuilder;
import org.springframework.security.core.Authentication;
import org.springframework.security.oauth2.core.OAuth2AuthenticatedPrincipal;
import org.springframework.stereotype.Service;

@Service
public class MySQLTokenService {

    private final OnBehalfOfCredential oboCredential;

    public MySQLTokenService() {
        this.oboCredential = new OnBehalfOfCredentialBuilder()
                .clientId("<YOUR-MIDDLE-TIER-APP-ID>")
                .clientSecret("<YOUR-MIDDLE-TIER-APP-SECRET>")
                .tenantId("<YOUR-AZURE-TENANT-ID>")
                .build();
    }

    public String getMySQLAccessToken(Authentication authentication) {
        OAuth2AuthenticatedPrincipal principal = (OAuth2AuthenticatedPrincipal) authentication.getPrincipal();
        String userFrontendToken = principal.getAttribute("access_token");

        TokenRequestContext requestContext = new TokenRequestContextBuilder()
                .addScopes("https://mysql.database.azure.com/.default")
                .build();

        // Fetch the token on behalf of the authenticated user
        AccessToken mysqlToken = oboCredential.getToken(requestContext).block();
        return mysqlToken.getToken();
    }
}

d. Configure a Dynamic Data Source #

Instead of using a static password, set up your MySQL data source to dynamically use the OBO token as the authentication password. Using HikariCP (Spring’s default connection pool):

import com.zaxxer.hikari.HikariDataSource;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.security.core.context.SecurityContextHolder;

import javax.sql.DataSource;

@Configuration
public class DataSourceConfig {

    private final MySQLTokenService mySQLTokenService;

    public DataSourceConfig(MySQLTokenService mySQLTokenService) {
        this.mySQLTokenService = mySQLTokenService;
    }

    @Bean
    public DataSource dataSource() {
        HikariDataSource dataSource = new HikariDataSource();
        dataSource.setJdbcUrl("jdbc:mysql://<YOUR-MYSQL-SERVER>.mysql.database.azure.com:3306/<YOUR-DATABASE>?useSSL=true&serverTimezone=UTC");
        // Use the Azure AD user's UPN (matches the MySQL user you created earlier)
        dataSource.setUsername("<USER-UPN>");
        
        // Dynamic password provider: fetch the OBO token for the current authenticated user
        dataSource.setPasswordProvider(() -> {
            Authentication auth = SecurityContextHolder.getContext().getAuthentication();
            return mySQLTokenService.getMySQLAccessToken(auth);
        });

        return dataSource;
    }
}

4. Key Best Practices & Considerations #

• Token Expiry Handling: Azure AD access tokens expire after ~1 hour. HikariCP automatically refreshes connections, and your token service will fetch a new token each time a connection is created—no manual refresh logic needed.
• Permission Alignment: Ensure MySQL database permissions match Azure AD user roles (e.g., restrict write access to Azure AD admins only).
• Security: Never store tokens in persistent storage. The OBO flow ensures you only retrieve tokens when needed, and they’re short-lived to minimize risk.
• Error Handling: Add fallback logic for token retrieval failures (e.g., expired frontend token, user not authorized in MySQL) to return clear, user-friendly errors to the frontend.

This flow ensures users only authenticate once via the frontend, and their identity is seamlessly propagated through the middle tier to the database—no separate database credentials required!

内容的提问来源于stack exchange，提问作者Ranbir Sinha