Decrypt data with ENHANCED_SYM_DECRYPT - MaxCompute

Decrypts ciphertext using a specified keyset and returns the plaintext as a BINARY value.

Prerequisites

Before you begin, ensure that you have:

A basic or wrapped keyset generated by NEW_KEYSET or NEW_WRAPPED_KEYSET
(For wrapped keysets) The permissions required for the RAM role associated with the wrapped keyset. For details, see Activate KMS and complete configurations

Background

ENHANCED_SYM_DECRYPT accepts both basic keysets and wrapped keysets. The keyset used for decryption must match the one used for encryption.

A wrapped keyset is an existing keyset encrypted with a Key Management Service (KMS) key. Use wrapped keysets with KMS to manage encryption keys more securely than with basic keysets.

Syntax

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

Parameters

Parameter	Required	Type	Description
`keyset`	Yes	BINARY or STRUCT	The keyset to use for decryption. Must be the same keyset used during encryption.
`ciphertext`	Yes	BINARY	The ciphertext to decrypt. Must have been encrypted with the specified keyset.
`additional_data`	No	STRING	Additional data used for verification. This corresponds to the additional authenticated data (AAD) supported by the encryption algorithm.

Important

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

Return value

Returns the plaintext of the BINARY type.

Sample data

The following examples use the mf_user_info table. Run the following statements to create and populate the table.

-- 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 with a basic keyset

The following example decrypts the id_card_no column using a basic keyset passed directly as a hex literal.

Important

The data must have been encrypted before decryption. Use the same keyset for both operations. For an example of encrypting data, 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|
+------------+------+--------+------------+------------+

Decrypt with a wrapped keyset

The following example decrypts the tel column using a wrapped keyset. USE_WRAPPED_KEYSET retrieves the keyset from KMS at query time using the specified KMS key ARN and RAM role.

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;