MaxCompute:ENHANCED_SYM_DECRYPT

Background and prerequisites

You can use the ENHANCED_SYM_DECRYPT function in MaxCompute to decrypt data with a specified basic or wrapped keyset. The keyset used for decryption must be the same as the one used for encryption. A wrapped keyset is created by encrypting an existing keyset with a Key Management Service (KMS) key. You can use wrapped keysets with KMS to manage keys more securely than with basic keysets.

Before you use the ENHANCED_SYM_DECRYPT function, complete the following operations:

• Generate a basic keyset or a wrapped keyset using the NEW_KEYSET or NEW_WRAPPED_KEYSET function. For more information, see NEW_KEYSET and NEW_WRAPPED_KEYSET.

• If you use a wrapped keyset to decrypt data, you must have the permissions for the role that is associated with the wrapped keyset. For more information, see Activate KMS and complete configurations.

Syntax

BINARY ENHANCED_SYM_DECRYPT(BINARY <keyset> , BINARY <ciphertext> [,string <additional_data>])

Parameters

• keyset: Required. The user keyset, which can be of the BINARY or STRUCT type.

  Important

  The basic or wrapped keyset used for decryption must be the same as the one used for encryption.

• ciphertext: Required. The ciphertext of the BINARY type to decrypt. The ciphertext must have been encrypted with the specified keyset.

• additional_data: optional. This parameter specifies the verification information supported by the algorithm. The verification information is of the STRING type.

Return value

Returns plaintext of the BINARY type.

Sample data

-- Create a table.
create table mf_user_info(id bigint,
                          name string,
                          gender string,
                          id_card_no string,
                          tel string);
-- Insert data into the table.
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");
-- Query data from the table.
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|
+------------+------+--------+------------+------------+

Examples

• Decrypt the id_card_no column in the mf_user_info table using a plain keyset:

  Important

  Before you decrypt the data, ensure that it has been encrypted. The basic or wrapped keyset used for decryption must be the same as the one used for encryption. For an example of an encryption operation, see ENHANCED_SYM_ENCRYPT.

  INSERT OVERWRITE TABLE mf_user_info 
  SELECT id,
         name,
         gender,
         CAST(ENHANCED_SYM_DECRYPT(unhex('0A1072384D715A414541385044643351534C12580A330A0B4145532D47434D2D323536122026A8FB1126DF4F5B5DD03C180E6919565D7716CBB291815EFB5BBF30F8BEF9AF1801200210011A1072384D715A414541385044643351534C20022A0B68656C6C6F20776F726C64'), UNBASE64(id_card_no)) AS STRING) AS id_card_no,
         tel
  FROM mf_user_info;

  The following result is returned:

  SELECT * FROM mf_user_info;
  
  -- Sample result
  +------------+------+--------+------------+------------+
  | 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|
  +------------+------+--------+------------+------------+

• Use a wrapped keyset to decrypt the encrypted mf_user_info column in the tel table:

   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;

  The following result is returned:

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