本文為您介紹如何使用ENHANCED_SYM_DECRYPT
函數指定密鑰集進行資料解密。
背景與前提
MaxCompute支援使用ENHANCED_SYM_DECRYPT
函數指定產生的基礎密鑰集或封裝密鑰集對資料進行解密,解密使用的基礎密鑰集或封裝密鑰集需與加密時使用的一致。封裝密鑰集是通過KMS金鑰組產生的密鑰集(KEYSET)再次進行加密,結合Key Management Service做密鑰管理,相比基礎密鑰集會更加安全。
使用ENHANCED_SYM_DECRYPT
函數前需要完成以下操作:
已有通過
NEW_KEYSET
或NEW_WRAPPED_KEYSET
函數產生的基礎密鑰集或封裝密鑰集,詳情請參見NEW_KEYSET、NEW_WRAPPED_KEYSET。如果使用封裝密鑰集解密資料,需要有使用封裝密鑰集對應角色的許可權,操作詳情請參見開通KMS並完成配置。
命令格式
binary ENHANCED_SYM_DECRYPT(binary <keyset> , binary <ciphertext> [,string <additional_data>])
參數說明
keyset:必填,使用者密鑰集, 類型為BINARY或者密鑰集的STRUCT類型。
重要解密使用的基礎密鑰集或封裝密鑰集需與加密時使用的一致。
ciphertext: 必填,經過密鑰集加密過待解密的BINARY類型密文。
additional_data: 可選,演算法支援的STRING類型驗證資訊。
傳回值說明
返回BINARY類型的明文。
樣本資料
--建立表
create table mf_user_info(id bigint,
name string,
gender string,
id_card_no string,
tel string);
--插入資料
insert overwrite table mf_user_info values(1,"bob","male","0001","13900001234"),
(2,"allen","male","0011","13900001111"),
(3,"kate","female","0111","13900002222"),
(4,"annie","female","1111","13900003333");
--查詢資料
select * from mf_user_info;
+------------+------+--------+------------+------------+
| id | name | gender | id_card_no | tel |
+------------+------+--------+------------+------------+
| 1 | bob | male | 0001 | 13900001234|
| 2 | allen| male | 0011 | 13900001111|
| 3 | kate | female | 0111 | 13900002222|
| 4 | annie| female | 1111 | 13900003333|
+------------+------+--------+------------+------------+
使用樣本
使用普通密鑰集對
mf_user_info
表已加密的id_card_no
列解密:重要解密前請確保已對資料加密,且解密使用的基礎密鑰集或封裝密鑰集需與加密時使用的一致,加密操作樣本請參見ENHANCED_SYM_ENCRYPT。
insert overwrite table mf_user_info select id, name, gender, ENHANCED_SYM_DECRYPT(unhex ('0A1072384D715A414541385044643351534C12580A330A0B4145532D47434D2D323536122026A8FB1126DF4F5B5DD03C180E6919565D7716CBB291815EFB5BBF30F8BEF9AF1801200210011A1072384D715A414541385044643351534C20022A0B68656C6C6F20776F726C64'), unbase64(id_card_no) )as id_card_no, tel from mf_user_info;
查詢解密結果樣本如下:
select * from mf_user_info; --返回結果樣本 +------------+------+--------+------------+------------+ | id | name | gender | id_card_no | tel | +------------+------+--------+------------+------------+ | 1 | bob | male | 0001 | 13900001234| | 2 | allen| male | 0011 | 13900001111| | 3 | kate | female | 0111 | 13900002222| | 4 | annie| female | 1111 | 13900003333| +------------+------+--------+------------+------------+
使用封裝密鑰集對
mf_user_info
表已加密的tel
列解密:select /*+ MAPJOIN(a) */ id, name, gender, id_card_no, ENHANCED_SYM_DECRYPT( USE_WRAPPED_KEYSET('acs:kms:cn-hangzhou:1**************7:key/key-hzz****************1t', 'acs:ram::1**************7:role/kms', unhex('613256354C576836656A59314D6D59344E7A6B7A624452754D6D3434627A49786443317A655859786358426F4E6A4D78447654524C4632635077766E74554654584579715242583953724167446D2F397131786F57456E6F5474516739633853766242674456773565736674714A4D5435524455382F6F6A2B4E61766D774344494C734B6A416B6B675A42496F5568656F566D38564C4F30506D4778767137646956517453447A5467395147775639533161305A464A6D6A45562B6742722F56386653444D6E424D2B71493779784668303866594E6D336578775744423949726B645A3469784F2B532B476E6750523854524A58326E5768666478347034473468687248684A514D615071332F526C342B67427652773D3D') ), ENHANCED_SYM_ENCRYPT( USE_WRAPPED_KEYSET('acs:kms:cn-hangzhou:1**************7:key/key-hzz****************1t', 'acs:ram::1**************7:role/kms', unhex('613256354C576836656A59314D6D59344E7A6B7A624452754D6D3434627A49786443317A655859786358426F4E6A4D78447654524C4632635077766E74554654584579715242583953724167446D2F397131786F57456E6F5474516739633853766242674456773565736674714A4D5435524455382F6F6A2B4E61766D774344494C734B6A416B6B675A42496F5568656F566D38564C4F30506D4778767137646956517453447A5467395147775639533161305A464A6D6A45562B6742722F56386653444D6E424D2B71493779784668303866594E6D336578775744423949726B645A3469784F2B532B476E6750523854524A58326E5768666478347034473468687248684A514D615071332F526C342B67427652773D3D') ), tel ), '' ) as tel FROM mf_user_info;
返回結果樣本如下:
+------------+------+--------+------------+------+ | id | name | gender | id_card_no | tel | +------------+------+--------+------------+------+ | 1 | bob | male | 0001 | 13900001234 | | 2 | allen | male | 0011 | 13900001111 | | 3 | kate | female | 0111 | 13900002222 | | 4 | annie | female | 1111 | 13900003333 | +------------+------+--------+------------+------+