Always Encryptedで暗号化されたカラムを更新してみた

JDBC接続文字列に「columnEncryptionSetting=Enabled」を追加し、Azure Key Vault に対する認証を行うための設定を追加することで、Always Encryptedで暗号化したカラムの取得に加え、更新も行うことができる。

今回は、Always Encryptedで暗号化したカラムを更新してみたので、そのサンプルプログラムを共有する。また、SSMS(SQL Server Management Studio)上での、Always Encryptedで暗号化したカラムの更新方法についても、サンプルプログラムの実行結果の中で共有する。

前提条件

下記記事の実装が完了していること。

Always Encryptedで暗号化されたカラムを復号化して表示してみたAlways Encryptedで暗号化したカラムは、Always Encryptedにより暗号化されたカラムを復号化できる設定を追加す...

作成したサンプルプログラム(App Service側)の内容

作成したサンプルプログラム(App Service側)の構成は以下の通り。

なお、上記の赤枠は、前提条件のプログラムから追加・変更したプログラムである。

コントローラクラスの内容は以下の通りで、パスワードを更新する処理を追加している。

package com.example.demo;

import javax.servlet.http.HttpServletRequest;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.http.HttpEntity;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpMethod;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.util.LinkedMultiValueMap;
import org.springframework.util.MultiValueMap;
import org.springframework.util.StringUtils;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.client.RestTemplate;

import com.fasterxml.jackson.databind.ObjectMapper;

@Controller
public class DemoController {

    /** RestTemplateオブジェクト */
    @Autowired
    private RestTemplate restTemplate;

    /** ObjectMapperオブジェクト */
    @Autowired
    private ObjectMapper objectMapper;

    /** application.propertiesからdemoAzureFunc.urlBaseの値を取得 */
    @Value("${demoAzureFunc.urlBase}")
    private String demoAzureFuncBase;

    /**
     * メイン画面を初期表示する.
     * @param model Modelオブジェクト
     * @return メイン画面
     */
    @GetMapping("/")
    public String index(Model model) {
        return "main";
    }

    /**
     * 暗号化されたパスワードを取得する.
     * @param model Modelオブジェクト
     * @return メイン画面
     */
    @PostMapping("/getUserPass")
    public String getUserPass(Model model) {
        // Azure FunctionsのgetUserPass関数を呼び出すためのヘッダー情報を設定する
        HttpHeaders headers = new HttpHeaders();
        headers.setContentType(MediaType.APPLICATION_JSON);

        // Azure FunctionsのgetUserPass関数を呼び出すための引数を設定する
        MultiValueMap<String, String> map 
            = new LinkedMultiValueMap<>();
        HttpEntity<MultiValueMap<String, String>> entity 
            = new HttpEntity<>(map, headers);

        // Azure FunctionsのgetUserPass関数を呼び出す
        ResponseEntity<String> response = restTemplate.exchange(
              demoAzureFuncBase + "getUserPass", HttpMethod.POST
            , entity, String.class);

        // Azure Functionsの呼出結果を、Modelオブジェクトに設定する
        try {
            GetUserPassResult getUserPassResult = objectMapper.readValue(
                response.getBody(), GetUserPassResult.class);
            if (getUserPassResult != null 
                && getUserPassResult.getUserPass() != null) {
                model.addAttribute("getUserPass", getUserPassResult.getUserPass());
            }
        } catch (Exception ex) {
            throw new RuntimeException(ex);
        }
        return "main";
    }

    /**
     * 暗号化されたパスワードを更新する.
     * @param model Modelオブジェクト
     * @param request HttpServletリクエスト
     * @return メイン画面
     */
    @PostMapping("/updUserPass")
    public String updUserPass(Model model, HttpServletRequest request) {
        String updUserPass = request.getParameter("updUserPass");

        // 更新後パスワードが未入力の場合は、更新しない
        if(!StringUtils.hasText(updUserPass)) {
            model.addAttribute("updUserPassMsg"
                , "パスワードが未入力のため更新できませんでした。");
            return "main";
        }
		
        // Azure FunctionsのupdUserPass関数を呼び出すためのヘッダー情報を設定する
        HttpHeaders headers = new HttpHeaders();
        headers.setContentType(MediaType.APPLICATION_JSON);

        // Azure FunctionsのupdUserPass関数を呼び出すための引数を設定する
        MultiValueMap<String, String> map = new LinkedMultiValueMap<>();
        map.add("updUserPass", updUserPass);		
        HttpEntity<MultiValueMap<String, String>> entity 
            = new HttpEntity<>(map, headers);

        // Azure FunctionsのupdUserPass関数を呼び出し、完了メッセージを表示する
        restTemplate.exchange(demoAzureFuncBase + "updUserPass"
            , HttpMethod.POST, entity, String.class);
        model.addAttribute("updUserPassMsg", "パスワードの更新が完了しました。");
        return "main";
    }

}

