Azure Spring Apps でシステム・マネージドの Identity を利用して MySQL Flexible Server にパスワードレスで接続する方法

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

環境変数の設定
--------------------------------------------------
```bash
# リソースグループ名
# MySQL に作成する DB 名
# インストール・ロケーション
# Azure Spring Apps のインスタンス名
# Azure Spring Apps のアプリケーション名
# 作成するユーザ Managed Identity 名
# Azure CLI のログイン・ユーザ名
# Azure CLI のログイン・ユーザのオブジェクトID

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 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)
```
--------------------------------------------------

リソース・グループの作成
--------------------------------------------------
```bash
az group create \
    --name $AZ_RESOURCE_GROUP \
    --location $AZ_LOCATION
```



MySQL Flexible サーバの作成
--------------------------------------------------

[setup_mysql.sh](https://github.com/MicrosoftDocs/mslearn-jakarta-ee-azure/blob/main/setup_mysql.sh)を入手

```bash
./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-lkmkqtzrik.mysql.database.azure.com
[INFO] MySQL USERNAME is azureuser
[INFO] MySQL PASSWORD is !psUvMYai11789
[INFO]
[INFO]
[INFO] 2. Please execute the following command.
[INFO]
[INFO] mysql -u azureuser -h mysqlserver-lkmkqtzrik.mysql.database.azure.com -p [Enter Key]
[INFO] Enter password: !psUvMYai11789 [COPY&PASTE]
[INFO] 
[INFO] 
[INFO] 3. Clean up Resource (Delete MySQL DB)
[INFO] az group delete -n Password-Less-Access
[INFO] -------------------------------------------------------
```

MySQL サーバ名の設定
--------------------------------------------------
```bash
export AZ_DATABASE_SERVER_NAME=mysqlserver-lkmkqtzrik
```

MySQL 内に新規 DB の作成
--------------------------------------------------
```bash
az mysql flexible-server db create --resource-group $AZ_RESOURCE_GROUP \
    --server-name $AZ_DATABASE_SERVER_NAME  --database-name $AZ_DATABASE_NAME
```

MySQL サーバ・インスタンスに接続し文字セットの変更
--------------------------------------------------
```bash
mysql -u azureuser -h $AZ_DATABASE_SERVER_NAME.mysql.database.azure.com -p
```

```mysql
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 ;
```

ユーザ・Identity を作成
--------------------------------------------------
```bash
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)
--------------------------------------------------
```bash
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"
```

Azure Spring Apps の作成
--------------------------------------------------
```bash
az spring create \
    --resource-group $AZ_RESOURCE_GROUP \
    --name $AZ_SPRING_APPS_SERVICE_NAME \
    --location $AZ_LOCATION 
```

Azure Spring Apps にアプリケーションの作成
--------------------------------------------------
```bash
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 
```

System Managed Identity で MySQL との接続用アカウント作成
--------------------------------------------------
```bash
az spring connection create mysql-flexible \
    --deployment production \
    --resource-group $AZ_RESOURCE_GROUP \
    --service $AZ_SPRING_APPS_SERVICE_NAME \
    --app $AZ_SPRING_APPS_APP_NAME \
    --connection user_for_springapp \
    --tg $AZ_RESOURCE_GROUP \
    --server $AZ_DATABASE_SERVER_NAME \
    --database $AZ_DATABASE_NAME \
    --system-identity mysql-identity-id=$AZ_IDENTITY_RESOURCE_ID \
    --client-type springboot
```

MySQL に接続
--------------------------------------------------
```bash
mysql -u azureuser -h $AZ_DATABASE_SERVER_NAME.mysql.database.azure.com -p

MySQL PASSWORD is !psUvMYai11789
```

MySQL に "ad_user_for_springapp" の管理者が自動生成され
このアカウントを利用しログインが可能となる事を確認

確認方法

```mysql
mysql> select user,plugin from mysql.user;
```

アプリケーション、プロパティの編集（DB ユーザの編集）
--------------------------------------------------
1. application.properties の編集
2. pom.xml の確認

```bash
mvn clean package
```

Azure Spring Apps にアプリケーションのデプロイ
--------------------------------------------------
```bash
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_PASSWORDLESSENABLED=true" \
    --artifact-path target/spring-petclinic-2.7.8.jar
```

Azure Functions でも System Managed Identity を確認 (Java アプリ）
--------------------------------------------------
```bash
mvn clean package
mvn azure-functions:deploy
```

Azure Functions (Azure App Service)用システム・マネージドIDの作成
--------------------------------------------------
```bash
az webapp connection create  mysql-flexible \
    --resource-group $AZ_RESOURCE_GROUP \
    -n Java-Azure-Functions-yoshio \
    --connection user_for_webapp \
    --tg $AZ_RESOURCE_GROUP \
    --server $AZ_DATABASE_SERVER_NAME \
    --database $AZ_DATABASE_NAME \
    --system-identity mysql-identity-id=$AZ_IDENTITY_RESOURCE_ID \
    --client-type java
```

```mysql
mysql> select user,plugin from mysql.user;
```