Go to Qiita Advent Calendar 2024 Top
LoginSignup
0
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

@yoshioterada(Yoshio Terada)inMicrosoft

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

Last updated at Posted at 2023-03-08

こちらは、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
0
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?