また、HTMLの内容は以下の通りで、パスワードの更新を行う機能を追加している。

<!DOCTYPE html>
<html lang="ja" xmlns:th="http://www.thymeleaf.org">
<head>
<meta charset="UTF-8">
<title>メイン画面</title>
</head>
<body>
    <form method="post" th:action="@{/updUserPass}">
                   更新後のUserPass： <input type="text" name="updUserPass" /><br/><br/>
	    <input type="submit" value="UserPassを更新" /><br/><br/>
	    <div th:if="${updUserPassMsg}">
	        <span th:text="${updUserPassMsg}">updUserPassMsgの値</span>
	    </div>
	</form>
    <br/><br/><hr/><br/>
	<form method="post" th:action="@{/getUserPass}">
	    <input type="submit" value="getUserPassの値を取得" /><br/><br/>
	    <div th:if="${getUserPass}">
	    	<span th:text="${getUserPass}">getUserPassで取得した値</span>
	    </div>
	</form>
</body>
</html>

その他のソースコード内容は、以下のサイトを参照のこと。
https://github.com/purin-it/azure/tree/master/always-encrypted-update/demoAzureApp

作成したサンプルプログラム(Azure Functions側)の内容

作成したサンプルプログラム(Azure Functions側)の構成は以下の通り。

なお、上記の赤枠は、前提条件のプログラムから追加・変更したプログラムである。

＜2021年4月13日 追記＞
spring-cloud-function-dependenciesのバージョンは、2021年3月16日にリリースしたバージョン3.1.2を利用すると、1つのAzure Functions内に複数のファンクションを含む場合の不具合が解消できている。

その場合、Handlerクラスの継承するクラスを「AzureSpringBootRequestHandler」クラスから「FunctionInvoker」クラスに変更する。

spring-cloud-function-dependenciesの3.1.2を利用した実装サンプルは、以下の記事を参照のこと。

spring-cloud-function-dependenciesのバージョンを最新(3.1.2)にしてみたこれまでこのブログで取り上げてきたAzure Functionsのサンプルプログラムでは、spring-cloud-function-d...

USER_PASSテーブルにアクセスするMapperクラス・Mapper XMLの内容は以下の通りで、USER_PASSテーブルを更新する処理を追加している。

package com.example.mybatis;

import org.apache.ibatis.annotations.Mapper;
import com.example.mybatis.model.UserPass;

@Mapper
public interface UserPassMapper {

    /**
     * USER_PASSテーブルからデータを1件取得する.
     * @return USER_PASSテーブルからの取得結果
     */
    UserPass selectOne();
	
    /**
     * USER_PASSテーブルのデータを更新する.
     * @param userPass 更新対象のUSER_PASSテーブルの値
     */
    void updateUserPass(UserPass userPass);
	
}

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.mybatis.UserPassMapper">
    <resultMap id="userPassResultMap" type="com.example.mybatis.model.UserPass" >
        <result column="id" property="id" jdbcType="VARCHAR" />
        <result column="pass" property="pass" jdbcType="VARCHAR" />
        <result column="passEncrypted" property="passEncrypted" jdbcType="VARCHAR" />
    </resultMap>
    <select id="selectOne" resultMap="userPassResultMap">
        SELECT id, pass, pass_encrypted AS passEncrypted
        FROM user_pass
        WHERE id = 1
    </select>
    <update id="updateUserPass" parameterType="com.example.mybatis.model.UserPass">
        UPDATE user_pass SET pass = #{pass}, pass_encrypted = #{passEncrypted}
        WHERE id = 1
    </update>
</mapper>

また、USER_PASSテーブルを更新する処理を呼び出すサービスクラスと、その入出力項目の内容は、以下の通り。

package com.example.service;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.example.model.UpdUserPassParam;
import com.example.model.UpdUserPassResult;
import com.example.mybatis.UserPassMapper;
import com.example.mybatis.model.UserPass;

@Service
public class UpdUserPassService {

    /** USER_PASSテーブルのデータを取得するMapperオブジェクト */
    @Autowired
    private UserPassMapper userPassMapper;

    /**
     * USER_PASSテーブルのデータを更新し結果を返却する.
     * @param updUserPassParam 更新用Param
     * @return 結果情報オブジェクト
     */
    public UpdUserPassResult updUserPass(UpdUserPassParam updUserPassParam) {
        UserPass userPass = new UserPass();
        userPass.setId("1");
        userPass.setPass(updUserPassParam.getUpdUserPass());
        userPass.setPassEncrypted(updUserPassParam.getUpdUserPass());
        userPassMapper.updateUserPass(userPass);
		
        return new UpdUserPassResult();
    }

}

