こちらは、Azure Spring Apps でユーザ・マネージドの Identity を利用して MySQL Flexible Server に接続する方法の自分用メモです

環境変数の設定

export AZ_RESOURCE_GROUP=Password-Less-Access
export AZ_DATABASE_NAME=yoshiodb
export AZ_LOCATION=japaneast
export AZ_SPRING_APPS_SERVICE_NAME=yoshio-spring-apps
export AZ_SPRING_APPS_APP_NAME=petclinic-service
export AZ_USER_IDENTITY_NAME=user-identity-for-yoshio

export AZ_MYSQL_AD_NON_ADMIN_USERNAME=petclinic-non-admin-local
export AZ_MYSQL_AD_MI_USERNAME=petclinic-non-admin-azure
export CURRENT_USERNAME=$(az ad signed-in-user show --query userPrincipalName --output tsv)
export CURRENT_USER_OBJECTID=$(az ad signed-in-user show --query id --output tsv)

リソース・グループの作成

az group create \
    --name $AZ_RESOURCE_GROUP \
    --location $AZ_LOCATION

ユーザ Identity の作成

AZ_IDENTITY_RESOURCE_ID=$(az identity create \
    --name $AZ_USER_IDENTITY_NAME \
    --resource-group $AZ_RESOURCE_GROUP \
    --query id \
    --output tsv)

AZ_IDENTITY_PRINCIPAL_ID=$(az identity show \
    --name $AZ_USER_IDENTITY_NAME \
    --resource-group $AZ_RESOURCE_GROUP \
    --query principalId \
    --output tsv)

echo $AZ_IDENTITY_RESOURCE_ID
echo $AZ_IDENTITY_PRINCIPAL_ID

認可の設定(Authorization_Request)

uri=https://graph.microsoft.com/v1.0/servicePrincipals/$AZ_IDENTITY_PRINCIPAL_ID/appRoleAssignments

graphResourceId=$(az ad sp list --display-name "Microsoft Graph" \
                     --query "[0].id" --out tsv)

userReadAll=$(az ad sp list --display-name "Microsoft Graph" \
             --query "[0].appRoles[?value=='User.Read.All' && contains(allowedMemberTypes, 'Application')].id" \
             --output tsv)
groupReadAll=$(az ad sp list --display-name "Microsoft Graph" \
             --query "[0].appRoles[?value=='GroupMember.Read.All' && contains(allowedMemberTypes, 'Application')].id" \
             --output tsv)
appReadAll=$(az ad sp list --display-name "Microsoft Graph" \
             --query "[0].appRoles[?value=='Application.Read.All' && contains(allowedMemberTypes, 'Application')].id" \
             --output tsv)

body1="{'principalId':'$AZ_IDENTITY_PRINCIPAL_ID','resourceId':'$graphResourceId','appRoleId':'$userReadAll'}"
body2="{'principalId':'$AZ_IDENTITY_PRINCIPAL_ID','resourceId':'$graphResourceId','appRoleId':'$groupReadAll'}"
body3="{'principalId':'$AZ_IDENTITY_PRINCIPAL_ID','resourceId':'$graphResourceId','appRoleId':'$appReadAll'}"

az rest --method post --uri $uri --body $body1 --headers "Content-Type=application/json"
az rest --method post --uri $uri --body $body2 --headers "Content-Type=application/json"
az rest --method post --uri $uri --body $body3 --headers "Content-Type=application/json"

MySQL Flexible サーバの作成

setup_mysql.sh を入手

./setup_mysql.sh flexible

[INFO] -------------------------------------------------------
[INFO] Azure Database for MySQL Setup Completed SUCCESS
[INFO] -------------------------------------------------------
[INFO] 1. Please copy the following value into your temporal file
[INFO]
[INFO] RESOURCE GROUP is Password-Less-Access
[INFO] MySQL HOSTNAME is mysqlserver-********.mysql.database.azure.com
[INFO] MySQL USERNAME is azureuser
[INFO] MySQL PASSWORD is !********
[INFO]
[INFO]
[INFO] 2. Please execute the following command.
[INFO]
[INFO] mysql -u azureuser -h mysqlserver-ifmvsbpnxj.mysql.database.azure.com -p [Enter Key]
[INFO] Enter password: !******** [COPY&PASTE]
[INFO] 
[INFO] 
[INFO] 3. Clean up Resource (Delete MySQL DB)
[INFO] az group delete -n Password-Less-Access
[INFO] -------------------------------------------------------

