云原生大数据计算服务 MaxCompute：ENHANCED_SYM_DECRYPT

背景与前提

MaxCompute支持使用ENHANCED_SYM_DECRYPT函数指定生成的基础密钥集或封装密钥集对数据进行解密，解密使用的基础密钥集或封装密钥集需与加密时使用的一致。封装密钥集是通过KMS密钥对生成的密钥集（KEYSET）再次进行加密，结合密钥管理服务做密钥管理，相比基础密钥集会更加安全。

使用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 |
  +------------+------+--------+------------+------+