package com.example.model;

import lombok.Data;

@Data
public class UpdUserPassParam {

    /** 更新後パスワード */
    private String updUserPass;

}

package com.example.model;

public class UpdUserPassResult {

    @Override
    public String toString() {
        return "UpdUserPassResult []";
    }

}

さらに、Azure Functionsのメインクラスは以下の通りで、USER_PASSテーブルを更新する処理を呼び出すサービスクラスを呼び出しを追加している。

package com.example;

import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import java.util.function.Function;

import javax.annotation.PostConstruct;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.Bean;

import com.example.model.GetUserPassParam;
import com.example.model.GetUserPassResult;
import com.example.model.UpdUserPassParam;
import com.example.model.UpdUserPassResult;
import com.example.service.GetUserPassService;
import com.example.service.UpdUserPassService;
import com.microsoft.sqlserver.jdbc.SQLServerColumnEncryptionAzureKeyVaultProvider;
import com.microsoft.sqlserver.jdbc.SQLServerColumnEncryptionKeyStoreProvider;
import com.microsoft.sqlserver.jdbc.SQLServerConnection;

@SpringBootApplication
public class DemoAzureFunction {

    /** keyVaultのクライアントID */
    @Value("${keyVaultClientId}")
    private String keyVaultClientId;

    /** keyVaultのクライアントキー */
    @Value("${keyVaultClientKey}")
    private String keyVaultClientKey;
	
    /** Key Vaultの認証の接続設定が終わっているかどうかを判定するフラグ */
    private static boolean keyVaultProviderFlg = false;

    /** USER_PASSデータ取得サービスクラスのオブジェクト */
    @Autowired
    private GetUserPassService getUserPassService;
	
    /** USER_PASSデータ更新サービスクラスのオブジェクト */
    @Autowired
    private UpdUserPassService updUserPassService;

    public static void main(String[] args) throws Exception {
        SpringApplication.run(DemoAzureFunction.class, args);
    }

    /**
     * Key Vaultの認証の接続設定を登録する.
     * @throws SQLException SQL例外
     */
    @PostConstruct
    public void postConstruct() throws SQLException {
        if (!keyVaultProviderFlg) {
           SQLServerColumnEncryptionAzureKeyVaultProvider akvProvider 
              = new SQLServerColumnEncryptionAzureKeyVaultProvider(
                     keyVaultClientId, keyVaultClientKey);
           Map<String, SQLServerColumnEncryptionKeyStoreProvider> keyStoreMap 
              = new HashMap<String, SQLServerColumnEncryptionKeyStoreProvider>();
           keyStoreMap.put(akvProvider.getName(), akvProvider);
           SQLServerConnection.registerColumnEncryptionKeyStoreProviders(keyStoreMap);
           keyVaultProviderFlg = true;
        }
    }

    /**
     * USER_PASSテーブルのデータを取得し結果を返却する関数
     * @return USER_PASSテーブルデータ取得サービスクラスの呼出結果
     */
    @Bean
    public Function<GetUserPassParam, GetUserPassResult> getUserPass() {
        return getUserPassParam -> getUserPassService.getUserPass(getUserPassParam);
    }
	
    /**
     * USER_PASSテーブルのデータを更新し結果を返却する関数
     * @return USER_PASSテーブルデータ更新サービスクラスの呼出結果
     */
    @Bean
    public Function<UpdUserPassParam, UpdUserPassResult> updUserPass(){
        return updUserPassParam -> updUserPassService.updUserPass(updUserPassParam);
    }
}

また、ハンドラークラスの内容は以下の通りで、Azure App Serviceのコントローラクラスから呼ばれるupdUserPass関数である。

package com.example;

import java.util.Optional;

import org.springframework.cloud.function.adapter.azure.AzureSpringBootRequestHandler;

import com.example.model.UpdUserPassParam;
import com.example.model.UpdUserPassResult;
import com.fasterxml.jackson.core.type.TypeReference;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.microsoft.azure.functions.ExecutionContext;
import com.microsoft.azure.functions.HttpMethod;
import com.microsoft.azure.functions.HttpRequestMessage;
import com.microsoft.azure.functions.HttpResponseMessage;
import com.microsoft.azure.functions.HttpStatus;
import com.microsoft.azure.functions.annotation.AuthorizationLevel;
import com.microsoft.azure.functions.annotation.FunctionName;
import com.microsoft.azure.functions.annotation.HttpTrigger;

