MaxCompute:ENHANCED_SYM_DECRYPT

Background information and prerequisites

MaxCompute allows you to use the ENHANCED_SYM_DECRYPT function to decrypt data by using a specified basic keyset or wrapped keyset. The basic keyset or wrapped keyset used for data decryption must be the same as that used for data encryption. You can create a wrapped keyset by encrypting an existing keyset based on a Key Management Service (KMS) key. Compared with basic keysets, you can use wrapped keysets with KMS to manage keys in a more secure manner.

Before you use the ENHANCED_SYM_DECRYPT function, make sure that the following prerequisites are met:

• A basic keyset or wrapped keyset is generated by using the NEW_KEYSET or NEW_WRAPPED_KEYSET function. For more information, see NEW_KEYSET or NEW_WRAPPED_KEYSET.

• Your account is assigned a role that has the permissions to use the wrapped keyset. This prerequisite must be met if you want to decrypt data by using a wrapped keyset.

Syntax

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

Parameters

• keyset: required. This parameter specifies a basic keyset of the BINARY type or a wrapped keyset of the STRUCT type.

  Important

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

• ciphertext: required. This parameter specifies the ciphertext of the BINARY type that is encrypted by using 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

Plaintext of the BINARY type is returned.

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 encrypted id_card_no column in the mf_user_info table by using a basic keyset.

  Important

  Before you decrypt data, you must make sure that the data has been encrypted, and the basic keyset or wrapped keyset used for data decryption is the same as that used for data encryption. For more information about encryption operation examples, see 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;

  The following sample statement queries the decryption result:

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

• Decrypt the encrypted tel column in the mf_user_info table by using a wrapped keyset.

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