MySQL サーバ名の設定

export AZ_DATABASE_SERVER_NAME=mysqlserver-********

MySQL 内に新規 DB の作成

az mysql flexible-server db create --resource-group $AZ_RESOURCE_GROUP \
    --server-name $AZ_DATABASE_SERVER_NAME  --database-name $AZ_DATABASE_NAME

mysql -u azureuser -h $AZ_DATABASE_SERVER_NAME.mysql.database.azure.com -p

mysql> show variables like "chara%";
+--------------------------+------------------------+
| Variable_name            | Value                  |
+--------------------------+------------------------+
| character_set_client     | latin1                 |
| character_set_connection | latin1                 |
| character_set_database   | latin1                 |
| character_set_filesystem | binary                 |
| character_set_results    | latin1                 |
| character_set_server     | latin1                 |
| character_set_system     | utf8                   |
| character_sets_dir       | /mysql/share/charsets/ |
+--------------------------+------------------------+
8 rows in set (0.19 sec)

mysql> SET character_set_client=utf8mb4 ;
mysql> SET character_set_connection=utf8mb4 ;
mysql> SET character_set_database=utf8mb4 ;
mysql> SET character_set_results=utf8mb4 ;
mysql> SET character_set_server=utf8mb4 ;

MySQL サーバに Identity をアサイン

az mysql flexible-server identity assign \
    --resource-group $AZ_RESOURCE_GROUP \
    --server-name $AZ_DATABASE_SERVER_NAME \
    --identity $AZ_USER_IDENTITY_NAME

現在の AZ Login ユーザ名を取得

export CURRENT_USERNAME=$(az ad signed-in-user show --query userPrincipalName -o tsv)
export CURRENT_USER_OBJECTID=$(az ad signed-in-user show --query id -o tsv)

echo $CURRENT_USERNAME
echo $CURRENT_USER_OBJECTID

MySQL に AD 認証でログインする管理者ユーザの作成（Azure にログインが必要）

az mysql flexible-server ad-admin create \
    --resource-group $AZ_RESOURCE_GROUP \
    --server-name  $AZ_DATABASE_SERVER_NAME \
    --display-name $CURRENT_USERNAME \
    --object-id $CURRENT_USER_OBJECTID \
    --identity $AZ_USER_IDENTITY_NAME

AZ Login を行ったユーザで MySQL にログイン

mysql -h $AZ_DATABASE_SERVER_NAME.mysql.database.azure.com --user $CURRENT_USERNAME \
  --enable-cleartext-plugin --password=$(az account get-access-token \
  --resource-type oss-rdbms --output tsv --query accessToken)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| flexibleserverdb   |
| mysql              |
| performance_schema |
| sys                |
| yoshiodb           |
+--------------------+
6 rows in set (0.21 sec)

MySQL に AD 認証でログインする非管理者ユーザの作成（ローカル開発用）

cat << EOF > create_ad_user.sql
SET aad_auth_validate_oids_in_tenant = OFF;
CREATE AADUSER '$AZ_MYSQL_AD_NON_ADMIN_USERNAME' IDENTIFIED BY '$CURRENT_USER_OBJECTID';
GRANT ALL PRIVILEGES ON $AZ_DATABASE_NAME.* TO '$AZ_MYSQL_AD_NON_ADMIN_USERNAME'@'%';
FLUSH privileges;
EOF

mysql> source create_ad_user.sql
mysql> select user,plugin from mysql.user;
+----------------------------------+-----------------------+
| user                             | plugin                |
+----------------------------------+-----------------------+
| tyoshio2002_yahoo.co.jp#EXT#@tyo | aad_auth              |
| mysql.session                    | mysql_native_password |
| mysql.sys                        | mysql_native_password |
| azureuser                        | mysql_native_password |
| azure_superuser                  | mysql_native_password |
| azure_superuser                  | mysql_native_password |
| petclinic-non-admin-local        | aad_auth              |
+----------------------------------+-----------------------+
7 rows in set (0.20 sec)

非管理者ユーザでログインが可能か確認

