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

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

検証データ

今回検証するデータは以下のようなDDLで作成をしました。

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と言うデータがありますので、これを暗号化しようと思います。

ちなみにこんなデータが入っています。

SELECT TOP 10 * FROM CUSTOMER
GO

結果

マスターキーの作成と証明書、対称キーの追加

対象となるデータベースに接続しまして、マスターキーを作成し、証明書と対称キーを追加します。

--マスターキーの作成
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テーブルに追加します。

ALTER TABLE CUSTOMER
    ADD C_PHONE_Encrypted varbinary(160);   
GO

対称キーの暗号化を解除し、対称キーを使用可能にする

以下のコマンドで、対称キーの暗号化を解除し、使用可能にします。

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を使っています。
※ただし、認証子は指定しなくても大丈夫です。指定しない方法は後述しています。

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を使って暗号化されていることを確認します。

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

ちなみに、対称キーをクローズして上記の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

追加：認証子を使わずに暗号化

先ほどのやり方は対称キーと認証子を使って暗号化しましたが、認証子を使わなくても暗号化は可能です。

--対称キーの暗号化を解除し、対象キーを使用可能にする
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を実行
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

補足

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