[専用SQLプール]データの列の暗号化を試してみた

Azure Synapse Analytics 専用SQLプールにもSQL Server同様にデータの列暗号化機能が存在します。今回はこの「データの列暗号化」を試してみたので記載いたします。

# 検証データ
今回検証するデータは以下のようなDDLで作成をしました。
```SQL
CREATE TABLE CUSTOMER
(
	C_CUSTKEY int NOT NULL,
	C_NAME varchar(25) NOT NULL,
	C_ADDRESS varchar(40) NOT NULL,
	C_NATIONKEY int NOT NULL,
	C_PHONE char(15) NOT NULL,
	C_ACCTBAL decimal(15, 2) NOT NULL,
	C_MKTSEGMENT char(10) NOT NULL,
	C_COMMENT varchar(117) NOT NULL
)
WITH
(
	DISTRIBUTION = ROUND_ROBIN,
	CLUSTERED COLUMNSTORE INDEX
)
GO
```
この`CUSTOMER`テーブルの中に`C_PHONE`と言うデータがありますので、これを暗号化しようと思います。

ちなみにこんなデータが入っています。
```SQL
SELECT TOP 10 * FROM CUSTOMER
GO
```
結果
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/697647/14e9ff78-1eb1-9497-d494-02c5df11ee50.png)

# マスターキーの作成と証明書、対称キーの追加
対象となるデータベースに接続しまして、マスターキーを作成し、証明書と対称キーを追加します。
```SQL
--マスターキーの作成
CREATE MASTER KEY
GO

--証明書の追加
CREATE CERTIFICATE Customer09
   WITH SUBJECT = 'Customer Phone Numbers';  
GO

--対称キーの作成
CREATE SYMMETRIC KEY Customer_Phone_Numbers_Key11  
    WITH ALGORITHM = AES_256  
    ENCRYPTION BY CERTIFICATE Customer09;  
GO
```
証明書`Customer09`と対称キー`Customer_Phone_Numbers_Key11`を作成しました。

# 暗号化したデータを格納するカラムを追加
次に`C_PHONE`を暗号化したデータを格納するカラム`C_PHONE_Encrypted`を`CUSTOMER`テーブルに追加します。
```SQL
ALTER TABLE CUSTOMER
    ADD C_PHONE_Encrypted varbinary(160);   
GO
```

# 対称キーの暗号化を解除し、対称キーを使用可能にする
以下のコマンドで、対称キーの暗号化を解除し、使用可能にします。
```SQL
OPEN SYMMETRIC KEY Customer_Phone_Numbers_Key11
   DECRYPTION BY CERTIFICATE Customer09;
GO
```

# 対称キーを使って暗号化した`P_PHONE`の値を`C_PHONE_Encrypted`に格納
対称キー`Customer_Phone_Numbers_Key11`を使って、`P_PHONE`の値を暗号化します。この時に、認証子を指定します。認証子として`CUSTOMER`テーブルの`C_CUSTKEY`を使っています。
※ただし、認証子は指定しなくても大丈夫です。指定しない方法は後述しています。
```SQL
UPDATE CUSTOMER
SET C_PHONE_Encrypted = convert(varbinary(160),EncryptByKey(Key_GUID('Customer_Phone_Numbers_Key11'), C_PHONE, 1, HASHBYTES('SHA2_256', CONVERT( varbinary , C_CUSTKEY))))
GO
```

# 結果の確認
以下のSQLを使って暗号化されていることを確認します。
```SQL
SELECT TOP 10
	  C_CUSTKEY
	, C_PHONE
	, C_PHONE_Encrypted  AS 'Encrypted Phone number'
	, CONVERT(char,DecryptByKey(C_PHONE_Encrypted, 1 ,HASHBYTES('SHA2_256', CONVERT(varbinary, C_CUSTKEY)))) AS 'Decrypted Phone number'
FROM CUSTOMER 
GO
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/697647/32013363-7a94-a9b7-1a13-7fa39326fe85.png)

ちなみに、対称キーをクローズして上記のSQLを再実行すると、複合化できないことがわかります。
```SQL
--対称キーをCLOSE
CLOSE SYMMETRIC KEY Customer_Phone_Numbers_Key11
GO

--確認のSQLを実行
SELECT TOP 10
	  C_CUSTKEY
	, C_PHONE
	, C_PHONE_Encrypted  AS 'Encrypted Phone number'
	, CONVERT(char,DecryptByKey(C_PHONE_Encrypted, 1 ,HASHBYTES('SHA2_256', CONVERT(varbinary, C_CUSTKEY)))) AS 'Decrypted Phone number'
FROM CUSTOMER 
GO
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/697647/b6501532-31e0-7151-ab4a-59374a0cb12b.png)

# 追加：認証子を使わずに暗号化
先ほどのやり方は対称キーと認証子を使って暗号化しましたが、認証子を使わなくても暗号化は可能です。
```SQL
--対称キーの暗号化を解除し、対象キーを使用可能にする
OPEN SYMMETRIC KEY Customer_Phone_Numbers_Key11
   DECRYPTION BY CERTIFICATE Customer09
GO

--対称キーのみ（認証子を使わず）暗号化
UPDATE CUSTOMER
SET C_PHONE_Encrypted = convert(varbinary(160),EncryptByKey(Key_GUID('Customer_Phone_Numbers_Key11'), C_PHONE));  
GO
```
実行後同様に確認します。
```SQL
--確認のSQLを実行
SELECT TOP 10
	  C_CUSTKEY
	, C_PHONE
	, C_PHONE_Encrypted  AS 'Encrypted Phone number'
	, CONVERT(char,DecryptByKey(C_PHONE_Encrypted)) AS 'Decrypted Phone number'
FROM CUSTOMER 
GO
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/697647/33fd08ee-34a2-4375-1ddc-cf889aecf586.png)

# 補足
上記を実行するためには以下の権限が必要です。
・データベースに対する `CONTROL` 権限。
・データベースに対する `CREATE CERTIFICATE` 権限。
・テーブルに対する `ALTER` 権限。
・キーに対する権限。`VIEW DEFINITION` 権限が拒否されていないことが必要です。