public class UpdUserPassHandler 
       extends AzureSpringBootRequestHandler<UpdUserPassParam, UpdUserPassResult> {

    /**
     * HTTP要求に応じて、DemoAzureFunctionクラスのupdUserPassメソッドを呼び出し、
     * その戻り値をボディに設定したレスポンスを返す.
     * @param request リクエストオブジェクト
     * @param context コンテキストオブジェクト
     * @return レスポンスオブジェクト
     */
    @FunctionName("updUserPass")
    public HttpResponseMessage execute(
        @HttpTrigger(name = "request", methods = HttpMethod.POST
           , authLevel = AuthorizationLevel.ANONYMOUS) 
                HttpRequestMessage<Optional<String>> request
           , ExecutionContext context) {

        // リクエストオブジェクトからパラメータ値を取得し、更新用Formに設定する
        ObjectMapper mapper = new ObjectMapper();
        String jsonParam = request.getBody().get();
        jsonParam = jsonParam.replaceAll("\\[", "").replaceAll("\\]", "");

        UpdUserPassParam updUserPassParam = new UpdUserPassParam();
        try {
            updUserPassParam = mapper.readValue(jsonParam
                , new TypeReference<UpdUserPassParam>() {
            });
        } catch (Exception ex) {
            throw new RuntimeException(ex);
        }

        // handleRequestメソッド内でDemoAzureFunctionクラスのupdUserPassメソッドを呼び出し、
        // その戻り値をボディに設定したレスポンスを、JSON形式で返す
        return request.createResponseBuilder(HttpStatus.OK)
             .body(handleRequest(updUserPassParam, context))
             .header("Content-Type", "text/json").build();
    }
}

その他のソースコード内容は、以下のサイトを参照のこと。
https://github.com/purin-it/azure/tree/master/always-encrypted-update/demoAzureFunc

サンプルプログラムの実行結果

サンプルプログラムの実行結果は、以下の通り。

1)「mvn azure-functions:deploy」コマンドによって、Azure Functions上にサンプルプログラムをデプロイする。

なお、Azure Functionsにデプロイする過程は、以下の記事の「Azure FunctionsへのSpring Bootを利用したJavaアプリケーションのデプロイ」を参照のこと。

Azure Functions上でSpring Bootを利用したJavaアプリケーションを作成してみた前回は、Azure Potal上でAzure Functionsを作成してみたが、今回は、前回作成したAzure FunctionsにS...

2)「mvn azure-webapp:deploy」コマンドによって、Azure App Service上にサンプルプログラムをデプロイする。

なお、Azure App Serviceにデプロイする過程は、以下の記事の「App ServiceへのSpring Bootを利用したJavaアプリケーションのデプロイ」を参照のこと。

Azure App Service上でSpring Bootを利用したJavaアプリケーションを作成してみた前回は、Azure Potal上でApp Serviceを作成してみたが、今回は、前回作成したApp ServiceにSpring Bo...

3) Azure App ServiceのURL「https://azureappdemoservice.azurewebsites.net/」とアクセスした場合の実行結果は、以下の通り。

なお、上記URLは、下記Azure App ServiceのURLから確認できる。

4) 「getUserPassの値を取得」ボタンを押下すると、以下のように、暗号化されたパスワードが復号化されて表示される。

5) 更新後のUserPassに「passAft1」を入力し、「UserPassを更新」ボタンを押下すると、以下のように、更新完了メッセージが表示される。

6) 再度「getUserPassの値を取得」ボタンを押下すると、以下のように、更新後のパスワードが復号化されて表示される。

7) このときのデータベースの値は、以下のようになる。

select * from dbo.USER_PASS where id = 1

●復号化しなかった場合

●復号化した場合

なお、SSMS(SQL Server Management Studio)上で暗号化されたカラムを復号化し表示する方法は、下記記事の「SSMSで暗号化されたカラムを復号化し表示」を参照のこと。

SQL DatabaseのカラムをSSMSを使ってAlways Encryptedで暗号化してみたAlways Encryptedを利用すると、SQL DatabaseやSQL Serverのデータベースに格納された、クレジットカード...

8) SSMS上で暗号化されたカラムを更新するには、以下のようなコマンドを実行する。

declare @PASS varchar(12) = 'passAftSsms1';
update dbo.USER_PASS set pass = 'passAftSsms1', pass_encrypted = @PASS where id = 1;

 

select * from dbo.USER_PASS where id = 1

 

9) Azure App ServiceのURL「https://azureappdemoservice.azurewebsites.net/」とアクセスし、「getUserPassの値を取得」ボタンを押下すると、SSMS上で更新したパスワードが復号化されて表示される。

要点まとめ

• JDBC接続文字列に「columnEncryptionSetting=Enabled」を追加し、Azure Key Vault に対する認証を行うための設定を追加することで、Always Encryptedで暗号化したカラムの取得に加え、更新も行うことができる。