mysql -h $AZ_DATABASE_SERVER_NAME.mysql.database.azure.com --user $AZ_MYSQL_AD_NON_ADMIN_USERNAME \
  --enable-cleartext-plugin --password=$(az account get-access-token \
  --resource-type oss-rdbms --output tsv --query accessToken)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| yoshiodb           |
+--------------------+
2 rows in set (0.09 sec)

mysql> source data.sql;

Spring Boot ソースコードのプロパティの修正

mysqlserver-ifmvsbpnxj.mysql.database.azure.com

ローカル環境での動作確認

mvn spring-boot:run

ユーザ・マネージド ID の APP ID を取得

AZ_MYSQL_AD_IDENTITY_APPID=$(az identity list -g Password-Less-Access --query "[].principalId" -o tsv)
echo $AZ_MYSQL_AD_IDENTITY_APPID

ユーザ・マネージド ID によって識別された MySQL のユーザ(petclinic-non-admin-azure)の作成

cat << EOF > create_ad_user.sql
SET aad_auth_validate_oids_in_tenant = OFF;
CREATE AADUSER '$AZ_MYSQL_AD_MI_USERNAME' IDENTIFIED BY '$AZ_MYSQL_AD_IDENTITY_APPID';
GRANT ALL PRIVILEGES ON $AZ_DATABASE_NAME.* TO '$AZ_MYSQL_AD_MI_USERNAME'@'%';
FLUSH privileges;
EOF

ユーザ・マネージド ID でアクセスするユーザの作成と確認

mysql -h $AZ_DATABASE_SERVER_NAME.mysql.database.azure.com --user $CURRENT_USERNAME \
  --enable-cleartext-plugin --password=$(az account get-access-token \
  --resource-type oss-rdbms --output tsv --query accessToken)

mysql> source create_ad_user.sql  
mysql> select user,plugin from mysql.user;
+----------------------------------+-----------------------+
| user                             | plugin                |
+----------------------------------+-----------------------+
| tyoshio2002_yahoo.co.jp#EXT#@tyo | aad_auth              |
| mysql.session                    | mysql_native_password |
| mysql.sys                        | mysql_native_password |
| azureuser                        | mysql_native_password |
| azure_superuser                  | mysql_native_password |
| azure_superuser                  | mysql_native_password |
| petclinic-non-admin              | aad_auth              |
| petclinic-non-admin-azure        | aad_auth              |
+----------------------------------+-----------------------+
8 rows in set (0.01 sec)

ローカルの環境からはログインができない事を確認

mysql -h $AZ_DATABASE_SERVER_NAME.mysql.database.azure.com \
  --user 'petclinic-non-admin-azure'  --enable-cleartext-plugin \
  --password=$(az account get-access-token --resource-type oss-rdbms \
  --output tsv --query accessToken)

Azure Spring Apps の作成

az spring create \
    --resource-group $AZ_RESOURCE_GROUP \
    --name $AZ_SPRING_APPS_SERVICE_NAME

Azure Spring Apps にアプリケーションの作成

az spring app create \
    --deployment-name production \
    --resource-group $AZ_RESOURCE_GROUP \
    --service $AZ_SPRING_APPS_SERVICE_NAME \
    --name $AZ_SPRING_APPS_APP_NAME \
    --assign-endpoint true \
    --user-assigned $AZ_IDENTITY_RESOURCE_ID

Azure Spring Apps にアプリケーションのデプロイ

az spring app deploy \
    --resource-group $AZ_RESOURCE_GROUP \
    --service $AZ_SPRING_APPS_SERVICE_NAME \
    --name $AZ_SPRING_APPS_APP_NAME \
    --env "spring.datasource.azure.passwordless-enabled=true" "spring.datasource.url=jdbc:mysql://$AZ_DATABASE_SERVER_NAME.mysql.database.azure.com:3306/yoshiodb?serverTimezone=UTC&defaultAuthenticationPlugin=com.azure.identity.extensions.jdbc.mysql.AzureMysqlAuthenticationPlugin&authenticationPlugins=com.azure.identity.extensions.jdbc.mysql.AzureMysqlAuthenticationPlugin" "spring.datasource.username=petclinic-non-admin-azure" \
    --artifact-path target/spring-petclinic-2.7.8.jar